Leveraging SQL Database Snapshots

I've known about SQL Database Snapshots for a while, but never really took the time to use the feature.  Lately, I've gotten into mirroring again and decided to play around with Database Snapshots.  I should clarify at this point that I'm speaking about the native SQL snapshot capabilities, and not a filer technology (such as NetApp Snaps).

There are probably many good reasons to use a snapshot, but I'm going to cover three scenarios.

Application Upgrades

Often times prior to an application upgrade I am asked to run a full backup for the database.  My typical technique for large databases is to schedule a full backup a bit earlier than the maintenance period and then run a differential backup once the application services have been shut down.  This will dramatically speed up the time it takes to back up the database, while still providing the ability to fail back if the upgrade goes awry.  This has been successful for me in the past, but I decided maybe I should try to take a database snapshot instead and if a fail back is needed, I can restore the database from the snapshot.

I am a little apprehensive to use this method, simply because I'm not comfortable with it.  And since data is so precious (and I'd get fired for losing it), I typically have continued to employ the full/differential backup method.  I will, however, consider in a test environment testing this methodology on databases of various sizes to increase my comfort level.

Assuming you are comfortable with giving it a shot, here's what you would do... Prior to your application upgrade, once all app services have been shut down and the database is no longer experiencing changes by users, create the database snapshot. In this example, taken from MSDN, a snapshot is taken of the AdventureWorks database.  The value shown for NAME = <logical_data_file>, is the actual logical data file name of the database you want to make the snapshot from.  This is not a new name for your snapshot.

1CREATE DATABASE AdventureWorks_dbss1800 ON ( NAME = AdventureWorks_Data, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Data\AdventureWorks_data_1800.ss' ) AS SNAPSHOT OF AdventureWorks
2
3GO

Once your snapshot is successful, you can access it from the "Database Snapshots" folder in Management Studio.  It's a point-in-time copy of your database and is created instantly.  Then, once your application upgrade is complete, you can delete it, or restore back to it if necessary.  I have not recovered a large database from a snapshot, but I believe it would be extremely fast as well.  From what I know of snapshots, it maintains pointers to the original data, and when new data blocks are modified, they are written to new blocks on the storage system.  So I assume that restoring from a snapshot will revert the pointers back to that state, allowing for an instant restore.

1USE master; -- Reverting AdventureWorks to AdventureWorks_dbss1800
2RESTORE DATABASE AdventureWorks from DATABASE_SNAPSHOT = 'AdventureWorks_dbss1800'
3GO

Mirroring

Another handy way to leverage snapshots is when mirroring.  As you probably know, when you are mirroring a database to another server, the target database on the mirror server cannot be read.  If you need to verify that something made it over, or whatever the circumstance may be, you can create a snapshot of that mirrored database and then query it as a normal database.  Depending on performance impact, you can most likely use this as a reporting server as well if your data does not have to be real-time.

 Nightly Refreshes in a Training Environment

Many training environments have the desire to revert to a clean state prior to each training session.  The main way to revert may be to set up a SQL Agent job to restore from a flat file, but this is another scenario that I want to restore from a snapshot.  When my training environment is in it's "gold copy" state, I took a flat file backup just to cover my bases.  Then a snapshot.  Instead of setting up a job to overwrite the database from the flat file, I just execute my restore from snapshot command, and the database reverts back.  It's easier and there are less moving parts.

Things to Consider

As I stated earlier, from my knowledge of snapshots, a snapshot is just pointers to blocks on a file system.  That's why the snapshot is instant.  The downside to this, however, is that when you query your snapshot, you are hitting the same blocks on the disk as the live database.  If you create too much I/O load against your snapshot, you will also be affecting the live database and can impact performance.

When the live database modifies data, it will write new blocks to the file system.  If your system has a high rate of change and snapshots exist, you will have to account for extra storage.  As new blocks are written, storage usage will increase, even if the database is not actually growing in size.