SQL Server backup is an I/O-intensive operation.

SQL Server backup is an I/O-intensive operation. To put it simply, you need to first read and then write the content of the entire database. The following are several steps to improve I/O throughput:

Write to as many disks as possible

Use as many disk drives as possible to separate the load. It can be a raid array consisting of many disks or multiple raid arrays.

Read from as many disks as possible

If your database is distributed across multiple disks, you can reduce the I/O bottleneck.

Read and Write operations using separated Disks

Read and Write operations on different disk groups help increase I/O throughput.

RAID configuration

Use the fastest RAID configuration for read and write operations. For example, RAID 5 is slow because it requires additional write operations on the disk. RAID 0 is the fastest for write operations, but it does not provide redundancy. You can consider using RAID 1 + 0: 1 and 0 to represent the image type (1) and band (0). Therefore, you can have a dataset image, additionally, you can combine multiple images to obtain additional I/O throughput. Since the backup must first be read from the database and then written to the backup file, the advantage of the write operation is obvious on the disk where the backup file is stored.

Controller

Use different controllers and (or) channels to increase I/O throughput. In addition, you need to use the best controller you can afford. When purchasing a controller, you should check the number of ports, the maximum number of supported drives, buffer size, battery backup, and SCSI protocol support. Read and Write operations must be separated across multiple controllers or channels.

Write data locally

Write to a local disk instead of the storage connected to the network. If the data is written to a directly connected storage device, you can eliminate other factors that may slow the backup.

Write multiple devices

In addition to reading and writing multiple disks, you can use as many threads as possible to increase throughput. This can be done by taking advantage of the Enterprise Manager and selecting multiple output files, or by taking advantage of the T-SQL and containing multiple files in the BACKUP command. You can also further increase the throughput by writing different disk subsystems.

Compressed backup

Use a third-party tool to compress your backup output. In this way, you can reduce the total size of the backup file by 90% and cut the backup time by half. Because most of the data is text data in the database, it is highly compress.

Write backup to disk

Always write the backup to the disk, rather than directly writing it to other types of media. After writing it directly to the memory, disk backup is the next fastest choice.

Fast disk drive

Use the fastest disk drive you can get. The SCSI drive is still faster than the IDE and can reach up to 15 K rpm.