Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Comparing SQL Server Native Backup Times to SnapManager Backups


By:   |   Read Comments   |   Related Tips: More > Backup

Attend these FREE MSSQLTips webcasts >> click to register


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:

looking at the differences in backup time and restores using smsql

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:

BACKUP DATABASE [BackupTest]
TO DISK = 'S:\BackupTest_FULL_201102221637.BAK'
WITH NAME = 'BackupTest(Daily Backup)' ,
DESCRIPTION = 'Normal BACKUP on Feb 22 2011 4:37PM.' ,
MEDIANAME = 'BackupTest_Dump20110222',
MEDIADESCRIPTION = 'Normal BACKUP on Feb 22 2011 4:37PM.' ,
STATS = 10

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.

using VSphere to monitor the disk while executing a native sql backup without compression

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:

SmsqlJobLauncher.exe new-backup -svr 'STL-PDBADEVD01' -d
'STL-PDBADEVD01', '1', 'BackupTest' -RetainBackups 5 -bksif
-RetainSnapofSnapInfoDays 2 -trlog -mgmt standard

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?

using snapmanager for a single database snapshot

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:

RESTORE DATABASE [BackupTest]
FROM DISK = N'S:\BackupTest_FULL_201102221637.BAK'
WITH FILE = 1, NOUNLOAD, STATS = 10

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.

Finally, let's try one more experiment. When there are multiple databases on a single volume a snapshot of both databases will exist even though you only backup one database. This is because SMSQL snaps at the volume level. Now what if we take that snapshot backup and use it to restore only one of the databases? In this scenario I restore only the BackupTest database and do not restore the smaller database. Guess what happened? The restore took over 20 minutes to complete. This is nearly four times longer than the native restore and many more times larger than the single snapshot restore.

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

Here is a run down of the times for each backup and restore experiment:

summary of each of the backup senarios using smsql

This wasn't the most scientific experiment, but it still should provide you a good sense of performance differences between several backup scenarios using SMSQL. The backups and restores are not "instantaneous" as the NetApp brochures might claim, but it is undeniable that they are fast compared with native backups and restores. They are fast as long as your database files are configured correctly. If you are aware of what shares the volume then you should be able to take advantage of the efficiencies snapshots have to offer.
Next Steps


Last Update:


signup button

next tip button



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

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Learn more about SQL Server tools