Skip to main content

Complete Guide on Database Snapshot (SQL 2005/2008)


Most of us heard about this new cool feature (database snapshot) from SQL 2005 Enterprise Edition onwards and some of us also use it also in daily life. Here I am going to discuss about some important dimensions of this technology.


Snapshot is actually a read-only static copy of a source database as it existed at snapshot creation, minus any uncommitted transactions. You may take multiple snapshot of a database and provide them to your customers for their easy access. Creating a snapshot is extremely fast and handy but it will take almost the same amount of space taken by data file of the source database. Couple of points you should be aware of (and possibly let your customers know) about using this technology are as follows:

1. Snapshot is a read only copy of the source database. So no one (even a DBA) can not do any changes on a database snapshot. Also snapshot must reside on the same server where source database resides.
2. Snapshot itself can not be backed up or restored. So if you delete a snapshot, it is a permanent deletion.
3. Snapshot can be used to restore the source database to the point when snapshot was taken. However there are limitations using this technology. (For example, you have to delete all other snapshot/s of the database before restoring). Also restoring from a snapshot breaks the backup sequence/replication etc of source database.
4. If the source database becomes unavailable for any reason then the snapshots will also become unavailable. So never consider a snapshot as a “Backup” of source database.
5. Though initial disc taken by a database snapshot is very low (128KB by default), it can eventually grow up to the size of source database. So it is best practice to refresh database snapshot.

From SQL Server Architecture point of view snapshot technology actually uses the concept of one or more sparse file. When you take a database snapshot, under the hood SQL Server performs below steps:

1. SQL Server creates sparse files for the snapshot and allocates minimum space for them. However SQL Server notes the actual size of corresponding data file of source and notes it in the file so that provision of growth is maintained.
2. SQL Server rollbacks any uncommitted transaction in the newly created snapshot and then brings snapshot online.

At this point customers start using snapshot. When ever they run a query on the snapshot, SQL Server actually refers to the original source database’s data page only if there is no change on the source database’s page. If there is a change in the data page of the source databases after the snapshot creation time, SQL Server copies the original data file to corresponding sparse file during the data modification time. So if a customer runs a query on the snapshot where they refer to a data page which is changed at source after the snapshot is created, they actually read the data from sparse file. If you want to know about this internal architecture in more details, please refer to http://msdn.microsoft.com/en-us/library/ms187054.aspx

However this architecture concludes the need to refresh database snapshot periodically for a database which changes it’s data often. Otherwise the snapshot file’s size (sparse file) can grow up to the size of source database’s data file size (as it was while taking the snapshot).

Snapshot technology is recommended to be considered to be used in following scenarios:

1. Snapshot can be used for reporting purpose. We can create snapshot of a database at a particular time and then reports can be generated from that read-only snapshot with out directly accessing the source database. Also snapshot may be considered as a mirror of the source when a static data up to a certain point of time is acceptable for use. In this way, customers who does not need to access live data can refer to snapshot and offload the overhead on source.
2. Snapshot can be used as a “Point of recovery” before doing any critical operations on data/definition on the database. For example, before a bulk insert of data, you may consider to take a snapshot of the database. If anything goes wrong during the bulk insert and you need to revert back the database to the point just before the bulk insert started, you can easily perform this operation by running a restore from snapshot. Best of all, restoring database from a snapshot will not be only easy but also considerably fast compared to conventional resotre from backups.

I strongly recommend using snapshot before doing anything critical on a database where you suspect the database may get corrupted and you may end up undoing the change by rolling back/reverting the database. So in other words, snapshot can be considered a very powerful new generation “Rollback Procedure”
However, you need to know below facts about reverting a database from its snapshot:

1. Reverting from the snapshot will not work under following condition:
a. If source database contains any read-only or compressed file group
b. If any file of the source is offline which was online while the snapshot was created
c. More than one snapshot of the source exists. (In this case you have to delete all other snapshots before starting the restore)
2. Both source and snapshot will be inaccessible during the restore operation. They will be in recovery mode.
3. A successful restore will rebuild the log of source. So consider taking a final log backup of source before starting the restore from snapshot.
4. After the restore, backup chain will be broken for the source database. So take a full backup to ensure that you have good backup of source after the reverting.
Lastly please be aware that snapshot technology is no way meant to replace traditional backup/restore and must not be considered as a disaster recovery solution. In case of a failure which makes the source database unavailable or not recoverable, all the snapshots of that database will also be unusable.

Now couple of direct answers to implement this technology:

How to create a database snapshot?
How to restore a database from a snapshot?
How to drop a database snapshot?
How to create a script to create database snapshot automatically using TSQL?


Labels:

Comments

Post a Comment

Popular posts from this blog

How to kill a negative SPID (like SPID -2) in SQL Server?

Rarely this scenario will arise when most likely you see this negative SPID (most likely SPID -2) is blocking other transaction causing issues. If you try to kill it using normal KILL command, it will fail reporting below error: Msg 6101, Level 16, State 1, Line 1 Process ID <SPID Number> is not a valid process ID. Choose a number between 1 and 2048 This is because of an orphaned distributed transaction ID.  Please follow below steps to kill it: Step 1: -- Find the UOW Number select req_transactionUOW from master..syslockinfo where req_spid = <SPID Number> --  <SPID Number>  is -2 most likely. Step 2: -- Copy the UOW number from Step one KILL ‘<UOW Number>’ This will kill the negative SPID resolving the issue.  However please note following points: 1. For SPID -2, you may find multiple UOW numbers. Please start killing them one by one. Typically killing first UOW will resolve the issues. (ie. will kill all UOW and release
Post a Comment
Read more

DMV/TSQL to find out basic hardware information of the SQL Server including when SQL Server started.

Please use below code: However, please be advised that it can not tell correct information around virtualization.  For example, it will show Hypervisor even if SQL runs on a physical OS where Hyper-V is on. So use this query only when you do not have sufficient access on underlying Windows Operating system to get these information directly. -- Basic hardware information for SQL Server (sys.dm_os_sys_info) /* This query is courtesy of https://sqlserverperformance.wordpress.com/. All credits goes to original author. */ SELECT cpu_count AS [Logical CPU Count] , scheduler_count , hyperthread_ratio AS [Hyperthread Ratio] , cpu_count / hyperthread_ratio AS [Physical CPU Count] , physical_memory_kb / 1024 AS [Physical Memory (MB)] , committed_kb / 1024 AS [Committed Memory (MB)] , committed_target_kb / 1024 AS [Committed Target Memory (MB)] , max_workers_count AS [Max Workers Count] , affinity_type_desc AS [Affinity Type] , sqlserver_start_time AS [
Post a Comment
Read more

‘Trace Skipped Records’ – What is this and how to resolve it while using SQL Server Profiles?

In some very rare case, you may experience a very weired message in profiler’s output as ‘Trace Skipped Records’ while you trace something on SQL Server. Screenshot of similer situation is as below: This is not an error but it comes by design of SQL Server (I believe so). When you are using SQL profiler and return data is too big to fit in the GUI (for me, it is an enormous xml), SQL Server simply prints this message and proceed to next step. Mostlikely this is to save server’s memory and performance. Although not suggested and guranteed, you can try to run a server side trace and dump data in a file which should capture all the data. However, it is strongly not recommended to run a trace on your production server from server side. Microsoft will probally document this limitation in future. More details may be found at https://connect.microsoft.com/SQLServer/feedback/details/304225/msft-edw-profiler-displays-trace-skipped-records-for-large-530kb-batch 
Post a Comment
Read more