Upgrade to from SQL Server 2000 to 2005

[Former Member]

Hello,

We are currently running ECC 5.0, BW 3.5, and XI 3.0 running on SQL Server 2000. We are looking into upgrading it to 2005, but are hesistant to because we are not familiar with the version.

Has anyone here run SQL 2005 with similar SAP applications mentioned above? Did you encounter an issues or any negative impact to SAP? What are some of the benefits from the upgrade from a function and technical standpoint?

We appreciate any feedback, and if anyone is willing to participate in a conference call, that would be great as well.

Thanks,

Rick

Under Armour Performance Apparel.

[Former Member · Accepted solution]

Hello Rick,

I have performed approximately 20 SQL 2000 -> SQL 2005 upgrades and have a procedure documented that will tell you exactly how to execute the upgrade.

I would strongly recommend all SAP customers running SQL 2000 to upgrade to SQL 2005. There are large performance, security and functionality improvements.

[SDN Editor's Note: N.P.C.'s document can now be downloaded here: <a href="http://www.sdn.sap.comhttp://www.sdn.sap.comhttp://www.sdn.sap.com/irj/servlet/prt/portal/prtroot/docs/library/uuid/c90ecf8d-0d01-0010-4aa3-e6fc4da46bb3">Upgrading from SQL 2000 to SQL 2005 for SAP</a> -Thx]

Before you begin I recommend you update your support pack stack to the latest available (I cover the requirements in my document). As your SAP release is quite new the upgrade will be quite simple and will take less than 1 hour per system assuming your DB is not more than 500GB.

The procedure is very simple and well documented by SAP and Microsoft. The only slightly difficult part is setting some new options for SQL 2005 and replacing the JDBC drivers for Java based systems.

For SQL Server 2005 only Collation BIN2 is supported. Before you upgrade from SQL Server 2000 to SQL Server 2005, you must change the collation of your SAP database to BIN2, as described in Note 600027. Any well managed SQL site would have performed this collation change some time ago!

Message was edited by: The SDN Content Team

[Former Member · Accepted solution]

Hi NPC,

Thank you for the response! This is exactly what I am looking for.

Do you have any specific details around the improvements with upgrading to SQL 2005. I am trying to convince management to upgrade, but haven't found any other SAP customers who are similar to us.

Thanks again

Rick

[Former Member · Accepted solution]

Hi NPC,

Here is a strategy question for you.

At the moment our SAP servers are running on 32bit OS. Can or should we upgrade to SQL 2005 and then migrate to a x64/IA64? Or vice versa?

Thanks,

Rick

[Former Member · Accepted solution]

Hi Rick,

SAP will de-support 32 bit kernels shortly. All SAP customers have been sent a letter advising this. You can view the FAQ here:

https://websmp206.sap-ag.de/~sapidb/011000358700002950922006E

The answer to your question is: “do both at the same time”. SQL 2000 datafiles can be read by SQL 2005. sp_attach_db is used to move the databases to the new systems.

The basic procedure is outlined in note 799058, though I warn everyone that this procedure is note totally "complete". Look at the instructions in this section of 799058 "III HOMOGENEOUS SYSTEM COPY TO MS SQL SERVER 2005"

You may find that you have very positive ROI when upgrading to 64bit systems. You may be able to shutdown 50-70% of your non-productive servers and consolidate them on Itanium or x64 systems. The savings in database and OS licenses can be quite large. Dual core servers will save on licensing costs, because Microsoft will only charge for 1 license even if you have two or four “cores” in the CPU.

With Windows Itanium you can consolidate at least 4 SAP instances onto one server (I have seen 6 SAP instances running on one Itanium server simultaneously). Windows behaves more like Unix when running on Itanium – add memory and you can run more applications. Consolidation is also possible to a slightly lesser extent on x64 systems.

