Comparing SQL Server Native Backup Times to SnapManager Backups

By: Scott Shaw   |   Updated: 2011-03-25   |   Comments   |   Related: > Backup

Problem

What are the backup and restore time differences between different implementations of SnapManager (snapshots vs. streaming) and native SQL Backups?

Solution

You have many options when deciding on how to backup your databases. You might have a couple of third party tools and also tools provided to you by your backup and recovery team. You also always have the option of native backups. Throw in the fact that you might be backing up directly to tape, backing up to local disk, or backing up remotely and suddenly you are confronted with a wealth of different options.

This tip looks at a few scenarios. First we will look at the difference in backup times and restores using SMSQL. I'll look at both streaming backups and snapshots. I'll also take native backups for baseline purposes.

Setup

I'll first start off with our setup. The system runs Windows Server 2003 Enterprise SP2. The server is a VM running a single dual core 2.93 GHz Intel Xeon processor. The server hosts SQL Server 2008 R2 Standard Edition. I could have tested with Windows 2008 R2 and this might have given us better performance, but I think this setup is more common, especially when SQL has been upgraded but not the OS.

Here is a quick look at the server specifications in vSphere:

The server is configured with 3 LUNS on a single volume. The M: drive has the data files, the L: drive has the log files, and the S: drive holds the snapshots. All our backups will be taken to the S: drive. The database we are backing up is approximately 32 GB. The connection between the host and the storage is a 10 GB Ethernet.

Native Backup

I first executed a native SQL backup without compression from the query window. I used the following command:

The native backup completed in 34 minutes and 39 seconds. I used VSphere to monitor the Disk (KBps). The chart clearly shows high activity during the backup period. When measuring DiskTime on the server through Perfmon, the monitoring was pegged at 100% throughout the backup process.

Single Database Snapshot

Now let us try running a snapshot backup using SnapManager. We will first run a snapshot against a single database on a single volume. I do not take a log backup, run dbcc checkdb, or run a verification after the backup. Here is the command:

The snapshot backup for the same 38 GB database took 1 minute and 37 seconds! When we look at the same disk rate graph in vSphere there is little to no disk activity except for quick spikes in reads. Why?

The reason is because snapshots are completed at the array level. SMSQL calls the array to ask for a snapshot. Everything is then placed in readonly mode (quiesced) and all new data is written to other free areas on the file system. In fact, when you take native backups (non-snapshot backups) you are actually creating two copies of the data: one copy on the array and one copy on the host. So, you gain performance by eliminating high disk I/O but you run the risk of application sessions failing due to quiesing the volume.

Finally let's add a small database to the server and place the files on the same volume as the previous database. The backup now takes 2 minutes and 37 seconds. The difference is reasonable and expected considering the size of the new database (about 1.5 GB).

Restores

How about restores? Let us first start with the SQL native restore. I used the same backup taken in the native SQL backup test. I ran the restore from a query window:

The restore took 8 minutes and 40 seconds which is not too bad.

Now let us try a restore from the snapshot. With the test database files being the only files on the volume the snapshot restore took 1 minute and 39 seconds. Now that is a quick restore for a fairly large database.

The moral of this story is if reducing restore times is important to you and\or you have a large database then you will want to make absolutely sure that the database files do not share a volume with other files.

Summary

Next Steps

• It's not enough to just have its own LUN. Always configure a large database on its own volume.
• Here is the MSDN link talking about how to get the best performance from your SQL Server backups Optimizing Backup and Restore Performance in SQL Server
• This link is to the official NetApp marketing page for SMSQL SnapManager for Microsoft SQL Server
• Here is a link to most of the NetApp\SQL documentation. Unfortunately is focused primary on SQL 2005. SQL Server Resource Guide
• Here is a list of SQL 2005 Backup Product Options. These options still apply to SQL 2008. SQL Server 2005 Backup Product Options

About the author

Scott Shaw is a Lead SQL Server DBA with extensive experience running SQL Server in a virtualized environment.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips