Database Snapshots and Mirroring - SQL Server 2008

When you have a mirrored system, you may want to use the mirror as the server that users query to generate reports on their data, for two reasons:

  • You do not want users querying your primary OLTP system, since it must handle the load for all your transactions.
  • You may want to get more usage out of your mirror, since you have already invested in the hardware for the mirror.

Before we dive into a discussion about creating snapshots on your mirrors, though, there are some things you need to think about. First and foremost is that allowing users to query the mirror using snapshots will be a performance hit on the mirror. This could, in turn, slow down your entire database mirroring solution. The second consideration is that when there is a failover to the mirror, you do not want your clients querying your new principal; rather, you want to failover your snapshots and your clients to the new mirror if it’s available.

How Database Snapshots Work

The easiest way to define database snapshots is to use the term copy-on-write. Snapshots use a copy-on-write technology, so that only changes to the database are written to the snapshot. Therefore, the entire database does not need to be copied when creating a snapshot; only the changes are copied. Now, if your entire database is changing over the lifetime of a snapshot, then your snapshot will become larger as the changes are applied.

Database snapshots are a read-only, point-in-time, static view of your database. Snapshots work at the data-page level. This means that when a data page changes, the original page is copied over to the snapshot. If the same data page is modified later, the snapshot does not change and is unaware of the new data page. To update the snapshot with the new data, you need to create a new snapshot.

When a snapshot is created, a sync log sequence number is created in the source database. SQL Server then runs recovery on the snapshot, since uncommitted transactions need to be rolled back on the snapshot. This does not affect the transactions on the source database. SQL Server creates an NTFS sparse file to hold the snapshot. NTFS sparse files work as you might guess: when they are initially created, sparse files are, in fact, sparse. They don’t contain any data, and they grow as data is added to the file. Allocations to the sparse file are in 64KB blocks. When a copy-on-write happens, all the pages are zeroed out except for the changed data page, if a new 64KB block is allocated. When other pages change, individual pages are copied over to the 64KB block.

When an application updates a value in the database, before the new value is written, the database makes a copy of that page to the snapshot in its sparse file. SQL Server maintains a bitmap for the related sparse file so that it can quickly decide where the I/O should go on a read operation, since the snapshot or the original database may contain the correct information. When a read operation comes in, if the snapshot already contains the correct data, SQL Server will go to the snapshot for the information. If the data has not changed in the original database and therefore hasn’t been copied to the snapshot, SQL Server will go to the original database to retrieve the information. No locks are taken on the original database when a read happens for a snapshot.

Database snapshots

Database snapshots

You may be wondering how database snapshots can work with database mirroring, since the database on the mirror is restoring. Through the magic of code, database snapshots get an exclusive latch on the mirror database, so that snapshots can read the data in a mirrored database.

Managing Snapshots Using T-SQL

You may want to perform some common operations with snapshots using T-SQL. These include creating and deleting snapshots, as well as reverting to a snapshot.

Creating a Database Snapshot

To create a database snapshot, use the CREATE DATABASE statement with the AS SNAPSHOT OF argument.

You must specify every database file of the source database. You can have multiple snapshots on your system, so that you have different point-in-time views of your databases. Note, though, that you will need to explicitly connect to the snapshot from your applications.

One best practice to follow is to use descriptive names for your snapshots so you know when they were created, just in case you have many snapshots on your system. The following example creates a snapshot of the Adventure Works database on a mirrored server:

