SQL Server Backups

---

Mission-critical adoption of SQL Server has grown significantly over the past couple of years. According to a Gartner Group study, Microsoft has moved into first place in the Windows relational database segment with a 39.9 percent of the market, followed by Oracle with 34 percent. This growth has been driven largely by recent advances in data protection that reconcile the business needs of round-the-clock availability and high transactional throughput with the time demands of backup and recovery. Technical cooperation between Computer Associates, Microsoft, Hitachi Data Systems, and leading storage vendors has simplified and accelerated the backup and recovery process, resulting in the fastest database recovery performance ever recorded on any distributed platform: 2.21 TB per hour. This was achieved through the use of ShadowImage technology as well as heightened operational integration between the database, hardware, and backup/restore engine.

Breakthrough in Backup and Recovery Performance

Depending on the CPU and the size or type of backup being performed, processor utilization is often above 50 percent on critical systems during backup and restore operations. Some users even report utilization rates rising to 85 percent or more. During these periods, transactional traffic can grind to a halt and impatient users may go elsewhere to satisfy immediate needs. What is needed is an approach to storage that minimizes the bandwidth and CPU load during backup so transactional traffic continues unaffected and mission-critical systems stay operating at maximum efficiency. At the same time, the business-critical nature of the application is driving the need for rapid recovery, so this method must also provide a means of recovery for multi-terabyte databases at a far more rapid rate than is currently being experienced. These conditions have been satisfied by the BrightStor offering, a storage design for the Windows platform based on technology developed through the combined efforts of Computer Associates, Microsoft, and storage leaders such as Hitachi Data Systems, Brocade, Emulex, and Quantum.

This architecture was tested at Microsoft's Redmond campus using a 2.5-TB Microsoft SQL Server 2000 Enterprise Edition database. Technicians optimized these tests around the recovery performance of SQL Server, minimizing the impact on ongoing transactions during backup, and operational simplicity. These tests resulted in the following (see Exhibit 3):

• A 2.6-TB/hour archival backup speed

• A recovery rate of 2.2 TB/hour using 32 drives

• Average CPU utilization on the 8-CPU backup server of 26 percent during backup and 12 percent for recovery

• A time of 68 minutes to back up a 2.5-TB database to tape and 88 minutes to recover it

• Complete recovery of a 2.5-TB database in 11 minutes when done directly from a ShadowImage copy

Such impressive recovery and backup performance was achieved via automated integration and coordination of applications and hardware, in conjunction with implementation of ShadowImage technology. Essentially, a ShadowImage is a quick, point-in-time copy of data that allows databases and applications to remain online. Based on Hitachi Data System's split-mirror technology, extra mirrored sets of the data can be split off from the primary volume to create very fast backups.

SQL Server stabilizes the file system to allow a safe hardware snapshot, then BrightStor Enterprise Backup controls the entire sequence of actions, informing SQL Server to allow the ShadowImage copy and also controlling backup to tape. During the few seconds that a ShadowImage is being taken, SQL Server allows all database reads. Writes, though, are temporarily suspended for a few seconds. Upon completion of rapid point-in-time Shadow-Image copy of SQL Server, the database executes any queued writes and returns to normal operation. While taking the ShadowImage during testing, SQL Server processed 983 transactions per second consisting of a typical online transaction-processing environment.

Once the ShadowImage is completed, SQL Server continues and as far as it is concerned the backup is complete. As the data on the secondary volume is essentially a valid backup, this data can then be rapidly streamed to tape using BrightStor Enterprise Backup as an additional level of backup for archiving and long-term and off-site storage.

Performance is improved so markedly because this architecture accomplishes backup and recovery at a hardware, not a software level. Within the Hitachi Data Systems' storage subsystem, both the primary and ShadowImage volumes are replicated on demand to provide a point-in-time copy. By operating at a hardware level, it takes a few seconds to complete a snapshot of the 2.5 TB of data using the ShadowImage technology. The ShadowImage volume can then be fed to tape directly by a backup server. Thus, a full backup can be taken without impacting SQL Server performance (Exhibit 1).

Exhibit 1: During and After Snapshot Backup

Similarly, when it comes to data recovery, the use of snapshots greatly speeds the process. If the required point-in-time copy already exists on the storage subsystem, for example, it is not necessary to recover the data from the tape library. During benchmarking, for instance, it took only 11 minutes to recover the 2.5-TB database to a point where transactions were running using a recent ShadowImage copy. Even when data had to be copied from tape to the ShadowImage volume, it took a total of 88 minutes to fully restore the database. In both cases, BrightStor EB ensures that the secondary ShadowImage volume and primary volume have been resynchronized before allowing SQL Server to commence recovery operations. These speeds are achieved due to the data being available to the database server almost immediately because of synchronization between the primary and ShadowImage volumes. Backup server CPU utilization averaged 12 percent during recovery.

In addition to greatly accelerated backup/restore and nominal overhead on the server, snapshots can be used in combination with differential and log backups providing the flexibility required to roll forward to a user-specified point in time, if necessary, while retaining the ability to backup large databases without disruption.

---