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.
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
Pingback from My Weekly Bookmarks for September 14th | Brent Ozar - SQL Server DBA
Pingback from Pizza & Beer during 24 hours of PASS :Andr?? Kamman
links for 2009-09-24
Очень часто получается так, что реальные бизнес -требования оказываются сложнее, чем возможности мастеров
Очень часто получается так, что реальные бизнес -требования оказываются сложнее, чем возможности мастеров...
По материалам технической статьи Майкрософт: A Case Study: Fast and Reliable Backup and Restore of a
По материалам технической статьи Майкрософт: A Case Study: Fast and Reliable Backup and Restore of...
Pingback from Backup and Restore of VLDBs « Systems Engineering and RDBMS
Πρόσφατα έπεσα μούρη με μούρη σε μια παρουσίαση του Kevin Cox που είχε το παραπάνω θέμα. Επειδή είναι
The network changes implemented to speed up the backup throughput will have an impact on the general database performance (reports/ETLs/ad-hoc queries). Was this impact also captured? If you steam 2TB (or more) through the database server, the impact on memory management and CPU must be heavy. We are currently experiensing performance issues due to the backups pushing the CPU load to 90+ % (4x 6 cores). We did managed to identify that the amount of files (database files) have a use impact because SQL start multiple parallel backup process threads and this kills the CPU. At the moment I'm trying to consolidate files to bring the issue back under control. The next step will be to implement file group backups, but we are running 2005, thus no compression and backup storage requirements will shoot through the roof (12TB database).
Long story short, I'm trying to slow the backup throughput down to preserve server performance during backup windows. We use a 3rd party backup tool, SQLSafe for compression functionality.
Regards
Pingback from What are the largest SQL projects in the world? « SqlSchool.gr
Pcpretorius, as you mention that you are using SQLSAFE for backup and compression, but in SQL server 2008 compression ratio is approx same as any other third party tools provide, yet it consume approx +10 to 4% of CPU.
Can you provide the Size of Database and compression ratio with SQL SAFE.
as i have tested it it was same and i feel better with 2008 (now i m not using SQLSAFE)
but in SQL SREVER 2005 third party is better.
Pingback from Entendendo e Melhorando a performance de seus backups | Vladimir M. B. Magalh??es – SQL Server DBA
Good Day. Thank you for an excellent White Paper. I do however have a question .We are running the Postilion Office 4.2 application on aN SQL Server 2005 64 bit platform . We are backing the database of 700 gig up to a single file onto a shared drive.I checked the same types of shared lockes are used during both type of Backup strategies and no I/O errors are registerred in the Log of SQL Server . When we run the normal single file backups the application does not freeze, but when we run the backup to multiple files, the operators claim the application freezes. Has anyone come across similar incidents ?