Example: You operate ERP 2004, EP 6.0 and BW 3.5. You have a Sandbox, Development, QA and Production landscape. Each landscape has three 32 bit servers (one each for ERP, EP and BW). 4 X 3 = 12 (for only Sandbox, Dev and QA). Each server had 4 CPUs – a total of 48 (4 cpu X 12 servers).

With 64 bit systems you can run ERP, EP and BW all on three more powerful server with 4 dual core CPUs (meaning 8 “cores”). This is more than sufficient SAPS for non-productive systems. One server for all Sandbox systems, one for DEV and one for QA. With Itanium systems from Unisys, Fujitsu and HP you can consolidate to 1 physical server with 3 “partitions”.

Before

12 Servers – air con, UPS, floor space, support contracts etc

48 CPU – SQL 2005 license 48 X 24,999 = 1,199,952 (list price before discount)

48 CPU – Windows license 48 X 2,000 = 96,000 (list price)

Plus you there is the cost of backup agents, anti-virus etc

= over 1,300,000

After

3 Server – lower BTU, floor space etc

12 dual core CPU (24 cores) – SQL 2005 license 12 X 24,999 = 299988

12 dual core CPU (24 cores) – Windows license 12 X 2000 = 24,000

= 330,000 and much lower maintenance and misc. costs

You can also reduce the number of production application servers and improve performance.

I would plan carefully (there are a few details to consider) and you can make a strong business case and save money and lower the workload of your SAP admins. There are a lot new technologies for reducing the number of servers you need for HA/clustering your Production systems released recently. Contact your hardware vendor.

NPC

[andreurban_blumberg]

Hi Rick,

we have upgraded around 20+ SAP systems to SQL 2005 Service Pack 1 incl. BW 3.1, ERP 2005, CRM 5.0 systems. We made very good experiences, performance is up ~15% (everything else unchanged), and the upgrade process is fairly straightforward.

Re DB upgrade with HW change yes or no: we did somethings migrate to new x64 hardware (HP DL585) at the same time, sometimes we did the SQL upgrade later, both works. My preference is to do it together and the db_detach / db_attach makes this straightforward. Advantage is you can test your new hardware / new DB on a separate landscape and run-in the equipment nicely prior to cutover.

[Former Member]

Hello

I would appreciate feedback, comments or criticism of the document I have emailed to many of you detailing the procedure for upgrading from SQL 2000 to SQL 2005

Thanks

N.P.C

[clas_hortien]

Hi,

i have read your document and like to give some feedback:

- 4.6C is still not supported on SQL 2005, only with a special permission, that has to be requested via OSS

- You can't run select serverproperty('collation') in ST04 -> .. -> stored procedures

- During the installation, the Integration Service should be installed, when you want to use Maintenance plans, what most of the customer do.

- The Tools should be installed as well, as it is very helpful in a case of support. Otherwise you have to connect to another system to administer the main system. Disk space cannot be the cause (today) for not installing the tools.

- There are still some customer references in the document (search for SBB or look at page 23)

Many thanks

Clas

[Former Member]

Hello Clas,

Thank you for your feedback. It is always nice to receive.

1. 4.6C - you are correct. I have not investigated this too much and I hope I never have a customer who asks me to upgrade the DB on a 4.6C system!

2. You are correct. I have updated the document and will publish it on SDN tomorrow.

3. Sure, this can be installed if needed. I don't prefer to use this - but this is a matter of choice.

4. I do not install nor recommend installing Tools for two reasons.

(A) I work on large Itanium systems and there is no IA64 version of management studio. Management Studio runs very slowly on IA32 emulation layer and I do not recommend using IA32 applications on IA64 systems - as a general rule.

(B) The customer I developed this procedure for is very security conscious. Almost nobody has terminal server access onto the SAP servers. All administration is done remotely and centrally using either the SAP CCMS or an IA32 management station. I suggest that there is seldom a requirement for the basis admins to log onto the servers and that the less people who have access the more stable the system will be. The servers can be started and stopped via MMC configured to read instance data from an LDAP source (eg. Active Directory).

Again the document is a guideline, you should adapt this to the specificities of your customer.

