SAP Basis Tech Supports

Symptom
Setting up and running SQL Server 2005 for an SAP system and information on the upgrade from SQL Server 2000 to SQL Server 2005.

Reason and Prerequisites
When introducing SQL Server 2005 into your system environment, you must follow the official SAP installation and upgrade guide “Installation of and upgrade to SQL Server 2005 in an SAP environment”. The guide is available on SAP Service Market Place at http://service.sap.com/instguides -> Database Upgrades -> MS SQL Server.
For SQL 2005 cluster installation, refer to note 1257296 for important list of checks/prerequisites that you must perform in the cluster nodes before the SQL server cluster installation.
This note contains additional up-to-date information for this guide. IT IS IMPORTANT TO DOWNLOAD THE LATEST VERSION OF THIS NOTE AS IT CONTAINS IMPORTANT AND CURRENT UPDATES. For specific updates to the installation guide, refer to section V below.
For sick messages, problems when starting the system, or other runtime errors, refer to section “VI TROUBLESHOOTING” below.

Solution

CONTENTS

I IMPORTANT GENERAL REMARKS
II UPGRADE FROM MS SQL SERVER 2000 to MS SQL SERVER 2005
III HOMOGENEOUS SYSTEM COPY TO MS SQL SERVER 2005
IV GENERAL MS SQL SERVER 2005 INFORMATION
V CHANGES TO THE OFFICIAL GUIDE
VI TROUBLESHOOTING
VII FREQUENTLY ASKED QUESTIONS
=======================================================================
I IMPORTANT GENERAL REMARKS
=======================================================================
You have the following options to introduce SQL Server 2005 in the SAP environment:
You install a new SAP system that is based on NetWeaver 2004s (basis release 7.00), which supports SQL Server 2005.
In addition, there are installation master DVDs supporting SQL Server 2005 available for all products with basis releases 6.20 or 6.40. Refer to SAP note 905634 for information about these new installation master DVDs.
You should not use older installations of SAP products based on 6.20 or 6.40 with SQL Server 2005.
Refer to SAP note 965145 to install 4.6C systems.
You must apply all basis support packages immediately after the installation. SAP Note 924288 provides more information about how to perform 6.xx based installations on SQL Server 2005.You upgrade an existing SQL Server 2000 instance that is already running SAP to SQL Server 2005.
IMPORTANT: Before you upgrade SQL Server 2000 to SQL Server 2005, make sure that you import the required basis support packages mentioned below to your system. If the required basis support packages are not imported to the system before the SQL Server upgrade, the upgraded system does not function properly with SQL Server 2005.
This procedure is described in section II below.You install SQL Server 2005 and attach a database from an existing SAP system with SQL Server 2000 (perform a homogeneous system copy).
This procedure is described in section III below.
For the installation no further steps are required except applying all basis support packages. For the upgrade and attach cases, you must follow the instructions below.
The minimum basis support package levels to run on SQL Server 2005 are:
Release 4.6C ———> 53
Release 6.20 ———> 57
Release 6.40 ———> 15
SAP recommends to apply all available basis support packages before using SQL Server 2005 productively.
Note that the upgrade of a 32-bit SQL Server 2000 that is running on a 64-bit machine to a 64-bit SQL Server 2005 is not supported. In this case you must install a new 64-bit instance of SQL Server 2005 and follow the instructions below for the homogenenous system copy (section III). Keep in mind that the source and target servers are the same.
It is highly recommended to run any new installation of a SQL Server 2005 instance using a special VB script provided by SAP. This script is named SQL4SAP.vbs and is located on the SQL Server 2005 DVD shipped with SAP products.
Note that besides upgrading the server, you have to carry out some steps for the SAP system. SAP does not support any other upgrade method than that described here. To perform the SAP upgrade you have to use “SAP Tools for MS SQL Server”, which you can download from SAP Service Marketplace at http://service.sap.com/msplatforms -> SQL Server”.
If there are more than one files with a similar name, make sure that you use the one whose name indicates that it is meant for SQL Server 2005 (or a beta version of SQL Server 2005).
Every application server which is connected to SQL Server 2005 must have the client side driver installed. This happens automatically if the SAP tools for MS SQL Server 2005 are installed on the application server. However, it is sufficient to install the “SNAC” or native client oledb library, which you can find on the SQL Server 2005 installation CD under Setup\sqlncli.msi
IMPORTANT: Always make sure that you have a full database backup of your system before you start this procedure.
=======================================================================
II UPGRADE MS SQL SERVER 2000 to MS SQL SERVER 2005
=======================================================================
The following steps describe how to perform to upgrade SQL Server 2000 to SQL Server 2005 in an SAP environment.
1. Apply all available SAP basis support packages for your release. Make sure that you apply at least the minimum level mentioned above.2. If you use a Java system, update the JDBC driver as described in SAP note 639702.3. Shut down the SAP system.4. For ABAP or ABAP+JAVA systems with basis release 6.40 or earlier: Drop SAP stored procedures in the database with the stored procedure “sap_droproc”. Run the following statements in the SAP database with the QueryAnalyzer:
use <SID>
go
setuser ‘<sid>’
go
exec sap_droproc
go
This takes some time depending on the speed of the server. A rate of at least 150000 stored procedures per hour should be expected. To check how many stored procedures will be dropped, run the statement:
select count(*) from sysobjects where name like ‘Y%’
If your database has many such stored procedures, you may have to schedule a separate downtime to achieve this. See also SAP note 814555 for information about how to run stored procedures.5. Make sure that the server and database collations are set to SQL_Latin1_General_CP850_BIN2. Refer to SAP note 600027 for information about how to check and change this.6. Apply the latest patches for the kernel and dbsl library to all SAP application servers. These patches are available on SAP Service Marketplace. For more information, see SAP note 19466. For 6.20 basis systems, apply the 6.40 executables. For 4.6C systems use the 4.6D executables. This is required for SQL Server 2005. Also install the Microsoft runtime DLLs as described in SAP note 684106.7. Execute the installation procedure for SQL Server 2005 provided by Microsoft. This allows you to upgrade one or more SQL Server 2000 instances.8. Download the zip file that includeds the SAP tools for MS SQL Server from SAP Service Marketplace. Extract it to a local (empty) folder on the central instance server. Run sapinst.exe, and choose “Upgrade to MS SQL 2005″. This program performs the post-upgrade steps that are required for SAP ABAP products running on SQL Server 2005.
This step isnotrequired for a Java system.
It is not necessary to run any other option, in particular do not run the “Schema Conversion” option for 4.6C systems. It is recommended to still run 4.6C systems under the ‘dbo’ schema.9. Start the SAP system.10. Execute the following commands in the SQL Server 2005 Management Studio (connect with a db administrator login):
———————————————-
use <SID> — where <SID> is your SAP database
go
EXEC sp_updatestats
go
———————————————-
It takes some time to replace the old SQL Server 2000 index statistics with new SQL Server 2005 statistics. You can run this while the SAP system is online.
=======================================================================
III HOMOGENEOUS SYSTEM COPY TO MS SQL SERVER 2005
=======================================================================
If you use SAP NetWeaver 2004s (7.0) or higher, there is an SAPinst option to perform migration steps. Only use this option, if the basis release of your source system is also 7.0.
For basis releases 4.6C, 6.20 or 6.40 perform the following steps:
1. Apply all available SAP basis support packages for your release.2. Shut down the SAP system.3. ForJAVA or ABAP+JAVA systems, then export the Java Add-in. The process flow and the steps are clearly explained in the Homogeneous system copy Guide for your SAP Release.4. For ABAP or ABAP+JAVA systems with basis release 6.40 or earlier: Drop SAP stored procedures in the database with the stored procedure “sap_droproc” as described in section II above.5. Make sure that the source server and database collations are set to SQL_Latin1_General_CP850_BIN2. For more information about how to check and change this, see SAP note 600027.6. Detach the SAP database from the source server using sp_detach_db (or use the Enterprise Manager).7. Install a new instance of Microsoft SQL Server 2005 on your target system. In some cases you will use the same physical server. This is the case, for example, when changing from a 32-bit SQL server 2000 to a 64-bit SQL server 2005 on the same host. In this case you can uninstall SQL Server 2000 and then install SQL Server 2005 with the same instance name.
Use the SQL4SAP.vbs script for the installation, if possible. If not, make sure that you select the collation option:
“Binary order based on code point comparison, for use with the 850 (Multilingual) Character Set.”8. If you migrate to a new physical server you must install a new SAP instance on that server that matches your source system release. If your system stays on the same server, this step is not necessary.9. If the source and target server are different, you have to copy the database files of your database from your source server to the target server and attach them to your SQL Server 2005 database either with sp_attach_db or the SQL Server Management Studio for SQL Server 2005. The target database may be created under a different name, which is normally the same as the target system ID.10. For JAVA or ABAP+JAVA systems, then check the Homogeneous system copy guide for the process flow and steps to install the Java Instance Add-in.11. Download the zipfile that includes the SAP tools for MS SQL Server from SAP Service Marketplace. Extract it to a local (empty) folder on the central instance server. Run sapinst.exe, and choose “Database Copy”. This option performs the required post-upgrade steps for all SAP products running on SQL Server 2005. It is not necessary to run any other option, in particular do not run the “Schema Conversion” option for 4.6C systems. It is recommended to still run 4.6C systems under the ‘dbo’ schema.
POST PROCESSING STEPS:
12. Apply the latest kernel and dbsl_lib patches from SAP Service Marketplace for your release before you start the SAP system. SAP 4.6C systems must use the 4.6D executables and SAP 6.20 systems running with SQL Server 2005 must use the 6.40 executables. This is required for all application servers. Also install the Microsoft runtime DLLs as described in SAP note 684106.13. If you use a Java system, update the JDBC driver as described in SAP note 639702.14. Start the SAP system.15. Execute the following commands in the SQL Server 2005 Management Studio (connect with a db administrator login):
———————————————-
use <SID> — where <SID> is your SAP database
go
EXEC sp_updatestats
go
———————————————-
It takes some time to replace the old SQL Server 2000 index statistics with new SQL Server 2005 statistics. You can run this while the SAP system is online.
=======================================================================
IV GENERAL MS SQL SERVER 2005 INFORMATION
=======================================================================
For more information about how to configure SQL Server 2005 parameters, refer to SAP note 879941.
SQL Server 2005 introduces new and more flexible datatypes for holding large variable length binary or text data. Previously, the datatypes used in SQL Server 2000 were image, text and ntext. In SQL Server 2005 these are replaced with varbinary(MAX), varchar(MAX) and nvarchar(MAX), respectively. The SAP tools for MS SQL Server perform this change except for release 4.6C. This is a catalog change and should run quickly regardless of the size of the database.
=======================================================================
V CHANGES TO THE OFFICIAL GUIDE
=======================================================================
No changes so far.
=======================================================================
VI TROUBLESHOOTING
=======================================================================
If you get “sick” messages after the installation of SAP basis release 6.20 or 6.40, refer to SAP note 924288 for details about how to install these SAP versions on SQL Server 2005.
If you cannot start SAP or get sick messages, read the sections above about the upgrade or the homogeneous system copy to find out what went wrong. If possible, run the SAP tools for MS SQL Server as described in the sections above. since the tools should resolve these issues.
If this does not help, open a customer support message and include any relevant messages found in dev_w traces in the work directory.
Installation Problems:

