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.

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.

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.

Brandon has worked in IT for nearly 20 years, and currently serves as a SQL Server DBA for a healthcare company in California. In his spare time, he runs for miles and helps maintain
RealCajunRecipes.com. Brandon is a certified SQL Server administrator.

Once the snapshot is deleted, there should not be anything holding onto the original disk blocks. This should free them up for reuse. The extent to how much extra disk space is required, and impact on performance and fragmentation really depends on your rate of change for the database in question. As I said in the original article, the I/O is still going against the same file system, so if you generate heavy I/O against the snap, it can affect your live database.

I can't clearly speak to the question of fragmentation when using snapshots without researching it online. Filers already keep data spread out across multiple disks, and depending on the school of thought, some may say that you may never get your data to truly line up as you expect when using so many physical disks.