Professional Microsoft® SQL Server® 2008 Administration

18.7. Backup and Restore Performance

As stated previously, SQL Server 2008 supports 64 backup devices. It will use multiple backup devices in parallel to back up and restore for faster throughput. The backup devices should be on a different controller from the database for better throughput. For disk devices, consider the RAID level used for fault tolerance and performance. Work with the SAN or disk array vendor to get their recommendations for the solution. A combination of full, differential, and transaction-log backups will improve performance.

Some of the largest database systems may use SAN Snapshot Disk technology supported by many SAN vendors for faster backup and restore (see Figure 18-6). It is configured as a three-way mirror whereby the business continuity volume (BCV) is data synchronized and then split from the mirror set. The disadvantage of this solution is the cost of the SAN hardware and software to implement it. The advantages of this solution are as follows:

• Nearly instant and low-impact backups, as the disk split occurs instantaneously

• Nearly instant database restore. In a restore, the BCV can be reintroduced and attached to SQL Server for instant restore while it is data synchronizing the others in the mirror set.

• It keeps the system online and available almost continuously with minimal impact to SQL Server, as the data synchronization occurs on the SAN level.

• It enables you to mount the BCV disks on another SQL Server to perform conventional backups and consistency ...