Capture the reporting benefits of database snapshots in SQL Server 2005

by Tim Chapman | Jan 08, 2007 8:00:00 AM

Tags: Storage, Databases, Tim Chapman, Microsoft SQL Server 2005, benefit, Microsoft SQL Server, database, database snapshot, data file, source database, SQL Server Newsletter

Takeaway: Database snapshots allow you to create read-only databases for reporting and can also be useful in your data recovery efforts in the event of a disaster. Tim Chapman explores how database snapshots work and provides an example of a snapshot.

Database snapshots are yet another great new feature in SQL Server 2005. A database snapshot is a read-only copy of a database that reflects all database data up to the point in time for which the snapshot is taken.

These database snapshots are good for reporting purposes because no locks are taken for any queries run in the snapshot database or in the source database. Snapshots are also useful in a disaster because you can revert your existing database to an existing snapshot database or restore individual tables and data as necessary in the event of adverse data manipulation statements.

How do database snapshots work?

A database snapshot is created like a typical database with a CREATE DATABASE statement; there is the additional specification of a source database as a snapshot in the statement. When the snapshot is created, a sparse file is created. This file (which is used only on NTFS volumes) initially has no disk space allocated to it—even though if you view the size of the file in Windows Explorer, it will look like it is the same size as the original source database file. The size on disk for that file is close to zero.

The database snapshot initially reads the data files from the source database. As data changes in the source database, the database engine copies the original data pages from the source database to the snapshot database. This technique ensures that the snapshot database will only reflect the state of the data that existed at the time the snapshot was taken. When a SELECT statement is issued against a database snapshot, no locks are ever issued—regardless of whether the data page being read is located in the source database data file or the snapshot database data file. Because no locks are issued on the read-only database snapshot, snapshots are a great candidate for reporting solutions.

A snapshot example

Let's take a look at how database snapshots work in SQL Server 2005. To do this, I will first need a source database to make a snapshot from. The script below creates the source database:

USE master

GO

IF EXISTS(SELECT name from sysdatabases where [name] = 'SourceDatabase')
      DROP DATABASE SourceDatabase

GO

CREATE DATABASE SourceDatabase ON PRIMARY

( 
      NAME = SourceDatabase_Data,
      FILENAME = 'C:\SQLServer\SourceDatabase_Data.mdf'

) LOG ON

(
      NAME = SourceDatabase_Log,
      FILENAME = 'C:\SQLServer\SourceDatabase_Log.ldf'

)

GO

Notice the size of the Product field. I made it a CHAR(150) field to emphasize the growth of the data file, which will make it easier to explain how the snapshot works a bit later in my example.

Now that I have a source database, I can load some data to expand the size of the data file a bit. To do so, use the script in Listing A to create a SalesHistory table.

Once you run the following script, navigate to where you have placed the data files. In the example above, I placed the files in the C:\SQLServer folder. On my machine, the size of my data file when the database was initially created was 1,216 KB, and after the data load was 7,360 KB. Now that the source database has some data in it we can create a snapshot. Use the following script to create the database snapshot.

CREATE DATABASE SnapshotDatabase

ON

(
      NAME = 'SourceDatabase_Data',
      FILENAME = 'C:\SQLServer\SnapshotDatabase.mdf'

) AS SNAPSHOT OF SourceDatabase

The syntax for creating a snapshot is very similar to the syntax for creating a database. A couple of the main differences are: the AS SNAPSHOT OF SourceDatabase statement, which indicates which database in the server instance will be the source database for the snapshot; and the fact that there is no log file created for a database snapshot. Because no data manipulation transactions will be occurring on the read-only database, no log files are required.

This database snapshot script creates a sparse file named SnapshotDatabase.mdf (the extension of the datafile is not required to mdf). If you navigate to this file in Windows Explorer and look at its properties, you will see that the size of the file is the same as the source database file; however, the size on disk is close to zero. The database snapshot has no data of its own at this point.

You can run the same script I used above to insert 10,000 rows into the SalesHistory table again in the SourceDatabase database. At this point, the size of my SourceDatabase is 12,480 KB, while the size on disk of my SourceDatabase is now 448 KB. The data pages that have changed in the SourceDatabase have been copied to the SnapshotDatabase, which explains why its size on disk has increased.

Points to ponder

Database snapshots allow you to create read-only databases for reporting and, if necessary, you can revert your source database back to your database snapshot. Also, you can create as many database snapshots as you need for your reporting purposes.

It is important to remember that these database snapshots take up space on disk, and having too many can fill up your disk arrays quickly, especially in a production environment where data is updated frequently.

In addition, using database snapshots will degrade performance a bit because I/O is increased on the database due to the fact that the data pages are copied as the write operation is performed.

Despite these minor drawbacks, if you are able to come up with a good database snapshot creation plan for reporting, this new feature of SQL Server 2005 can be extremely useful.

Tim Chapman is a SQL Server database administrator who works for a bank in Louisville, KY, and has more than 7 years of IT experience. He is also Microsoft certified in SQL Server 2000 and SQL Server 2005. If you would like to contact Tim, please e-mail him at chapman.tim@gmail.com.

Print/View all Posts Comments on this article

Snapshot vs replicationiansavell@...  | 01/09/07

Very Very BadlyTony Hopkinson  | 01/09/07

Sorry but I'm not having thatTony Hopkinson  | 01/09/07

Definetly not true...chapman.tim@...  | 01/09/07

Well according to the guy who gave me myTony Hopkinson  | 01/09/07

No lockschapman.tim@...  | 01/09/07

Really how does it guarantee a transaction then ?Tony Hopkinson  | 01/09/07

The version storechapman.tim@...  | 01/09/07

I realised last nightTony Hopkinson  | 01/10/07

Maybe..chapman.tim@...  | 01/09/07

Snapshots are good.fmendez  | 01/09/07

Thankschapman.tim@...  | 01/09/07

Mirroringdavidsatz  | 03/27/08