Tips and Tricks to Growing Your SQL Server Estate from SMB to Enterprise Scale
Thursday, June 28, 2018 - click here to learn more
What are the backup and restore time differences between different implementations of SnapManager (snapshots vs. streaming) and native SQL Backups?
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.
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.
I first executed a native SQL backup without compression from the query window. I used the following command:
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.
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:
'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?
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).
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:
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.
SummaryHere is a run down of the times for each backup and restore experiment:
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.
- 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
Last Update: 2011-03-25
About the author
View all my tips