Perhaps the most important tasks that occur on every SQL Server is running backups and restores. Backup copies of your database give you the security that if any issues surface with your production database you have a complete copy to fall back on. In most cases the restore process is done for non-production critical means such as refreshing development\test environments or refreshing a reporting environment. But in the most critical mode you are restoring these backup copies to replace or fix a production environment.
Based on the importance of creating backups and the critical need for restoring backups to correct a production problem, time is of the essence. Backups are an online operation but they do use system resources. Restores, however, require exclusive access to the database, so in a failure state this is an even more critical task.
In light of the time factor to complete these tasks, there are several things that can be done on both the backup side and the restore side to improve the speed of these operations.
Hardware
Backup and restore time is affected by your hardware as well as the configuration of that hardware. From a hardware perspective here are a few things you should consider to improve performance.
- Spread disk I/O. By using as many drives as possible you can make sure the disk I/O is not the bottleneck. Also make sure you are not using the same drives for both reading and writing.
- Employ the newest hardware technology.
- Use the fastest RAID configurations: RAID 0, RAID1, RAID10 and then RAID 5.
- Use the fastest drives.
- Use the fastest controllers, and separate disk activity onto different controllers or different channels.
- Use locally attached disks instead of backing up across the network.
- Backup to disk and then archive to tape.
- Use SAN technologies for snapshot and split mirror backups.
- If you do need to backup to other machines use the fastest network cards and switches possible. Also if you can segment this traffic from normal traffic, you'll reduce the network I/O bottleneck.
Native Backups
Another area affecting the time it takes to complete backups is when and how the backups are run.
- Execute during low server usage times.
- Don't run all of your backups at the same time.
- Don't run batch processing at the same time as large backups.
- Use backup options to write to multiple files. This will spread your I/O as well as increase the number of threads.
- Use a combination of backup techniques: full, differential and log.
Native Restores
From a restore perspective most of the items mentioned above apply to restores as well. Here are a few additional tips:
- Use a staging area so backups are partially restored, instead of having to restore all backups at the same time.
- Use a restore process such as Log Shipping which achieves something similar to the previous bullet point.
- Use other technologies besides backup and restore for data recovery such as clustering, replication, CDP, etc.
Third Party Software
One key time-saving function is using a backup compression tool built specifically for SQL Server. There are several on the market and these tools provide the biggest gains with the least amount of effort.
- Use compression software such as
- Idera's SQLsafe
- Quest's SQL LiteSpeed
- Red-Gate's SQL Backup
- Based on a test by Idera and various hardware vendors, Idera was able to achieve backup rates of 4.5 terabytes per hour and restore rates in excess of 2.3 terabytes per hour by using SQLsafe. Take a look at this link for additional info on setting new performance records. This is probably the extreme for most SQL environments, both from the cost to configure the hardware to the need to backup a 4.5 terabyte database. But the fact is that it is possible by configuring the total solution in both hardware and software.
Summary
As you can see there are several different things that can be done to increase the throughput of your backup and restore processing. Some of these items are pretty simple fixes while others require reconfiguring your hardware, purchasing new hardware or purchasing tools that can help increase the speed.
Based on the speed of the Idera tests to achieve 4.5 terabytes in an hour, using a third party backup compression tool seems like the simplest and easiest way to go. I don't think there are many databases that fall into this realm of database size, so based on the test most full backups could be completed in under an hour. Here is a report of the largest SQL Server databases, as you can see there are still not many databases that cross the terabyte threshold. From the report, the number almost tripled in two years and I am sure the number will triple again if not more in the next two years.
By using a combination of all these options you will be able to achieve faster backup and restore times, but with just about everything there will always be some kind of limitation.
More on SearchSQLServer.com
Expert webcast: SQL Server Backup and Restore Fundamentals
Guide: SQL Server Backup and Recovery Learning Guide
Tip: Online restore feature in SQL Server 2005
| ABOUT THE AUTHOR: |
|
Greg Robidoux is the president and founder of Edgewood Solutions LLC, a technology services company delivering professional services and product solutions for Microsoft SQL Server. He has authored numerous articles and has delivered presentations at regional SQL Server users' groups and national SQL Server events. Robidoux, who also serves as the SearchSQLServer.com Backup and Recovery expert, welcomes your questions.
Copyright 2006 TechTarget
|
|
