How does Microsoft perform Backups in their SAP system landscape

[Juergen Thomas]

First published on MSDN on Mar 28, 2008

This probably is one of the most asked questions we always get related to Microsoft’s SAP system landscape. Well, the second most popular question actually, after the question of how large our SAP ERP system is. Therefore let’s discuss how we solve the problem of backups in Microsoft’s SAP landscape.

Different SAP systems in use

The first SAP system introduced in Microsoft was an R/3 system we refer to as SAP ERP system today. This system still is around running on SAP release 4.7E and meanwhile is running on Unicode as well. The current data volume of the database is around 5.4 TB as of March 2008

Besides our SAP ERP system we are looking into a SAP BW system of an older release, a SAP BI system on Netweaver 7.0 platform, a SAP SCM system, a SAP GTS system, an E-Recruiting system and a Solution Manager System. All of those other systems are in more moderate sizes of a few hundred GB to 1.5TB data volume. Sure enough we at least run 3 instances of each of those systems above. In the case of our SAP ERP system it currently looks more like 5 different instances at least.

High-Availability configuration

The high-availability configuration of each system is dependent on the importance to the business and business processes. That puts the SAP ERP in Microsoft’s landscape right into the center of attention as the most important system. In order to protect sufficiently from storage mal-function, Microsoft IT decided to maximize the availability of the SAP ERP system by using SQL Server Database Mirroring with Automatic Failover to protect within the same datacenter. To support a disaster recovery datacenter SQL Server Log Shipping is used for the productive SAP ERP instance as well. All other systems also use Log-Shipping in order to supply the data to our disaster recovery datacenter. For all systems, the test instances completely reside in the disaster recovery data center. This includes application servers as well as the dedicated database servers. Except the Solution Manager implementation we look at 3-Tier configuration for all of those different systems (exceptions might be some development instances). The servers running the test instances of a SAP system will have pre-installed production instances on them which are disabled. From a database point of view, the database server running the database of the test instance also functions as secondary in the Log-Shipping for the production database server. Due to lower importance and smaller databases of the other SAP systems, besides SAP ERP, these other systems are not using SQL Server Database Mirroring, but use Microsoft Cluster Services.

See also http://download.microsoft.com/download/d/9/4/d948f981-926e-40fa-a026-5bfcf076d9b9/SAP_SQL2005_B... for different HA possibilities and their abilities in an SAP environment

Actual execution of backups

Despite the fact that the databases have different sizes, the decision was made early on that ALL systems are backed up in the same method. This simply was done to avoid confusion and to stream line a process of backup/recovery independent of the different systems. That unification goes down to the detail of the directories where backups get written to. This also is true for the QA and the Development instances of each of the different SAP systems.

In order to keep the costs for the complete backup infrastructure under control, Microsoft IT decided years back to invest into a central backup center. This gave all the business systems the opportunity of leveraging this service instead of every group looking for own hardware and methods to backup and restore. Years back, the SAP Basis Team of Microsoft decided to leverage the services of the corporate backup group and hand the responsibility to get database backups on tape to that group.

Nevertheless it figured out that for fast recoveries, but especially for refreshing sandbox systems or creating sandbox systems, the services of the corporate backup group was a bit too in-flexible. Also, to still keep the schedule of those SAP instances in their own hands and to be free to issue backups whenever needed for instance refreshes or one time creation of sandboxes, it was decided to perform backups against SAN disk devices attached to the specific database server. Corporate backup will pull those backups over to tape at their convenience. In opposite to the SAN volumes which contain the data files and log files of the different SAP instances, the SAN volumes for keeping the backups are configured as RAID5 and not as RAID10.

Backup Compression

In order to maximize resources in space usage on SAN disks as well as copying backup to the corporate backup facilities, all the backups except transaction log backups get compressed (one exception – see next chapter) while performing the backups. For SQL Server 2005, Quest SQL Litespeed got used for this purpose. Since the productive SAP ERP system is running on SQL Server 2008, Microsoft IT uses the backup compression which got built into SQL Server 2008 and which is part of SQL Server 2008 Enterprise Edition. Experiences so far were that the new SQL Server 2008 Backup feature delivers similar compression with similar resource consumption as SQL Litespeed does. Dependent on the configuration of SQL Litespeed, even advantages for SQL Server Backup Compression could be observed. In the following section, we will give compression rates which we achieved using SQL Server 2008 Backup Compression. The query to get these compression rates look like:

select backup_size/compressed_backup_size,* from msdb..backupset
where compressed_backup_size is NOT NULL
and type = 'I' and database_name = <myDBNmae>
order by backup_start_date desc

‘I’ in the column type represents a differential database backup, where ‘D’ will represent a full online database backup and ‘L’ will specify a Transaction Log backup

All SAP systems, but the SAP ERP system are running on SQL Server 2005 and still use SQL Litespeed for backup compression

Transaction Log backups

