How to optimize tape backup device performance in SQL Server

There are four variables that affect the performance of tape backup devices and allow SQL Server backup and restore performance operations to increase the linear proportions of the system by substantially adding more tape devices.

Software data block size

Number of tape devices sharing a small computer system interface (SCSI) bus

Tape Device Type

The software data block size is calculated by SQL Server for optimal performance and should not be changed.

Many high-speed tape drives run better if you have a dedicated SCSI bus for each tape drive that you use. Drives with a native transfer rate exceeding 50% of the SCSI bus speed must be on the dedicated SCSI bus.

For more information about settings that affect tape drive performance, refer to the tape drive vendor documentation.

Important never place a tape drive on the same SCSI bus as a disk or CD-ROM drive. The error handling operations for these devices are incompatible.

Optimizing Disk Backup Device performance

The original I/O speed of the Backup-to-disk device affects disk backup device performance and enables SQL Server backup and restore performance operations to increase linearly with the addition of multiple disk devices.

Careful consideration is required when using RAID (redundant array of independent disks) for a disk backup device. For example, RAID 5 has a low write performance, roughly the same speed as a single disk (since parity information must be maintained). Additionally, the original speed of appending data to a file is significantly slower than the original device write speed.

If the backup device is highly striped so that the maximum write speed of the backup device exceeds the speed at which the backup device appends the data to the file, it is more appropriate to place several logical backup devices on the same stripe set. In other words, you can improve backup performance by placing several backup media families on the same logical drive. However, an empirical approach is needed to determine whether this is a benefit or a loss for each environment. In general, it is a good idea to place each backup device on a separate disk device.

Typically, only a few disks on the SCSI bus can run at maximum speed, but the ultra-wide and Ultra-2 buses can handle more disks. However, it is likely that you will need to carefully configure your hardware for optimal performance.