Systems Engineering and RDBMS

Archive for May 27th, 2008

Database Snapshots in SQL Server 2005

Posted by decipherinfosys on May 27, 2008

Database snapshots was introduced as a new feature in SQL Server 2005. It is available only in Enterprise Edition and is used for creating a read only copy of a database as of a certain point in time. There are a lot of benefits of having such a database snapshot scheme in place – two big benefits being reporting applications can use this snapshot instead of the regular OLTP/OLAP databases, and the other one being that you can use it for recovery purposes. There are other benefits as well that we will cover in this post. First, let’s see how we can go about creating these database snapshots.

First most important thing to understand is that database snapshots manage the data at the page level. SQL Server does not create another database file when we create a database snapshot, instead it creates what is called as a sparse file. We specify the name and location of the sparse file when we first create the snapshot
and this file initially has no data in it and is allocated minimal disk space. As and when a page is modified in the source database, the original version of the page file gets written to this sparse file. This process is also called as “copy-on-write”. Having this process in place allows us to create a record of the database as it existed when the sparse file was first created. Key thing to note is that the un-changed pages remain in the source database itself. Another thing to deduce from that point is that when/if we do a database recovery using the sparse file, the original source database needs to be present since the un-changed pages are not present in the sparse file.

So, the sequence of events is this:

1) When the snapshot is first created, the sparse file is empty.

2) When a change (update) happens in the source database, the original page is then written out to the sparse file.

So, any read operations that are done on the snapshot are:

a) Read from the source if the data has not changed since the snapshot was taken,

b) Read from the source as well as the sparse file (for the data that has been updated at the source). As mentioned above in #2, when the update happens at the source, the original data page gets copied over to the sparse file.

Another key thing to note is that since the database snapshots are NOT redundant storage, having snapshots does not protect us from any database corruption at the source database. Tomorrow, we will go through the commands to cover how to set up database snapshots in SQL Server 2005 as well as to go through a couple of different scenarios – locking, heavy IO operations and the advantages and limitations of this feature.

Posted in SQL Server | Leave a Comment »