Since all SAP systems of Microsoft have a secondary instance in the disaster recovery data center, there was a need to Log-Ship the transaction Log backups as frequently as possible. For doing so with all the SAP systems, Microsoft uses SQL Server Log-Shipping Wizard, setting the frequency of a transaction log backup to 1 minute. This is as low as one can go. It for sure produces a lot of backups, but on the other side, supports the notion of potentially losing the least amount of data. As with the Log-shipping Wizard configured, the backups are copied over to the secondary immediately. Additionally the transaction log backups are getting copied to the Database Mirror server as well. Just to have another redundant copy of those backups available

Most problems around this space is the network bandwidth available compared to the volumes of transaction log backups. Especially building up the new SAP BI system, problems came up due to latency copying larger backup files over to the secondary server. Since external backup compression tools like Quest SQL Litespeed can’t be used by SQL Server’s Log-Shipping logic, the problem remains up to the point where this specific system will get moved to SQL Server 2008, where SQL Server 2008 Backup Compression for Transaction Log backup can be used with the Log-Shipping configuration.

In the specific case of Microsoft’s SAP ERP system which already runs on SQL Server 2008, transaction log backups are getting compressed. The backup compression rate we observe for Transaction Log backups is in the area of 2.0 to 2.5 or a disk savings of around 60%.

In order to enable Backup Compression for SQL Server 2008 Log-Shipping one needs to set the parameter in the SQL Server Instance Configuration:

sp_configure 'backup compression default', 1
go
reconfigure with override
go

This parameter change will enforce backup compression with every native T-SQL backup command. However calls for backup via 3 ^rd party software using the SQL Server VDI interface will not use backup compression based on this configuration change.

Daily backups

The pure volume of especially the productive SAP ERP system and the associated consumption of infrastructure resources simply didn’t allow performing a full database backup every night. Therefore a differential backup is performed every evening at 5pm. Other reasons for performing differential daily backups only also are tied to the internal costing model between the central backup services and its customers. Dependent on the day of the week and the workload on the productive SAP ERP system, the differential backup takes between 10 and 40min. As described earlier, this backup takes usage of the SQL Server 2008 Backup Compression feature. The compression rate of the differential backups varies between rates of 4 to 5. In the case of the differential backup, we target two disk devices. E.g.

BACKUP DATABASE <myDBName>
to DISK='X:first_backup_deviceDIFFDIFF_Bkup1.bak',
DISK='X:first_backup_deviceDIFFDIFF_Bkup2.bak'
WITH DIFFERENTIAL , COMPRESSION , INIT;

Please note that the option ‘COMPRESSION’ is not a valid option for predecessor releases of SQL Server 2008 and also is a valid option with SQL Server 2008 Enterprise Edition or Developer Edition only. The option ‘COMPRESSION’ also is not mandatory to be named in case the instance configuration is set as described in the section ‘Transaction Log Backups’

Full Database Backup

Takes place once the week on Saturday evening. The backup usually takes around 2.5h which indicated a nice throughput of around 2GB/h or more than 500MB/sec. The backup is timed in a way that it doesn’t collide with an optional downtime window our Basis team can take every few weeks from Saturday 11pm to Sunday 1pm. Also this backup is compressed by SQL Server 2008 Backup Compression. In the specific case of Microsoft’s SAP ERP system, the compression rate by SQL Server 2008 Backup Compression is around 5. For the full online backup, we target 12 disk devices like:

BACKUP DATABASE <myDBName>
to DISK='X:full_backup_deviceDIFFFull_Bkup1.bak',
DISK='X:full_backup_deviceDIFFFull_Bkup2.bak',
DISK='X:full_backup_deviceDIFFFull_Bkup3.bak',
DISK='X:full_backup_deviceDIFFFull_Bkup4.bak',
DISK='Y:full_backup_deviceDIFFFull_Bkup5.bak',
DISK='Y:full_backup_deviceDIFFFull_Bkup6.bak',
DISK='Y:full_backup_deviceDIFFFull_Bkup7.bak',
DISK='Y:full_backup_deviceDIFFFull_Bkup8.bak',
DISK='Z:full_backup_deviceDIFFFull_Bkup9.bak',
DISK='Z:full_backup_deviceDIFFFull_Bkup10.bak',
DISK='Z:full_backup_deviceDIFFFull_Bkup11.bak',
DISK='Z:full_backup_deviceDIFFFull_Bkup12.bak'
WITH DIFFERENTIAL , COMPRESSION , INIT;

In regards to the efficiency of backup compression, please note that the compression rate is very specific to the re-organization state of tables. A newly loaded database might experience lower compression rates.

Consistency Checks and Restore Checks

Every week towards the end of the week, a full backup is taken plus all the differential backups since and are restored on a sandbox system. This server is used during the week for eventual destructive testing. However immediately after restoring the database backups, a consistency check is run on the restored database in order to find out whether there might be eventual physical inconsistencies.