Microsoft’s SAP Deployment and SQL Server 2012

  • Article

Microsoft’s internal ERP solution is the SAP Suite of products including SAP ECC 6.0, SAP BW and SAP SCM. Microsoft tests new SQL Server releases and Service Packs on not only test SAP systems but also the core ECC Production system that runs Microsoft worldwide. The ECC 6.0 single instance, single client system is accessed by 95,000 users worldwide and transacts the majority of the $65B USD in revenue each year. SQL Server 7, SQL 2000, 2005, 2008 R2 were all tested on the core ECC Production system before these products were released. Microsoft will follow the same process with SQL 2012 so that customers can have the confidence that no version of SQL Server is ever released without running Microsoft’s own production SAP system for several months. We want to catch up with this article and give a status update on our internal deployments.

Since January 2011 we tested SQL Server 2012 builds in a sandbox representing around half of the capacity of our productive SAP ERP systems. Besides testing the functionality used by SAP, one of the major tests areas were around our new HA/DR functionality offered in SQL Server. We combined this set of functionality under the name AlwaysOn in SQL Server 2012 (https://msdn.microsoft.com/en-us/sqlserver/gg490638 ). All the old methods like Windows Clustering, Database Mirroring and Log-shipping still ship in SQL Server 2012. The new AlwaysOn functionality which allows more flexibility and functionality like multiple secondaries or online reading from secondaries, is offered in parallel to the ‘old’ functionality. At the beginning of the tests in the sandbox we started with Database Mirroring as HA method. Once we proofed that Database Mirroring was working flawlessly we moved the High Availability configuration to AlwaysOn with 2 replicas (one principal and one secondary) basically replacing Database Mirroring. Since July we had a 3rd replica ( one additional secondary) added into the HA/DR configuration to test the replacement of Log-shipping to our Disaster Recovery Datacenter. In opposite to the combination of Database Mirroring plus Log-shipping as we used it so far, we now had all three servers using the same AlwaysOn functionality to replicate the changes. We had one place to configure the HA/DR configuration and one place to monitor whether everything would run fine. All tests around our new HA/DR technology went pretty well. Feedback out of these tests to SQL Server Development was used to improve and fine tune AlwaysOn technology. From that point of view, the early testing we did together with our SAP Basis team can be considered as very successful by Microsoft IT as well as SQL Server development. Besides AlwaysOn we tested a new trace flag which I documented earlier here . This new functionality also proved to be a full success, eliminating some well-known issues we sometimes encountered with query plan generation.

We moved our sandbox system to a late CTP3 Refresh build in July and continued to run testing on it checking different features and functionality.

In mid of August we focused our attention our official SAP ERP test instance which is located in our DR site and is used for Business Regression, workload and scalability testing. As such this system is an exact copy of our productive SAP ERP system. We moved that SQL Server 2008 R2 instance onto SQL Server 2012 CTP3 Refresh with and in-place upgrade. We replaced Database Mirroring in this test system as well with AlwaysOn in a 2-replica configuration. Scalability tests with AlwaysOn using our Business Regression Test Suite plus index rebuilds executed in parallel did show that the replication of transaction log data between primary and secondary is scaling way better than DBM ever did. Scenarios of high volume workload could cause effects like described here. Whereas the new designed data transaction log transfer did not show any problems. Experience made in our SAP Test System were extremely positive and paved the way to move SQL Server 2012 into our productive system as planed mid of November.

As a last instance we moved our productive SAP ERP instance to refreshed SQL Server 2012 CTP3 Refresh build on 11/11/2011. It was during our usual quarterly maintenance which we usually use to get new SAP kernels and SAP Support Packages applied to our SAP ERP system when we performed in-place upgrades of our SQL Server 2008 R2 instances to SQL Server 2012. The sequence of the steps taken looked like:

  1. Upgraded the Log-Shipping destination in-place to SQL Server 2012 two days before downtime
  2. Upgraded the former database mirror to SQL Server 2012 one day before the downtime
  3. Shutdown the SAP ERP system at the beginning of the downtime.
  4. Stop Database Mirroring
  5. Opened former mirror database and let it adapted to the SQL Server 2012 schema upgrade steps.
  6. Afterwards we created an AlwaysOn node (our new HA/DR functionality – there will be a series of Blogs about this following) with one replica and a listener name (virtual name).
  7. In parallel the SNAC (SQL Native Access Client) got deployed to all the application servers.
  8. After these steps were completed we needed to replace the server name and failover partner name in several locations of the SAP configuration with the new virtual/listener name of the AlwaysOn configuration.
  9. After this was done we got the system up and running again to perform the normal quarterly maintenance which usually takes care on upgrading various SAP support packages, SAP kernel exchange and many transports which usually implement new functionality into our SAP ERP system.

After the SAP system was up and running again:

  1. We upgraded the former principal server to SQL Server 2012.
  2. We deliberately didn’t synchronize the former principal and now secondary not to the HA configuration while all the support package imports and transports because we usually always keep the mirror server on the state of the start of the downtime in order to be able to recover in a fast way if insurmountable problems during the support package application would occur

After all support packages and transports were applied to the SAP ERP system, we performed the following steps

  1. We then synchronized this SQL Server 2012 instance with the transaction log backups of the current principal and added it as a replica into the AlwaysOn Availability Group.
  2. We performed several failover tests with the SAP ERP system still running. We wanted to check with these tests whether the AlwaysOn configuration was sound and correct.

So far the system behaves perfectly and shows great performance. Not a single issue came up in the week that we are productive. So far so good.