Speed up SQL Server backup and re-storage

One of the most important tasks performed on each SQL Server is running backup and recovery. Backup copies your database, and when the problem occurs in your product database, the backup provides security by giving you a full copy of the recovery. In most cases, the recovery process is done in a product-critical manner, such as a clean development/test environment or a clean reporting environment. But in most key models, you need to restore these backup copies to fix the product environment.

The time is fundamental, based on the importance of creating backups, and the recovery of backups to correct critical requirements for product issues. Backups are online operations, but they do use system resources. However, recovery requires additional access to the database, so this is even a more critical task in the error state.

Given the time factor for accomplishing these tasks, there are a few things that need to be done on the backup and recovery side to improve the speed of these operations.

Hardware

Backup and recovery times are affected by your hardware and the configuration of these hardware. From a hardware standpoint, here are some things you need to consider to improve performance.

Scatter disk I/O. By using as many drivers as possible, you can ensure that disk I/O is not a bottleneck. Make sure you do not use the same hard drive for read-write operations at the same time.

Uses the latest hardware technology to use the fastest RAID configuration: RAID 0, RAID1, RAID10, and then RAID 5.

Use the fastest driver

Use the fastest controller and distribute disk activity to different controllers or different channels.

Use locally added disks and do not back up over the network.

Back up to disk, and then archive to tape.

Use SAN technology for snapshots and split-mirror backups.

If you need to back up another machine, use the fastest NIC and switch as much as possible. Also, if you can separate these network traffic from the normal network traffic, you can reduce the likelihood of network I/O bottlenecks.

Local backup

Another area that might affect the time it takes to complete a backup is when and how to run the backup.

Executed when the server is using less time

Do not run all your backups at the same time.

Do not run a batch program at the same time as a large backup.

Use the backup option to write to multiple files. This will scatter your I/O and increase the number of threads.

Use several backup technologies at the same time: complete, differentiated, and log.

Local recovery

From a recovery standpoint, most of the time mentioned above is used for recovery. Here are some additional tips:

Different phases are used in different areas, so that backups are partially restored without the need to restore all backups at the same time.

Use the recovery process, such as log Shipping, to achieve something similar to a previous point.

Use technologies other than backup and re-storage from data recovery, such as clustering, replication, CDP, and so on.

Third-party software

A key time-saving approach is to use the Backup compression tool to build backups specifically for SQL Server. There are some of these tools in the marketplace that can be used with minimal effort to get the maximum benefit.

Idera's Sqlsafe

Quest's SQL Litespeed

SQL Backup for Red-gate

Based on testing with vendors using Idera and a variety of hardware, Idera can achieve a backup rate of 4.5TB per hour and 2.3TB per hour by using the rate of storage in SQL safe. See the following links for additional information about setting up a new performance record. This is almost the limit for most SQL environments, whether from the cost of configuring hardware to the 4.5TB backup database per hour. But the reality is that by configuring a complete solution for both hardware and software at the same time, it is possible to achieve it.

Summary

As you can see, there are a few different things to do to improve the throughput of your backup and re-storage processes. Some of these are very simple fixes, while others need to configure your hardware, buy new hardware, or buy tools that can help speed up.

Based on the speed of 4.5TB per hour Idera test, using a Third-party backup compression tool appears to be the simplest and easiest way. I don't think there are a lot of databases going to this level of database, so based on testing, most of the full backups can be done in less than an hour. Here is a report on the largest database, and as you can see, there are not many databases up to the scale of TB. From the report's point of view, this number is about 3 times times more than 2 years, and I'm sure the order will reach 3 times times less than two years.

But with the combination of all these options, you'll get faster backup and re storage, but even all of the above methods will always have certain types of limitations.