Upgrade to SQL Server 2008 or SQL Server 2008 R2 with minimal downtime

[Juergen Thomas]

First published on MSDN on Nov 29, 2009

In the last article I announced Microsoft running productive on SQL Server 2008 R2. In this article I’d like to tell more about how this upgrade was done with 5min downtime.

Database Configuration of the Microsoft SAP ERP system

As described in an earlier article on this blog ( http://blogs.msdn.com/saponsqlserver/archive/2008/03/28/how-does-microsoft-perform-backups-in-t... ), the HA configuration is based on synchronous SQL Server Database Mirroring. This means there are three SQL Server instances which need to be upgraded. To cover an outage of the main datacenter there also is another SQL Server instance in our DR site. Means all in al,l we are looking into four SQL Server instances with three databases of a volume of around 5TB each. Only one of these databases is accessible (Principal of the Database Mirroring configuration). The databases on the other 2 instances are in a non-accessible mode since steady recoveries are executed.

What needs to be changed during a SQL Server In-Place Upgrade?

Let’s assume that the SAP Application already is on the correct Basis Support Package Level and also is running with the minimum necessary patch for SQL Server 2008 R2 (the same procedures also applies to SQL Server 2008 In-Place Upgrades). Focusing on SQL Server, the following components need to be upgraded:

·         Client side connectivity layer of SQL Server (SNAC): Every new SQL Server release comes with a new SNAC layer, which can exist in parallel to older versions on a server. The SAP Database porting layer will choose the correct SNAC version dependent on the database it connects to. Therefore the SQL Server 2008 SNAC DLL necessary, can be installed on the application server while the system is up and running. The MSI installer package for SNAC10 can be found on the SQL Server installation DVD under <platform>setup<platform>. Look for the file sqlncli.msi. Just start the installation. It usually finishes within less than 1min and no reboot is required.

·         SQL Server Management tools: In order to keep downtime low, most customers today stopped installing the SQL Management tools on all application servers. The only real SAP application server which requires some SQL Server Management tools actually is the server from which a SAP release upgrade is run. Only during a SAP release upgrade, SAP is using a component of the SQL Server Management tools. Hence for a normal SQL Server release upgrade, the only servers where the SQL Management tools need to be installed on are the database servers. And that is done with the SQL Server In-Place upgrade

·         SQL Server Relational Engine, SQLAgent, SQL Browser, SQL VSS Writer: These are the components which usually run on the database servers and which will be upgraded in the SQL Server In-Place upgrade.

Sequence of Upgrade in Microsoft’s SAP ERP configuration

The goals of the upgrade from SQL Server 2005 to SQL Server 2008 and now to SQL server 2008 R2 were:

·         To have minimal impact on HA and DR configuration: Up to SQL Server 2005, SQL Server did not support the upgrade of non-accessible databases. This meant that during each Upgrade logshipping or secondary databases in a database mirroring setup were lost and had to be rebuild from a backup after the upgrade of the primary was finished. During the time of the upgrade of the primary and the restore of the backup on the mirror and logshipping databases the system was exposed with only 1 version of the database available.
SQL Server 2008 and SQL Server 2008 R2 finally allow an In-Place upgrade of in-accessible databases and thus eliminate the problem of the extended time of exposure.

·         Minimize the downtime during the SQL Server upgrade to a few minutes (downtime depending on release and target version of SQL server).

So the sequence of steps executed two weeks ago looked like:

·         One day before the actual downtime for the upgrade was taken:

o   Suspend SQL Server Log-Shipping

o   In-Place Upgrade of the Log-Shipping instance

o   Resume Log-Shipping – The transaction log backups of a lower SQL Server release can be applied by SQL Server 2008 R2 (or SQL Server 2008) to a passive database

o   Install SQL  Server SNAC10 on all application servers

o   Disable automatic failover in the database Mirroring configuration

o   Execute the In-Place upgrade of the SQL Server instance which functions as Witness in the DBM configuration

o   Enable Witness again – The SQL Server 2008 R2 (or SQL Server 2008) witness can guard over a DBM configuration of a lower SQL Server release

·         Some hours before the downtime is taken:

o   Suspend Database Mirroring

o   Execute the In-Place Upgrade of the Mirroring instance

o   Resume Database Mirroring

o   Before downtime is taken, make sure that the principal and the mirror instance are synchronized

·         Downtime phase:

o   Stop the SAP application

o   Execute a manual failover. This will initiate the following actions:

§  Since the failover goes from a lower to a higher SQL Server release, Database Mirroring is getting suspended

§  The database is opened. Despite the upgrade to SQL Server 2008 R2 (or SQL Server 2008) the passive database schema is still on the state of the source version of SQL Server. This is changing in this activity. Now the SQL Server system tables of the SAP database are upgraded to the state of SQL server 2008 R2 (or SQL Server 2008). Experience wise this takes around 8-10min in the case of upgrading from SQL server 2005 to SQL server 2008 and less than 5min in the case of upgrading from SQL Server 2008 to SQL Server 2008 R2.

§  After the schema upgrade is finished, the database opens. This is the moment the SAP CI and application instances can connect against SQL Server again

§  In case of upgrading from SQL Server 2000 or SQL Server 2005 to SQL Server 2008 or SQL Server 2008 R2, one needs to run the SAPTools for MSSQL Server as described in the SAP upgrade documentation on page 20. However this steps usually takes a few minutes only

§  If this all works fine, the downtime is finished essentially after 5-15min

·         After Downtime:

o   Ensure Log-Shipping from the new principal to the Log-Shipping destination is working – All changes which were done to the SQL Server system tables in the SAP database are recorded in the transaction log. Hence those changes become part of the next transaction log backup, which gets copied to the Log-Shipping destination and then restored to the SAP log shipping database on that instance

o   Now is the time to get former principal upgraded. Since the SQL Server Management Tools are upgraded as well, this step could take as long as 20-25min. But SAP can be running w/o impact during this upgrade since is connected to the new principal server.

o   After this step is successfully finished, Database Mirroring is getting resumed. The old principal will realize that there is a new principal and that it now is in the role of a mirror. Changes applied to the new principal will be synchronized with the ‘new’ mirror. – As with the Log-Shipping destination, changes done to elevate the SQL Server system tables to the more recent SQL Server release are recorded in the transaction log and now via DBM get applied to the database which before still was on the source SQL Server state.

o   If both instances are synchronized, enable the Witness again in the DBM configuration

Around two years ago when our SAP Basis team practiced this sequence for the first time, our down time was around 10 min before the SAP CI could connect again. In the current case moving from SQL Server 2008 to SQL Server 2008 R2, the downtime for our SAP ERP was less than 5minutes. Reason is that there hardly are any changes on SQL Server system tables going from SQL Server 2008 and SQL Server 2008 R2

Using Windows Clustering instead of SQL Server Database Mirroring, Rolling Upgrades also are possible. Documentation which covers that case can be found on: http:// msdn.microsoft.com/en-us/library/ms191295.aspx