SQL Server Backup Bandwidth

Bandwidth is an important consideration when backing up huge databases and extensive filegroups. For starters, forget about doing any significant backup over a WAN or Internet connection unless you have upward of a 1.5-megabit pipe to the source; but this will still not work for critical high-end OLTP systems. Anything less, unless it is a very small filegroup, will not provide a suitable facility for backing up a SQL Server database.

I routinely back up over a 10Mbit Ethernet network. Most data centers have a 100Mbit backbone, and putting your servers directly on that is key. But even a 100Mbit backbone is only as valuable as the speed of the server bus, network links, and hard disk I/O, along with the capabilities of the backup devices and so on.

The minimum rate of backup you could expect over a 10Mbit network is between 15MB and 45MB per minute, depending on the backup device. Local tape drives on fast computers using SCSI technology and high-end hardware can even achieve levels of around 200MB per minute, and even higher rates on RAID systems and extremely high-speed disk arrays.

What you then need to do is work out how large a database file or transaction log you are going to back up and then figure out how long it is going to take you to back up the data. If the data is mission-critical, you may want to back it up more often. Remember, data changes every minute of the day Database applications can see as much as 20 percent of the data changing on the low end and as much as 80 percent changing on the high end.

I have provided a very simple formula for you to work out how long it will take to back up your databases and log files. Let’s say we want to back up X amount to a certain device in Y time. Starting with the desired unknown Y, you would want to first figure out how much data you are going to try to back up on the local machine or over the network device. After you have calculated this, your equation will resemble:

where Y=time, S=amount of data in megabytes, and T=transfer time of hardware (locally or over the network). The data transfer or backup rate of the LTO 3 is 576GB/hr, so multiply by 1,000 to get the rate per GB. The older DLT technology transfers at upward of 300MB per minute (hauling data off the local hard drives). Thus, your equation would be

and two gigs would thus take just over six minutes to back up. Factor in another minute or more per 100MB for latency, cataloging, files in use, database updating, and so forth, and it would be safe to say that two gigs of data get backed up in less than ten minutes. Over the local area network, divide the transfer rate by a factor of ten, and the same two gigs takes over an hour to back up. Using LTO on the local network, you get transfer rate improvement by an order of magnitude.