SQL Server: Basics of Database Snapshot November 26th, 2012

Vinod Kumar

In our day-today life we have needs for photocopies. Now if we take that concept and mimic the same inside SQL Server it is called as Snapshots. Inside SQL Server the concept of snapshots were there with replication for a while, but from SQL Server 2005 a new feature called as Database Snapshot were introduced.

The feature usage of Database Snapshots have been minimal in organizations, but there are some unique ways people use snapshots and a lot don’t know how this feature works too. This feature is different from SAN based snapshots and not to be confused. As we are in the topic, understanding that snapshots can be implemented as a  software solution or hardware vendor approach. In this blog post we will look at the feature within SQL Server called as database snapshots.

  1. Database snapshot is a process where DBAs can create a point-in-time read-only copy of any database. They can create multiple snapshots of the same database at different points in time.
  2. Space needed for each snapshot is typically much less because snapshot only stores pages that have changed.
  3. Database snapshots allows you to do the following:
    1. Turn a database mirror into a reporting server (you cannot read from a mirror but you can create a read only snapshot of the mirror and read from that)
    2. Generate reports without blocking or being blocked by production operations.
    3. Protect against administrative and user errors.
    4. This is yet another way to protect against database application upgrades and functioning like a backup before the process
  4. Each file in a snapshot is created as a sparse file (feature of NTFS). The database recovery models don’t have an impact on creating snapshots.
  5. Use the DMV sys.databases to check the source databases for snapshots.
  6. Write: Snapshot files contain only data that has changed from source, and for every file, SQL creates a bitmap that is kept in cache with a bit for each page of the file indicating whether the page has been copied to the snapshot. Every time a page in the source is updated, SQL Server checks the bitmap for the file to see if the page has already been copied, if not, copies it. This operation is called copy-on-write or COW process.
  7. Read: When a process reads from a snapshot, if first accesses the bitmap to see whether the page it wants is in the snapshot file or the source. When it reads from a snapshot database, no locks are taken regardless of what isolation level it’s in (true for reads from file and from source database).
  8. When SQL Server is shut down or the database is closed, the bitmaps are lost and need to re-created at startup. It checks whether each page of the database is in the sparse file as it is accessed and it records the information for future use using the memory maps.
  9. Allocations to the sparse files are made in units called regions, each region is 64KB. When a region is allocated, all the pages are zeroed except for the page that has changed leaving room for 7 more pages. A new region isn’t allocated till all 8 pages are used.
  10. The snapshot operated in pages as discussed above to fill up the regions.
  11. Keep monitoring your IO overheads that come because of COW for the snapshot databases.
  12. Also because of sparse file configuration, keep an eye on the drive space where the snapshots are present.
  13. If a snapshot exists for a source database, the source database cannot be dropped, detached or restored.
  14. You can replace a source database with one of the snapshots thereby reverting the source database to the state when the snapshot was taken using the RESTORE command.
  15. The restore cannot be performed for offline databases and corrupted databases.
  16. Snapshots can also be used to recover deleted tables from the source database.
  17. Snapshots cannot be created for the model, master, resourcedb or tempdb database
  18. Database snapshots have no relation to the concepts of snapshot backups, snapshot isolation of transactions or snapshot replication.
  19. We cannot backup, restore, attach or detach an snapshot database.
  20. FILESTREAM datatype is not supported by database snapshots.
  21. With the introduction of ALWAYSON technologies in SQL Server 2012, snapshots cannot be created on the databases part of availability groups.
  22. NOTE: Please DONOT use Database snapshots as a replacement for backups. The usual backup strategies must be done and in place.

Thanks for reading this far. Feel free to drop a comment to tell us if you have used the database snapshot feature in your production environments.

Tags: , , , , , , , ,

This entry was posted on Monday, November 26th, 2012 at 08:30 and is filed under Technology. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

Leave a Reply