CREATE DATABASE AdventureWorks_dbss031908 ON
( NAME=AdventureWorks_Data,
FILENAME= 'C:\Program Files\Microsoft SQL Server\
AS SNAPSHOT OF AdventureWorks;

Dropping a Database Snapshot

To drop a database snapshot, you just need to use the DROP DATABASE statement. All the sparse files associated with your snapshot will be deleted as well. The following code deletes the snapshot without affecting the source database just created:

DROP DATABASE AdventureWorks_dbss031908

Reverting to a Database Snapshot

We all make mistakes. Thankfully, database snapshots can help us recover from our mistakes. When you revert to a snapshot, the pages stored in the snapshot are rewritten back to the original database. The log is also over written and rebuilt in the process. Any updates to the database since the snapshot was taken, from both a data and metadata standpoint, are lost, so you must be completely sure that you want to revert to the snapshot.

A number of restrictions apply when reverting a source database to a snapshot:

  • You cannot revert if the source database has read-only or compressed filegroups.
  • You cannot have filegroups currently offline that were online when the snapshot was taken.
  • You cannot have multiple snapshots in existence when you revert. You must remove all other snapshots except the one snapshot to which you want to revert.
  • You cannot revert on the mirror. If you want to revert in a database mirroring scenario, you need to be taking snapshots on the principal.
  • You should back up your log before you revert to a snapshot. You cannot use this backup to roll forward changes after reverting, but the log may be useful in helping to understand what changes were made to the database after the snapshot was taken by using a log explorer tool. In addition, if you are using full recovery mode for your logs, you can restore the database on a separate server and use the point-in-time recovery feature to restore only up to the point that the failure or error was made. You can then bulk-export the changes and bulk-import the changes into your newly reverted database. Finally, perform a full backup of your database after you revert to the snapshot. The following code will revert back to the snapshot created earlier:

    RESTORE DATABASE AdventureWorks from
    DATABASE_SNAPSHOT = 'AdventureWorks_dbss031908'

Performance Considerations When Using Snapshots on Mirrors

Some special considerations relate to using snapshots on your mirrors. Remember that if you are running with FULL transaction safety on, the principal will wait for an acknowledgment from the mirror. If you are running many snapshots that users are querying against on the mirror, that will affect the performance of both the mirror and, in turn, the principal. One of the ways that you can make your snapshots more performant is to place your snapshots on a different drive than your mirror’s log. This way, the two technologies do not contend for I/O with one another.

You will also want to move the snapshots from the old mirror to the new mirror when it comes back online. This is not done automatically for you, so you will need to script the creation of the snapshots on a failure. Also, you will need to change your client applications, since the snapshot will now be on a different server.

Using and Monitoring Database Snapshots

Since snapshots look like real databases, you can easily query them for the data contained in the snapshot. Rather than specifying the source tables, you can use the tables contained in your snapshot. The following query returns a list of customers from the Customers table in the snapshot:

, [GroupName]
, [ModifiedDate]
FROM [AdventureWorks_dbss031908].[HumanResources].[Department]

Management Studio will display the snapshots you have created. If you want to view the physical path to the database snapshot, you can use the physical_name column of the sys.master_files catalog view. Note that if you query the same column in sys.database_files, the view will always return the source database files, even if you specify the database snapshot.

To determine the actual size of the sparse files, you can use the BytesonDisk column from the fn_virtual filestats function. This function takes a database ID and a file ID as values. The function willthen return the file statistics. Also, you could use Windows Explorer to view the size of the file using the Size on Disk value from the file’s Properties dialog box.

The following code returns information about the snapshot file. Notice the use of the DB_ID and FILE_IDEX functions to get the database ID and the file ID. Note that the file ID will correspond to the logical name of the file from sys.master_files. You could also retrieve both of these values from sys.master_files.

FROM fn_virtualfilestats(DB_ID(N' AdventureWorks_dbss031908'),

Programming against a database snapshot is very straightforward. Instead of connecting to the original database, you just connect to the snapshot in your connection string. The only caveat is that you will need to manually connect to a different snapshot if you want to change snapshots.

Limitations of Database Snapshots

While there are many benefits to database snapshots, you should also be aware of their limitations. The biggest one is that database snapshots are available only in the Enterprise Edition of SQL Server 2008. If you have the Express, Workgroup, or Standard Edition, you do not have access to the database snapshot functionality.

Second, the database and its snapshot must exist in the same instance. You cannot create a snapshot in a separate instance. While snapshots exist on a source database, you cannot drop, detach, or restore the source. You must first delete all snapshots for that database. Backup of the source database is unaffected by snapshots, so you can back up the database while you have active snapshots. Also, you cannot snapshot the master, tempdb, or model databases.

For the snapshot itself, remember it is a read-only copy reflecting the exact state of the source database at the creation time of the snapshot, with any uncommitted transactions rolled back. Files cannot be changed on the snapshot. Permissions are inherited from the source and cannot be changed. This is important, because if you change the permissions on the source database, these changes will not be reflected in existing snapshots. You may have users who you no longer want to have permissions on your data, but if you have snapshots, you must make sure to re-create your snapshots after changing permissions. Snapshots cannot be backed up or restored, nor can they be attached or detached. Also, you cannot create snapshots on FAT32 or RAW partitions. Finally, snapshots do not support full-text indexing, and any full-text catalogs on the source will not exist in the snapshot.

Remember that the snapshot is not an entire copy of the source database. The snapshot will go back to the source database for data that has not changed. This means that the state of the source database will affect the snapshot. If the source database goes into recovery mode, some data may not be available in the snapshot. If the source database takes a file offline, some data in the snapshot may not be available as well. If when you created the snapshot a filegroup was offline in the source and you bring it online later, it still will be considered offline in the snapshot. You will want to make sure you understand the state of both the snapshot and the source database; otherwise, you may get interesting errors from the snapshot that you may not realize are caused by the state of the source database.

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd Protection Status

SQL Server 2008 Topics