SQL Server: Always have a backup plan
Don’t confuse backup and restore with high availability. In the event of an outage, backup and restore will get back your data, but only after some downtime.
Saleem Hakani
In an ideal world, hard drives and other hardware would never fail. Software would never be defective, users wouldn’t make mistakes and hackers would never be successful.
However, we live in a less-than-perfect world. We need to plan for and prepare to handle adverse events. Performing and maintaining good and healthy backups is one of the top priorities for any administrator or engineer working with SQL Server.
Backup and restore is not HA
Here’s one good rule of thumb to keep in mind: Backup and restore is not a high availability (HA) feature. Restoring a database from backup is simply a repair feature, not an availability feature.
If you’re running a mission-critical system and your database requires HA, then look into various actual HA features available within SQL Server. HA does not equate to backup and restore.
If you’re running a large or mission-critical system, you need your database to be available continuously or for extended periods of time with minimal downtime for maintenance tasks. Therefore, the duration of database restore situations must be kept as short as possible.
Also, if you’re using exceptionally large databases, they’ll require longer periods of time to perform backup and restore. You should look into some of the new features SQL Server offers to increase backup and restore operational speed. This helps minimize the effect on users during both backup and restore operations.
Here’s a look at some other specific techniques.
Multiple backup devices: If you’re performing backups and restores on a large database, you should use multiple backup devices simultaneously. This configuration will help you write backups to all devices at the same time. Using multiple backup devices in SQL Server lets you write database backups to all devices in parallel.
One potential bottleneck in backup throughput is the backup device speed. Using multiple backup devices can increase throughput in proportion to the number of devices you’re using. Similarly, you can restore the backup from multiple devices in parallel.
Mirrored media set: If you use a mirrored media set, you can have a total of four mirrors per media set. With the mirrored media set, the backup operation writes to multiple groups of backup devices. Each group of backup devices makes up a single mirror in the mirrored media set. Each single mirror set must use the same quantity and type of physical backup devices, and all must have the same properties.
Snapshot backups: This is the fastest way to perform database backups. A snapshot backup is a specialized backup created almost instantaneously using a split-mirror solution obtained from an independent hardware and software vendor.
Snapshot backups minimize or eliminate the use of SQL Server resources to accomplish the backup. This is especially useful for moderate-to-large databases for which availability is crucial. You can sometimes perform snapshot backups and restores in seconds, with little or no effect on the server.
Low-priority backup compression: Backing up databases using the newly introduced backup compression feature could increase CPU usage. Any additional CPU capacity consumed by the compression process can adversely affect concurrent operations. Therefore, whenever possible, you should create a low-priority compressed backup whose CPU usage is limited by the resource governor to prevent any CPU contention.
Full, Differential and Log backups: If the database recovery model is set to Full, use a different combination of backups (such as Full, Differential and Log). This will help you minimize the number of backups you need to apply to bring the database to the point of failure.
File/file group backups: Use file/file group backups and T-log backups. These techniques let you back up or restore only those files that contain the relevant data. Because you’re not backing up or restoring the entire database, the operation is much faster.
Use a different disk for backups: Do not use the same physical disk that holds database files or Log files for backup purposes. Using the same physical disk not only affects the performance, but also may reduce the recoverability of the plan.
Remember to select a couple of techniques and tactics for backup and restore that work best within your configuration. It’s an essential aspect of any SQL Server operational strategy.
.jpg "Saleem Hakani")
Saleem Hakani is a principal architect at Microsoft with more than 18 years of experience. He has worked with SQL Server since 1992 and has worked on numerous large-scale Microsoft services as an engineer and architect over the last seven years, including Hotmail, Bing and MSN. Hakani leads the worldwide SQL Server Community for Microsoft employees, and is a technical presenter for various Microsoft events including TechReady, SQLFEST, SQL-SCHOOL and SQLPASS.