A Technical Case Study: Fast and Reliable Backup and Restore of Multi-Terabytes Database over the Network

Summary: Database sizes increase constantly, as do requirements for access and availability. At the same time, it is more important than ever to have a fast and reliable backup and recovery plan in place. This document discusses the challenges of designing a robust backup and restore solutions for very large databases (VLDBs). Using a real-world example, it demonstrates how to make the best use of the backup and restore features of SQL Server 2008 to help create a fast and reliable backup and restore plan for VLDBs over the network.

Backing up a 2-terabyte database onto a local hard drive and then restoring it is fast, but this simple process does not provide adequate protection against worst-case scenario. On the other hand, backing up the database over the network to another location provides protection against our worst-case scenario, but with a typical 1-gigabit-per-second (Gbps) connection, performance suffers. When we looked at this situation, as a baseline test, we backed up the 2-terabyte database over a 1-Gbps network link to another data center 10 miles away. This took more than 24 hours, which was far from acceptable. We needed to design a solution that would remove the bottleneck, enabling us to complete the backup within that time-to-restore window specified in our SLA.

Assembling all the small pieces of the backup puzzle and running endless tests, we were able to reduce the backup of the 2-terabyte database to 36 minutes. The solution, which we termed “multistreamed backups over the network”, used eight 1-Gbps network connections. Jumbo frames were configured for each network card, and each network card was then aggregated to layer 2 backup switches to one 10GE (10Gbit Ethernet) line that ran to the second site. Backup now took 2 hours and 15 minutes. Enabling SQL Server 2008 backup compression finally brought the time down to 36 minutes.

For details please refer to the whitepaper:

http://download.microsoft.com/download/d/9/4/d948f981-926e-40fa-a026-5bfcf076d9b9/Technical%20Case%20Study-Backup%20VLDB%20Over%20Network_Final.docx