When installing the MS SQL Server 2000 database for an SAP system, for "Collation Settings" we recommend to select "Binary order for use with the 850 Multilingual Character Set" (code page cp850).
With this option all new databases and table columns are created with this collation setting.
To view the default collation of MS SQL Server, use the query:
select serverproperty('collation')
With code page cp850 this select returns:
SQL_Latin1_General_CP850_BIN
Unfortunately, this collation setting does not sort Unicode data properly as required by the SAP system. To solve the problem, Microsoft provides a corrected collation with the name
SQL_Latin1_General_CP850_BIN2
SAP requires that all new installations of an SAP system use this collation. This SAP note explains how to apply the collation before installing an SAP system.
For existing non-Unicode SAP systems there is currently no need to convert them to the new collation.
With the most recent version of SQL Server 2000 (as described below), the conversion of existing non-Unicode SAP systems is quite easy.
The sorting of Unicode data used by the default Unicode collation of SQL Server 2000 does not meet SAP requirements.
The new collation is available from Microsoft in SP4 for SQL Server 2000 or later.
During the installation of MS SQL Server 2000 you cannot choose the new collation. Therefore, you must apply the collation after the installation by running the InstColl.exe executable attached to this SAP note.
Proceed as follows:
1. Install the MS SQL Server 2000 database as described in the installation guide.
For "Collation Settings", select the codepage "Binary order for use with the 850 Multilingual Character Set" (CP850 with binary sort order).
2. Install SP4 or later for SQL Server 2000.
For more information on how to get SP4, see SAP Note 62988.
3. Download the zip file attached to this SAP note and copy it to a local drive on your server.
Use the version that matches your host architecture.
4. Extract InstColl.exe to a local empty folder.
5. If you use a CD delivered by SAP, you must copy InstColl.exe to a local empty directory. You cannot run it directly from the CD.
6. Open a command prompt and execute InstColl.exe.
- For a named instance, enter:
InstColl.exe -S<server><instance> without any spaces after -S.
- For a default instance, enter:
InstColl.exe
- For a clustered server, you must use the virtual server name.
The program starts the SQL Server service, if it is not yet running, and performs several checks. When it has finished, it stops the SQL Server service.
You may be prompted to manually shutdown SQL Server.
The upcoming window, shows after a while an output similar to this:
...
2003-02-27 19:08:31.80 spid1 index restored for Northwind.Employees.
2003-02-27 19:08:31.83 spid1 index restored for Northwind.Categories.
2003-02-27 19:08:31.94 spid1 index restored for Northwind.Customers.
2003-02-27 19:08:32.12 spid1 index restored for Northwind.Suppliers.
2003-02-27 19:08:35.47 spid1 Default collation successfully changed.
2003-02-27 19:08:35.47 spid1 Recovery complete.
If you use the latest version of InstColl.exe, which is attached to this note, this window closes automatically and you receive a message in the original command window informing you whether the conversion finished successfully or not.
If the conversion was successful, the SQL Server starts automatically.
If errors occurred, refer to section "Error Handling" at the end of this note.
If you use an older version of InstColl.exe, you may have to press Ctrl-C to close the window with the messages shown above. In this case you may also have to start SQL Server manually.
7. To check whether the MS SQL Server is properly configured, run the following command in the Query Analyzer:
select serverproperty('collation').
8. Continue with the installation of the standard SAP system and install any SAP instance or the J2EE Engine on this server.
Running InstColl.exe against a server containing SAP databases
You can also run InstColl.exe against a server that already runs non-unicode SAP databases and you want to add a new Unicode system.
Before you run InstColl.exe, you must perform the following steps:
1. Shut down your SAP system.
2. Back up your database
3. Run a consistency check on the SAP database with "dbcc checkdb", and make sure that no errors occurred.
4. Run the stored procedure sap_droproc in your SAP database, as follows:
a) Use the QueryAnalyzer to connect to and access the SAP database.
b) Execute the following query:
exec sap_droproc
If sap_droproc is not found you may have to first execute
setuser '<sid>'
where <sid> is your SAP system ID in lowercase.
This may run for some time depending on the number of stored procedures in the database.
Now run InstColl.exe as described above. In a cluster configuration InstColl.exe must be run on the node containing the SQL Server group and the server name on the command line must be the name of the virtual SQL Server.
The conversion runs quickly because none of the columns in such databases uses Unicode datatypes. Regardless of the size of the SAP database, the conversion of a non-Unicode SAP database does not take longer than 10 minutes.
If errors occur, refer to section "Error Handling" at the end of this SAP note.
Installation on a cluster
Check your installation guide and determine whether it is recommended to perform the installation of the SAP system on a:
- Local node followed by a conversion of the SAP system to MSCS.
- Virtual node without any conversion step.
For newer installations with SAPinst this (easier) method is recommended.
Procedure for the installation on the local node
1. Install SQL Server 2000 on Node A.
2. Install SQL Service Pack 4 on Node A.
3. Run InstColl.exe on Node A as described above.
4. Install the SAP system on Node A as described in the installation guide.
5. On Node A run the SQL Server 2000 (RTM) setup, and convert SQL Server to a virtual server.
6. Re-apply SQL Server Service Pack 4 on node A.
7. Follow the installation guide to convert your SAP system for MSCS.
Make sure that throughout this procedure, all cluster groups stay on Node A. No failover should be done until steps 6 and 7 are completed.
Procedure for the installation on the virtual node
1. On Node A run the SQL Server 2000 (RTM) setup to install SQL Server as a virtual server.
2. Install the SQL Server Service Pack 4.
3. Run InstColl.exe as described above.
4. Proceed with the installation of the SAP system as described in the installation guide.
Use of the new collation
If you have an SQL Server running on an existing SAP system, and you want to apply Java components to that server, apply SP4, and InstColl.exe before installing the new components.
Always make sure that you have a current backup of your databases before you start such an action.
SAP requires that the new collation is to be used for all new Unicode or non-Unicode SAP system installations with MS SQL Server. New versions of the SAPinst installation tool check for the new collation.
At this time, there is no need to convert the existing SAP system to the new collation. If you plan to use the server for additional components that require Unicode (SAP or Java components), you may use SP4 and InstColl.exe to convert the server and all databases to the new collation.
A system using the new collation (Unicode or non-Unicode) and an existing system using the old collation cannot run as two databases in the same MS SQL Server instance. If you want to install a new system on a database server with an existing system, you have the following options:
You install a new named SQL Server instance for the new system and apply the new collation with InstColl.exe
You convert the existing system to the new collation using SP4 or higher and InstColl.exe.
This second option allows you to run both systems as two databases of a single SQL Server instance or as two schemas of an MCOD database.
Always make sure you have backed up your database before you start.
Besides applying SP4, you do not have to make any changes on the client side (SAP application servers).
Homogeneous system copy
SQL Server databases can be easily transferred from one SQL Server to another, using the detach/attach method (see stored procedures sp_detach_db and sp_attach_db in SQL Server Books Online). If the target server is SQL Server 2005 or later, then the target server must already use collation SQL_Latin1_General_CP850_BIN2. If the source database is using SQL_Latin1_General_CP850_BIN on SQL Server 2000, then the source server must be converted to SQL_Latin1_General_CP850_BIN2 first, before the system copy is executed. Use the instructions above to convert the source server to SQL_Latin1_General_CP850_BIN2.
Use the following instructions only if both source and target servers are running SQL Server 2000:
Make sure that the collation of the target server match with the collation of the database you are migrating.
1. Before you detach the database, execute the following select in the SAP database:
select databasepropertyex('<SID>', 'collation')
This returns a value showing the collation of the SAP database, which is either SQL_Latin1_General_CP850_BIN or SQL_Latin1_General_CP850_BIN2.
2. Then execute the following statement on the target server:
select serverproperty('collation')
3. If the two collations match, proceed with the homogeneous system copy.
4. If the collations do not match, proceed as follows depending on the case that applies for your system: :
Case 1: Database collation: SQL_Latin1_General_CP850_BIN
Target Server collation: SQL_Latin1_General_CP850_BIN2
a) In this case, attach the database.
b) Make sure that no user connects and uses this database.
c) Re-run InstColl.exe with the following command line:
InstColl -S<server> -D<database>
The special -D option allows you to convert only one database.
This should finish within a very short time (<3 minutes)
You can now use the SAP database in its new location.
Case 2: Database collation: SQL_Latin1_General_CP850_BIN2
Target Server collation: SQL_Latin1_General_CP850_BIN
a) Back up all databases on the target server.
b) Make sure that you also have a backup (or copy) of the database you are migrating.
c) Apply SP4.
d) Run InstColl.exe against the server (do not use the -D option).
e) Attach the database.
Installing a new transport program r3trans.exe
In order to avoid performance problems during transports, you must use a new version of the transport program r3trans.exe with release date 18.03.03 or later. You can download a current version of this executable from SAP Service Marketplace at http://service.sap.com/patches (see SAP note 19466 for details).
Elder R3trans versions may cause performance problems during the import of transport requests, support packages and upgrades. The new R3trans version adapts some internal functions automatically to the installed collation by using fast algorithms wherever possible.
SAPinst does not support the new collation for some SAP systems
For some SAP systems SAPinst does not support the new SQL_Latin1_General_CP850_BIN2 collation, which we recommend to use. If you install an SAP system with the BIN2 collation the installation might stop with the message:
"Wrong server collation..."
In this case, proceed as follows:
1. Cancel the installation with SAPinst.
2. Go to your SAPinst directory.
3. Open the keydb.xml and search for the string "SQL_Latin1_General_CP850_BIN".
4. Replace it with "SQL_Latin1_General_CP850_BIN2".
5. Restart SAPinst.
Error Handling
There is one known error that might happen, if there are very many objects existing in an SAP database, which is going to be converted.
In most cases this happens, if you do not run "sap_droproc" before the conversion.
The error message looks similar like this:
Error: 1204, Severity: 19, State: 1
The SQL Server cannot obtain a LOCK resource at this time. Re-run your statement when there are fewer active users or ask the system administrator to check the SQL Server lock and memory configuration.
Solution:
Re-start SQL Server twice and run the sap_droproc stored procedure in the SAP database as described above.
Re-run InstColl.exe.