Improve your performance for SQL Server backups

Writing directly to tape

A lot of third–party backup tools have agents that allow you to write directly to tape. This was great in the old days when disk space was expensive and limited. But this should not be the case anymore.

You can write to disk first and then archive to tape as disk backups will be faster.

Not maximizing disk input/output

Another common problem that occurs is not maximizing disk input/output. Backups and restores are very input/output intensive since they have to read the entire database and then write the entire database.

By creating your backups on different physical disks, you can reap the benefit of reading from one set of disks and writing to the other set of disks.

To further reduce disk input/output, you can compress your backups, so you are writing out much less data. You can also write out your backups to multiple files over multiple disks to further increase input/output throughput.

Backing up junk databases

Alright, you have enough to do already, so why do more? Do you have a bunch of extra non-production databases on the server that you are backing up every night?

If they are test or temporary databases, you should not waste cycles backing them up. If you have a process that backs up every database, you could waste time by backing up these databases you do not need. You should look at your backup routines and not bother backing up databases you do not need.

Backing up read-only databases

Along the same lines is backing up read–only databases repeatedly. If these databases do not change every day, you should not bother backing them up every day.

Not creating compressed backups

When a SQL Server backup is created, it will by default write out a page for every page that is used in the database. There is a lot of wasted space when a backup is created, since some of these pages may have little data.

There are several tools that allow you to create compressed backups. At the high end, you can compress your backups by up to 95%, allowing you to run your backups much faster and save a lot of disk space. This also helps if you need to move the backups to other servers since the file is so much smaller.

Backing up across the network

You can create a backup anywhere on your network using universal naming convention paths. But the downside is that you cannot control the network capacity. Therefore, backup times can vary from day to day.

It is a better idea to back up to local drives and then copy the files. This is another reason backup compression is so useful. Sometimes, you may have a separate virtual local area network for your backup traffic where you can control the network capacity and lessen the impact.

Running all backups at the same time

It is easy to set up backup schedules using SQL Agent. But you should try to do all backups at once. In most cases, on one SQL Server instance, you are doing these in series. But it is easy to use the same scheduled time if you are writing to a centralized backup location from multiple servers. You should know this and adjust your schedule.

So it is helpful to monitor your backups to see if they take any longer from day to day and then adjust your schedule as needed.

Read the whitepaper “Top SQL Server backup mistakes (and how to avoid them) by Greg Robidoux from MSSQLTips to learn more about improving SQL Server backups..

[Download PDF]

Anonymous