cancel
Showing results for 
Search instead for 
Did you mean: 

System copy from SQL2000 to SQL2005

Former Member
0 Kudos

Hello All,

We are trying to perform a homogenous system copy. Our source system is SQL 2000 and the target system is SQL 2005. We followed the normal attach/detach procedure and ran the STM tool for the database copy. After bringing SAP online, it gives an consistency check error in SICK - "Wrong long datatypes. Perform SQL2005 after upgrade steps. Please see note 126973."

This note in turn refers to note 799058 which describes the procedure of upgrading to SQL 2005 and homogenous system copy. But we have already done that. Can somebody please help me on this and let me know what I am missing.

Thanks,

Fahad

Accepted Solutions (0)

Answers (1)

Answers (1)

Matt_Fraser
Active Contributor
0 Kudos

Fahad,

I've done this a few times quite successfully, but there are a number of important prerequisite steps to ensure success. First, there is a PDF document "Upgrade to and Installation of SQL Server 2005 in an SAP Environment" that you will want to refer to, if you don't already have it. The latest version is dates 10/27/2006. You can find it at http://service.sap.com/instguides, then drill into "Other Documentation", "Database Upgrades", "MS SQL Server".

In addition to this, you'll want to refer to several Notes: 799058 which you already have, 151603, 683447, and 600027.

The important steps to take <i>before</i> you detach and copy your SQL2000 database are, basically, as follows:

On the target:

-- Install SQL2005 per the instructions in the PDF (I recommend a manual installation, so you can include Integration Services (required for Maintenance Plans to work), but the most important deviation from default is to choose the right collation: Binary order based on code point comparison, for use with the 850 (Multilingual) Character Set).

-- Install the new SAP central instance via SAPINST.

On the source:

-- Backup the database (you are going to make changes before copying).

-- Apply Basis support packs to at least the level specified in Note 799058 for your R/3 release.

-- Complete or delete outstanding updates in SM13.

-- Cancel all released jobs.

-- Go to SE14 - Extras - Invalid temp. table - and delete all QCM* tables.

-- Shut down R/3.

-- Run sap_droproc in Query Analyzer.

-- Set the source DB collation to SQL_Latin1_General_CP850_BIN2 via instcoll as per Note 600027 (this might be the step you missed?).

-- Detach and copy to the target.

On the target:

-- Attach the database.

-- Run DBCC CHECKDB just to make sure all is well.

-- Good time to get a backup!

-- Run the SAPINST from the "SAP Tools for SQL Server", using the "system copy setup / database copy" option.

-- Truncate the following tables with SQL tools: sap_perfhist, sap_perfsample, sap_perfinfo, MSSDWDLLS, ALCONSEG, ALSYSTEMS, DBSNP, MONI, OSMON, PAHI, SDBAD, SDBAH, SDBAP, SDBAR, DDLOG, TPFET, and TPFHT.

-- Run DBCC UPDATEUSAGE on the new database (important!).

-- Apply the latest kernel and dbsl_lib for your current release.

-- Start R/3!

-- Run sp_updatestats in SQL tools

-- Run SA38... MSSPROCS and double-check the db release -- shouldn't require action, though.

-- Run SM28 to check the installation.

and so on as described in the system copy guide.

I'm assuming you don't actually have to convert long datatypes as described in Note 154686, as you are already on SQL2000, but if your source R/3 release is 4.5B or lower, you might want to take a look at this Note to see if it applies to you.

Hopefully this has helped.

Best regards,

Matt

Former Member
0 Kudos

Thanks Matt for your detailed answer. Points rewarded.

HuseyinBilgen
Active Contributor
0 Kudos

Hi Matt,

What if someone forgots to run instcoll on 2000 sql?

Isn't there any way to convert collation to BIN2 on SQL 2005? Because System runs on the half converted System on sql 2005. Also Database settings allow to convert BIN2 on SQL 2005, but system gives errors like "Cannot resolve the collation conflict between "SQL_Latin1_General_CP850_BIN2" and "SQL_Latin1_General_CP850_BIN" in the equal to operation."

Matt_Fraser
Active Contributor
0 Kudos

Huseyin,

Unfortunately, it's not quite as easy once you are already on SQL2005. I believe the INSTCOLL tool is only for SQL2000, though I've never tried it on 2005. The easiest way to fix this situation is probably to go back to your original 2000 database, assuming it's still available, and assuming that you haven't already carried out productive transactions in the target 2005 database that you can't afford to lose, and start over. You cannot, supposedly, detach a 2005 database and attach it in 2000, i.e., you can't do the reverse of the system copy to 'downgrade' to 2000 and then run instcoll.

If the above is not possible, i.e., you no longer have the original 2000 database or you have transactions in the 2005 database that you must not lose, then there may be two options for you. One is to carry out another homogeneous system copy, this time from 2005 to 2005, but instead of the attach/detach method you must use the R3LOAD method, as referenced in Note 505906. The complete details of how to use this method are in the system copy guide that I referenced earlier.

Another possibility, though I don't know if it's supported by SAP (and I would try the R3LOAD method first), is to use Microsoft's DTS or SSIS tool to transfer the database contents from one database to another (i.e., a system copy), converting the collation enroute. This is discussed in Microsoft KB article 325335. The article talks about doing this with DTS in SQL 2000, but says the same concepts should apply in 2005.

Best of luck,

--Matt