Two Backup Windows

Closing backup windows is one of the most difficult and overlooked challenges you face as a DBA. On one hand, the window of time you have to perform a backup is shrinking, closing in on you. Databases are growing larger even as availability demands increase, leaving you with precious few minutes to back up your critical data. On the other hand, you need to make sure that your backup is secure, closing all inappropriate access paths. So, you're dealing with a time window that's closing—whether you want it to or not—and a security window that you need to keep closed at all times.

When Web applications and other mission-critical applications need almost 24-hour availability, properly backing up those applications—and the data they use—becomes increasingly complex. Often, the tables those applications access must remain open, making it nearly impossible to find time to perform a good backup. If you can afford a new Storage Area Network (SAN) device, you can perform split-mirror—style backup and keep your applications available. In this scenario, the SAN provides mirrored storage for the application to ensure high availability, while also providing significant backup benefit. For example, you can temporarily break up (or split) the mirroring, and the data on the disconnected mirrored drives provides a point-in-time image that you can dump to tape or other backup media without disrupting ongoing operations. Then, you can reattach the mirrored drives, and the SAN resynchronizes the data. If you can't afford a high-end SAN solution, the next best strategy is to reduce the size of your backup data so that you can back it up more quickly. You can perform differential backups, or you might split your database into different filegroups, which lets you back up just part of the database.

How you go about closing the backup security window depends on the type of backup you're performing. If you're doing a simple backup to tape, you need to have physical security measures in place to protect the tape media. Only authorized personnel should be allowed to handle the tapes, which need to be stored in a location that's off-limits to the public. If you're backing up to disk, you need to secure the backup locations by using NTFS permissions. You might think your backup file is in a binary format that's difficult to decipher, but a native SQL Server backup file is an ASCII file whose contents can be viewed with any text editor, including Notepad. Although SQL Server lets you password-protect your backups, it doesn't provide a means of encrypting backup data. But at last spring's SQL Server Magazine LIVE! Conference, I looked at an interesting product called SQLLiteSpeed from DBAssociates that can zip backup files and encrypt them by using a 128-bit key (for information about this product, see http://www.sqllitespeed.com).

Effectively managing these backup windows is critical to having an efficient, secure database environment. Finding time to back up is paramount because a good backup is essential for database recoverability. And securing the backup data itself should be a key part of any overall security plan because anyone with access to your database backup can potentially have access to all of your company's sensitive data.