Upgrading your SAP databases to SQL Server 2008 R2 using the System Copy Methodology

Recently I was onsite with a customer and worked on a checklist for them to upgrade to SQL Server 2008 R2 (S2K8 R2). The upgrade of the database was performed by backing up and restoring of SQL Server 2005 database to a S2K8 R2 instance. The method used for the upgrade is referred in the SAP space as a System Copy using Database Specific tools. Some asked why was the System Copy method used for the upgrade, well primarily because the customer wanted to also upgrade to Windows 2008 R2 (W2K8 R2), but also because it is a method that is very clean and quick from the customers point of view (customer’s point of view). It eliminates the customers concerns surrounding remnants of the old platform existing.

The main reason for this blog today, is because there is no documentation surrounding the system copy methodology for upgrading to S2K8 R2. In the document, Upgrading and Installation SQL Server 2008 R2, currently available on the SAP marketplace the in-place upgrade methodology is discussed. Not because System Copy methods like Backup/Restore or Detach/Attach are not approved, but simply because at the time the documentation was produced based on what had been performed by the MSIT team headed by Elke Bregler and Hans Reutter. So in order to provide additional guidance surrounding the upgrade process it made sense to write a blog about this method for upgrades.

Okay, so where do we start. In the SAP space, we begin by gathering information from the SAP Marketplace. Intellectual property (IP) like the following SAP notes and documents need to be reviewed thoroughly while planning the upgrade. The following is a list of notes that we went through for planning the upgrade.

· SAP Note 1152240 - Setting Up Microsoft SQL Server 2008 R2

· SAP Note 1476478 - Release planning for Microsoft SQL Server 2008 R2

· SAP Note 151603 - Copying an SQL Server database

· SAP Note 551915 - R3 won't start after database restore or database copy

· SAP Note 151603 - Copying an SQL Server database

· SAP Note 98678 - SQL Server Connection Issues

· SAP Note 683447 - SAP Tools for MS SQL Server

· Upgrade to and Installation of SQL Server 2008 (R2)

· SAP Note 1237682 - Configuration Parameters for SQL Server 2008

These notes and documents are a good starting point for planning the upgrade. However, there is still additional guidance that we used from the customer standard practices of restoring a database. For example the customer will backup the database end points, security configuration, master, msdb, etc… in order to be able to reproduce the environment in the event of having to failback to the previous version of the database. However, they also script out some of these items (endpoints, security, jobs, etc…) in order to recreate them in the new database version. We will not get into detail regarding those areas, just keep in mind that they are part of the task list that will be provided.

 

· Now let’s get to the meat and potatoes of this blog. First validate that your installation follows the requirements for upgrading found in SAP Note 1476478 - Release planning for Microsoft SQL Server 2008 R2

.  The SAP note contains the SAP versions, support package and dbsl/kernel levels supported for the upgrade to S2KR2. The following tables consist of the list of steps we followed broken down into Pre-Upgrade, Upgrade and Post Upgrade phases.

 

Upgrade Preparation

· Validate Support Package level: (SAP Note 1476478)

· Validate Kernel/DBSL level: (SAP Note 1476478)

· Script out logins

· Script out mirroring endpoint

· Capture db mail configuration

· Script out jobs

· Script out alerts

· Script out operators

· Capture SQL Agent Configuration

· Capture SQL configuration sp_configure

· Capture trace flags used

· Capture server settings such as page file, local admins, installed applications

· Provision backup LUN

· Install Litespeed

· Install SQL 2008R2 native client on SAP application servers

· Execute stored procedure sap_droproc ( SAPNote 151603)

· Backup system and user databases using Litespeed

 

Note: Customer uses Litespeed for compressed backups of SQL Server 2005 databases. In SQL Server 2008 and higher backup compression feature is an available feature to customers without the purchase of 3^rd party tools.

 

 

UPGRADE

· Install Windows 2008R2

· Add LUNS and format with 64K block size

· Install applications (Nimsoft, HDLM, Emulex, Broadcom, Symnatec, CommVault)

· Create SQL folders on the drives

· Install SQL 2008R2 with collation SQL_Latin1_General_CP850_BIN2

· Install Litespeed

· Setup server settings (Vol maint tasks, lock pages)

· Add logins

· Add mirroring endpoint

· Configure db mail

· Add jobs

· Add alerts

· Add operators

· Configure SQL Agent (Upgrade to and Installation of SQL Server 2008 (R2))

· Configure SQL Server (SAP Note 1237682)

· Add trace flags

· Restore databases using LiteSpeed

 

 

POST-UPGRADE

· Execute SAP Tools for Microsoft SQL Server (Upgrade to and Installation of SQL Server 2008 (R2), SAP Note 683447)
· Start SAP applications
· Validate SAP applications (executer Transaction Code SICK)
· Change db compatibility to 100 (if necessary)
· Execute DBCC UPDATEUSAGE)
· Execute sp_updatestats (Upgrade to and Installation of SQL Server 2008 (R2)document)
· Begin compression of all tables · Uninstall the SQL Server 2005 Client Components from the SAP Application servers.

Note: Make sure that when you use the SAP Tools for SQL Server you choose the “Database Upgrade Completion” option.

Pretty simple right! Keep in mind that as you read through the "Upgrade to and Installation of SQL Server 2008 (R2)" document you will find on page 5 of the following:

 "You upgrade an existing SQL Server 2000 or SQL Server 2005 instance that is already running an SAP system to SQL Server 2008 (R2). SAP does not support any upgrade method other than that described here. "

 

Even though the usage of the system copy method is not listed in the document, the method is fully supported. A full document with step-by-step instructions and screenshots will be avaialble end of October .