5. I don’t see any compromising data and in any case the screen shots show only the Sandbox server.

Thanks for your comments – always welcome.

If anyone has timing information for their upgrades I would like to include this. The upgrades I performed were on 16 way Itanium II servers with a very fast SAN and are probably not representative of most customers.

N.P.C

[rolfc_weber]

Hi,

I also want to give a little feedback to this nice document by N.C.P.

It’s not quite an error in the document, but I think the stated recommendation to use named instances should be some more conditional.

The problem is the resulting naming of database host when using named instances.

When using named instances it will be like <host>\<instance> (e.g.: saphost\P01).

This naming of the database host however conflicts with the RFC 952 naming convention:

RFC 952:

A "name" (Net, Host, Gateway, or Domain name) is a text string up to 24 characters drawn from the alphabet (A-Z), digits (0-9), minus sign , and period (.). Note that periods are only allowed when they serve to delimit components of "domain style names".

So the use of the slash (“\”) in the naming of the named instance can result in problems. SAP supports the use of named instances, but however not when running SAP in Switchover Environments because there the database host name must be confirm with the RFC 952 standard.

Another place where the use of a named instance can give problems is when the used SAP system is running a SAP J2EE engine. And here in special, when later on the TREX search engine has to be used against or with this J2EE Engine.

The reason for this is that the generated name of an index from the J2EE includes the database host name and will thereby include the slash sign that again will be an invalid name for the index.

My point is the following:

The use of named instances is nice, and can help a lot when doing server consolidation, but the use must be well considered and depends of the usage of the system.

As I see it, the recommendation should be as follows:

Running in switchover environment => No.

Running ABAP only systems => Yes.

Running ABAP+JAVA systems => Depends on usage

Running JAVA only systems => properly not.

But however this is my opinion about the recommendation of using named instances on SQL Server in this nice guideline by N.C.P. (Thanks a lot for writing and sharing it)

Regards

Rolf

[Former Member]

Hello Rolf,

You make some very good points here. Would you mind it I edit your post and add it as an appendix to the document?

Please advise and I will upload an updated version of the document to SDN.

Thanks

N.P.C

[rolfc_weber]

Hi N.P.C

You are welcome to use it.

I am glad my points can be used to improve your nice document....

Regards

Rolf

[Former Member]

Hi N P C,

I have gone through the pdf you uploaded and need clarification if the upgrade is the direct upgrade from 2000 to 2005 or is it a fresh installation of 2005?

Prashant

[clas_hortien]

Hi,

in N.P.C. document a upgrade from SQL 2000 to SQL 2005 is described. The named instace is SBP that gets upgraded in the document.

If you want to have a fresh installation of SQL 2005 you can run the sql4sap.vbs script on the installation DVD.

Best regards

Clas

[Former Member]

Hi,

I am going to upgrade it to 2005, so what do you suggest? Fresh installation over 2005

beside 2000 running and then restoring the data or direct upgrading to 2005?

The data size is 500GB.

Also please let me know the steps to follow?

Regards

Prashant

[clas_hortien]

Hi,

i would install a SQL 2005 beside SQL 2000 and copy over the database files to the new instance. There you can attach the database files directly. Follow note 151603 for the details how to detach, copy and attach the database. In this note the STM tools are mentioned as well. Run them, to do all the necessary after-attach work that it is necessary.

Please keep in mind, that some R/3 releases need special support package level to run on SQL 2005. You have to install these support packages BEFORE you upgrade to SQL 2005.

Best regards

Clas

[Former Member]

Hi Clas,

Thanks for the information.

But could you suggest if there is any other thing to do after an upgrade? Any scripts or instance installation something like that?

After the database is attached to 2005, how to activate 2005 for SAP. Should then 2000 should be stopped then?

Your input will be appreciated?

Regards

Prashant

[clas_hortien]

Hi,

