[58513] in SAPr3-news
Impact of changing MSSQL database collation.
daemon@ATHENA.MIT.EDU (Alan Pope)
Tue Nov 28 04:24:30 2006
To: sapr3-news@mit.edu
Date: 28 Nov 2006 01:24:00 -0800
From: "Alan Pope" <alan.pope@gmail.com>
Message-ID: <1164705840.868168.99270@l39g2000cwd.googlegroups.com>
We have a 3 system non-unicode landscape consisting of R/3 Enterprise
on a mix of i386 and IA64 boxes running Windows 2003 servers with SQL
Server 2000 with 6.40 kernel and fairly recent support packages.
We have bought a new server to go into the landscape which is an x86_64
architecture and as such we have installed x86_64 Windows 2003 running
SQL Server 2005.
We want to regularly copy the production database from the IA64 (SQL
2000) machine to the x86_64 (SQL 2005) machine using backup/restore.
OSS Notes 505096 [0], 799058 [1] and 60027 [2] indicate that the
collation order on SQL 2005 (the target machine) should be
SQL_Latin1_General_CP850_BIN2. However the sort order on the source
database (SQL 2000) is SQL_Latin1_General_CP850_BIN.
So SQL 2005 is installed correctly as BIN2, but when I restored the
database it was BIN. The notes indicate that the instcoll tool should
only be used on SQL 2000 so we cannot use it on the SQL 2005 system.
Indeed it crashes when run on the SQL 2005 system with "Runtime error
R6034 - An application has made an attempt to load the C runtime
library incorrectly.".
So I am not surprised by this because instcoll is designed for Win2000
and MSSQL 2000, not Win2003 and MSSQL 2005.
If we have a source which is collation BIN and a target which must be
BIN2 and no way to convert the collation order on the x86_64 then
either:-
1) We install (on another partition) Win 2000 32-bit on the x86_64 box,
install MSSQL 2K, restore the BIN database and use instcoll to convert
to BIN2. Then detach the database, reboot to Win2K3 64-bit and
re-attach the (now BIN2) database to the BIN2 MSSQL 2005 which is
already installed there.
Other alternatives we have considered:-
2) We install Windows 2000 32-bit on the x86_64 and keep that rather
than move to 64-bit Windows on that host.
- We'd lose the better memory management of 64-bit Windows
- We wouldn't lose much in the way of computing power as there are
plenty of CPUs and the bottleneck is more likely to be IO than CPU.
3) We install SQL 2005 with collation order BIN, NOT BIN2 then
backup/restore the BIN databases to it.
- My impression is that it is not supported to install MSSQL 2005 with
collation order BIN.
4) We change all our existing systems to BIN2 so that we can easily
copy them to the new servers (which are BIN2).
- What is the impact of changing existing SAP systems to BIN2 other
than the downtime to run instcoll.exe?
- Will there be any damage to our existing systems to change them to
BIN2?
Most importantly:-
- We have a secondary server for log shipping which would also need to
be migrated to BIN2 (assumption?).
- Can we run instcoll.exe on the secondary database and be sure it will
not start the database thus invalidating the database?
Or
- Do we need to re-create the standby database after we have changed
the collation order on the primary?
Cheers,
Al.
[0] https://service.sap.com/sap/support/notes/505096
[1] https://service.sap.com/sap/support/notes/799058
[2] https://service.sap.com/sap/support/notes/60027
