Backup and Restore Performance

As we have said, SQL Server 2005 supports 64 backup devices. It will use multiple backup devices in parallel to back up and restore for faster throughput. The backup device 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. Moreover, use a combination of full, differential, and transaction-log backups to improve performance.

Additionally, 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 where 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:

• 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.

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

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

Figure 18-6