the database upgrade is always part of a longer process and cannot be seen as a single task. The detailed steps necessary are described in the upgrade notes or guides for your R/3 release. For some R/3 release special support packages level must be installed BEFORE you do the upgrade to SQL 2005.

When you have attached the database to SQL 2005 you have to run the STM tools with the copy option. Within this STM tool you have to specify the CI so that the tool can moify the profiles. With this modified profiles the system will then connect to the SQL 2005 instead of the SQL 2000. SQL 2000 and SQL 2005 can run side-by-side, so there is no need to switch the SQL 2000 of if you still need it.

Best regards

Clas

[Former Member]

Hi Clas,

If there is 4 instances on the server, and while upgrading to 2005, do we need to update/upgrade all the 4 instances?

How to carry out the process then?

Thanks & Regards

Prashant

[Former Member]

Hi Prashat,

If the SQL instances are truly separate SQL instances then you do not need to update them all at the same time.

You will need stop all SQL instances during the FIRST upgrade as the client libraries (for client server communication) will need to be updated. I would recommend stopping SAP and all SQL instances for the FIRST upgrade. If you don't do this the SQL 2005 installer will ask you to reboot the operating system.

After the first instance is updated you will not need to stop any of the other instances - other than they shared client libraries they operate pretty much independantly.

Make sure you apply SQL 2005 SP2 at the same time.

Thanks

N.P.C

[Former Member]

Hi N P C,

I want to know how many companies have upgraded to SQL Server 2005 from 2000 and what benefits they have with it?

Can you give me such clients name or the contact persons name where it is installed?

Also what problem they face after upgrading to 2005? Is there any report for database consistency check before and after the upgradation? how it can be confirmed if everything was fine after an upgrade?

Your reply is highly appreciated?

Regards

Prashant

pcdhas@gmail.com

[Former Member]

Hello Prashant,

I cannot share customer names with you, at least not without the customers agreeing to this.

SQL 2005 benefits : DB mirroring, performance, DB Snapshots, Online Indexing, DB partitioning, SNAC, MARS - a very long list.

You need to make sure that you run a DBCC one SQL 2000 and then make sure the ABAP dictionary and DB are consistent before the upgrade.

Otherwise if you follow my procedure (above) it will work. I have done dozens of these upgrades and all of them have been a sucess.

N.P.C

[clas_hortien]

Hi,

you should check

http://www.microsoft.com/sql/solutions/upgrade/default.mspx

There are some Case Studies from Customer Upgrade Projects and a bunch of information about the upgrade to SQL 2005, like a whitepaper about the benefits of upgrading:

http://www.microsoft.com/sql/techinfo/whitepapers/why-upgrade.mspx

Best regards

Clas

[Former Member]

Hi N P C,

Is there any report which needs to run prior the upgrade for checking the database constitency and after the upgrade. What exactly I am looking at is what is the proof that while during the upgrade, some of the rows will get deleted. What is the proof that everything went okay? Is there anything ???

Regards

Prashant

[Former Member]

Hi Clas,

Thanks to you and N P C for the reply.

As asked to N P C, what is the proof that the upgrade will not have any problem. And what are the problems face by any customers after the upgrade. What are the benefits they got after upgrading to sql 2005?

Regards

Prashant

[Former Member]

Hello All SQL 2005 users,

I will update my upgrade document above shortly, until then here are some important points:

1. I recommend upgrade to Service Pack 2. Especially for BW users. Please see the new storage compression Note 991014 - Vardecimal compression for BW on SQL Server

2. If you use SP2 you <b>*MUST*</b>, once again MUST upgrade your kernel as per Note 1067103 - Transaction context lost

3. Multi-SID MSCS clusters are now supported. You can find a new installation guide and SAPInst on service.sap.com

4. Named Instances *ARE* fully supported in all cases including Java and/or MSCS installations

Thanks

N.P.C

[Former Member]

Hi Clas,

Which method out of “In-Place Upgrade” and “Side-by-Side Upgrade” would you recommend for SQL 2005 upgrade on MSCS.

regards

Anand