Among the features Microsoft added to SQL Server 2005 were new ways to handle backup and restore operations. Mirrored backup -- only available in the Enterprise Edition -- allows for backups with an additional layer of reliability. Before the mirrored backups came on the scene, if you only had one backup of a given thing, and you lost both the backup and the original, there was no recourse. You had to either suffer the consequences or dig up an earlier backup (if you had one!) and live with it.
Mirrored backups are made to an existing media set, or media "family," that has already been mirrored. Mirrored media must also be consistent. If you have a tape backup device, for instance, you can only mirror it to another instance of the exact same make and model of that tape backup device. This insures consistency between the mirrors right down to the sequence of bytes written to the tape; and each mirror in the backup is interchangeable with every other mirror. Up to four mirrored backup sets can be created for any one backup.
When you run the BACKUP DATABASE command, the mirrors are specified using the TO and MIRROR TO clauses. The TO clause contains the first mirror, and the MIRROR TO clauses contain each subsequent mirror. The backup operation must be able to find and write to all of these media sets and families successfully, or it will return an error, since the mirror will be essentially incomplete.
The restore operation is much more forgiving, however, and this is the reason why backup mirroring is useful. When you want to restore from a mirrored media set, you only need a single instance of a given media set to restore everything. That single instance can be culled from multiple media families if needed.
For example, let's say you have two media families, A and B, and each has two tape drives. You've created a mirrored backup across all four tape drives, and now you want to restore them. Unfortunately, you've discovered that one of the tapes has gone missing -- the first tape in family A was left in a briefcase on a train somewhere. Thank goodness for SQL Server encryption! Since this is a mirrored backup set, you can use the tapes from family B to restore everything. You can even mix and match them: If you lost one tape from each family, you could reassemble a composite backup and restore from that as long as you're not missing all of the tapes from any given family.
Don't forget the backup hardware. For the devices you're backing up to support firmware upgrades, always make sure they are all running the same level or revision of firmware. Inconsistencies between firmware revisions could create problems that might not be immediately obvious, but could surface later during a backup or recovery operation. This is generally more important for tape than disk. If SQL Server detects a mismatch between the devices you're using to perform the mirroring, it'll warn you, so you can always try staging a test backup on the hardware you have in question and see what happens. (Also be sure to stage a restore operation or at least run RESTORE VERIFYONLY to determine if the backup did indeed work when you first set this whole thing up.)
Mirrored tape and disk backups have many of the same limitations and behaviors as their non-mirrored counterparts. For one, you can't use continuation media on disks, only tapes. This means, if you fill a given disk with a backup, then you can't point to another disk during the backup operation and tell SQL Server to continue the backup there. Obviously, your choice of tape or disk when performing mirrored backups is going to depend on what sort of backup infrastructure you have. But, if you want to start using mirroring as part of your backup strategy, you'll have the excuse you need to reassess what you have and how it's being used.
| ABOUT THE AUTHOR: |
|
Serdar Yegulalp has been writing about Windows and related technologies for more than 10 years and is a frequent contributor to various sections of TechTarget as well as other publications. He hosts the Web site Windows Insight, where he posts regularly about Windows and has an ongoing feature guide to Windows Vista for emigrants from Windows XP.
Copyright 2007 TechTarget
|