The SQL Server 2005 cluster installation fails if a NT Group ‘SA’ exists in your system domain. The SQL Server development is currently working on this problem, and a fix will be available in SQL Server 2005 SP1.
As a temporary workaround, you can first install the SQL Server 2005 cluster in “Mixed Authentication Mode”, and then change the server authentication into “Windows Authentication Mode” by performing the following steps:
1. Choose “All Programs” -> “Microsoft SQL Server 2005″ -> “SQL Server Management Studio”.2. In the “Connect to Server” window, enter the server name (if required), and choose “Connect”.3. Right-click the SQL Server instance and choose “Properties”.4. Choose “Security”.5. For “Server authentication”, choose “Windows Authentication mode”.6. Choose “OK”.7. Restart SQL Server.
=======================================================================
VII Frequently Asked Questions:
=======================================================================
Question:I’ve upgraded a SQL Server 2000 to SQL Server 2005, and get runtime errors or “sick” messages. I noticed that the basis support package level is not the minimum required. Can I apply the required support packages now while running on SQL Server 2005?
Answer:Yes you can. This is not recommended by SAP but you should be able to apply support packages while running on SQL Server 2005. If you apply the required minimum basis support packages and still get errors or sick messages that cannot be resolved contact your SAP Support.Question:I’ve upgraded a SQL Server 2000 to SQL Server 2005, and am running the upgrade option of the SAP tools for MS SQL Server. I get an error message about a wrong collation. What do I do now?
Answer:SQL 2005 servers and databases must use collation SQL_Latin1_General_CP850_BIN2. The conversion tool described in SAP note 600027 only works on SQL Server 2000. You must restore a backup of your database that you made prior to the upgrade. You must restore this backup to a SQL 2000 server (could be a new SQL Server 2000 instance on the same host), and then follow the instructions of SAP note 600027 to change the collation.
When you have finished, you must uninstall SQL 2005 server, remove the upgraded SQL 2005 database files, and follow the instructions of section “III: HOMOGENEOUS SYSTEM COPY TO MS SQL SERVER 2005″, in order to copy the converted database from SQL Server 2000 to a new installation of SQL Server 2005.Question:I upgraded SQL Server 2000 to SQL Server 2005. During the upgrade I had chosen “Workstation Components”. In spite of this, the SQL Server Management Studio was not installed.
Answer:In the upgrade, you must use the “Advanced” option, and explicitly choose the client components. Then the SQL Server Management Studio will be installed. You can also install the SQL Server Management Studio from the SQL 2005 installation CD after the upgrade is finished.Question:When I start my SAP 6.20 system, why do I receive errors, which appear in the developer traces as:
Error/Message: (err 8116, sev 0), Argument data type varbinary(max) is invalid for argument 1 of textptr function.
Answer:You are running your SAP system with the 6.20 version of the SAP executables. Apply the latest 6.40 kernel from the SAP Service Marketplace.