Writer: Thomas H. Grohser (=tg=)Contributor: Lindsey AllenTechnical Reviewers: Sanjay Mishra, Lubor Kollar, Stuart Ozer, Thomas Kejser, Juergen Thomas, James Podgorski, Burzin Patel
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 Case Study-Backup VLDB Over Network_Final.docx
About Lindsey.allen
Lindsey Allen has been working with SQL Server and Business Intelligence for the past 15+ years. She joint Microsoft Consulting Services summer of 1995 and transitioned to SQL Server Product Group summer of 2005. Lindsey is currently managing SQLCAT Best Practice and Customer Lab team.
|
sql, server, best practices, whitepapers, analysis services, data mining, olap, datawarehouse, datawarehousing, availability, clustering, capacity, collation, data types, data warehouse, database, design, index, mirroring, optimization, partitions, performance, precision, processing, querying, scalability, security, reporting services, integration services
|
|
