Split-Mirror Backup and Restore

Split-mirror backup technology can help you achieve higher database availability by letting you back up the database very quickly, typically in a matter of seconds. This speed lets you make database backups more frequently. Also, you can restore a database from a split-mirror backup quickly, dramatically reducing the time a database is unavailable during a restore operation. Split-mirror backup can nearly eliminate the use of the database server's resources to perform the backup. Plus, you can initialize a secondary server much more quickly than with the standard SQL Server backup-and-restore technology.

Keep in mind that split-mirror backup requires specialized hardware and software. It requires that database data be stored on a disk subsystem with mirroring—typically RAID 10 on a SAN. In addition, it requires a specialized volume-management software utility to communicate with SQL Server 2000. You must balance the benefits of split-mirror technology against the cost of the required disk system hardware and software. For more information about required hardware and software, see the sidebar "Split Mirror, SAN, and NAS."

How Split-Mirror Backup Works

To understand how split-mirror backup works, start by thinking of three distinct mirrored volume sets, as Figure 1 shows. Assume you've configured them as RAID 10 in a SAN. The original database was created on two mirrored volume sets for fault tolerance. Let's say that since that time, you've added a third mirrored set of drives, which you've synchronized with the first two mirrors, and you're keeping a full copy of all the data current on the third set as well. All the mirrored sets are in the same disk subsystem, so SQL Server's disk writes go to all three drive sets simultaneously. With all this in place, you can make a split-mirror backup.

When you make a split-mirror backup, you use the SAN vendor's software and hardware to separate or "split off" the third mirrored drive set from the database's two primary mirrored drive sets. This nearly instantaneous split creates a backup of the database on the split drive set at a given point in time. (Note that the data copying occurs before the split, during the initial synchronization process.) The drive set that's split off is called a Business Continuance Volume (BCV) or a clone. The mirrored sets that remain behind ensure disk-system fault tolerance for the database data.

You accomplish the split-mirror backup by using a software utility supplied by the storage vendor or third-party backup-software supplier. When you make the backup, the split-mirror utility software issues a backup command to SQL Server's backup Virtual Device Interface (VDI) API. The backup utility software receives the backup set metadata from SQL Server and stores it on the cloned drive set.

During the splitting process, a checkpoint occurs, and although SQL Server might still accept writes to the database, depending on the vendor's implementation of split-mirror technology, writes to the physical disk volumes are suspended. If disk writes were allowed during the split, one possible result is that only a portion of the disk blocks belonging to a SQL Server data page would be written to the third drive set. This result is called a torn page. The suspension of writes to disk prevents the clone from potentially capturing a torn data page during the splitting process. During the splitting process, disk reads are unaffected, so SQL Server can still read data from disk. The splitting process generally takes a few seconds or less—the exact amount of time depends on the storage vendor and your storage-subsystem hardware configuration.

After the splitting process, database write activity to the storage subsystem resumes, but only to the original two mirrored drive sets, as Figure 2 shows. You can use split-mirror technology to make a fast copy of a large database to another server. Using the resulting BCV as a backup set, you can restore it to another database server on the SAN, or you can archive the BCV to tape.

Remirroring the Original Database

Like all backups, the BCV becomes out of date as soon as the original database data changes. So, depending on your disk resources, you might want to reuse the BCV volume set for making another split-mirror backup later. Figure 3 shows how you can resynchronize the BCV with the original database mirrored set. Of course, as soon as you let SQL Server write to the BCV drive set, you can no longer use that drive set as a backup image for restoring. If you have only one BCV, remirroring can leave you vulnerable. To remedy this, make split-mirror backups to two BCV sets in rotation, and remirror only the older one. That way, if the remirroring fails, the newer BCV is available as a backup.

Synchronization takes much longer than splitting. The time required for synchronization depends on the storage vendor's technology and the amount of data involved, but it could take several hours. When the synchronization is complete, your server's array returns to the state that Figure 1 shows: You have three complete mirrored sets, and you're ready to make another backup.

Because these backups occur quickly and don't affect the database server's resources, you could make full database or database file backups several times a day, if necessary. The only limiting factor is the time that synchronization requires.

Split-Mirror Restore

When you restore from a split-mirror backup, you reestablish the BCV as a third mirrored set for the database, and the disk subsystem synchronizes the other two drive sets so that they mirror the restored data. Because SQL Server can read the third mirrored set right away and write to all three sets, the database becomes available almost immediately. Depending on the vendor, the synchronization of the original mirrored sets might happen in the background.

When you restore a database from a BCV through split-mirror restore, the restore happens very quickly—potentially just as fast as the backup. To visualize how a split-mirror restore works, start with a BCV and the mirrored set of drives containing the database, as Figure 3 shows. You use the vendor's utility to initiate the restore process and supply the necessary backup metadata to SQL Server 2000. As with any database restore, the process performing the restore must be the only database user at the time. When the BCV becomes a part of the mirrored set, the restore is complete, and you can recover the database and make it available.

After the split-mirror restore, the BCV drive set becomes the reference set for the system, so you need to synchronize the original mirrored sets with the BCV. Transparent to SQL Server, disk reads come only from the BCV, while all disk writes go to all three mirrored sets simultaneously, as Figure 4 shows.

The restore synchronization process occurs in the background and takes considerably longer than the split-mirror restore—typically hours for large systems. If the database is heavily used, the synchronization process might cause some performance degradation because of the resulting increase in I/O activity.

After you restore a split-mirror backup of a SQL Server 2000 database, you can apply differential and transaction-log backups to the database, if you restored the split mirror without recovery. Because transaction-log restores usually go very quickly and the initial database restore takes only a few seconds, you can recover a VLDB with minimal downtime.

Note that during the synchronization after a restore, the database might lack disk-drive fault tolerance if the BCV volume is unmirrored. To determine whether the BCV can also be a mirrored set, you'll have to check with your vendor about how its product lets you create BCVs. When the synchronization process is complete, all three volume sets are mirrored and your disk array returns to the state that Figure 1 shows.

Vendor Support for SQL Server 2000 Split-Mirror Backup

Compaq, EMC, and HDS work with Microsoft to support split-mirror backup through their SAN products. Each of these vendors supplies SAN disk hardware and backup-utility software that enable split-mirror backups and restores.

Compaq offers the SANworks Enterprise Volume Manager (EVM) software for use with its StorageWorks storage systems. The EVM uses the SQLer command-line utility to interact with SQL Server's VDI for making split-mirror backups and restores.

EMC produces the TimeFinder volume-management software for use with its Symmetrix storage systems.

As of this writing, HDS is testing its SplitSecond software utility, which it will use in combination with its Freedom Storage SAN products to support SQL Server 2000 split-mirror backup.

Another Look

SQL Server 2000's split-mirror technology gives you several advantages over the traditional database backup methods. It provides nearly instant database restores for VLDBs and nearly instant and very low impact backups of large databases. And with nearly instantaneous split-mirror restores of large amounts of data, you can keep your system online and available almost continuously.