Internals of Database Snapshot – Part 3: Snapshot and Mirroring

  • Article

In my earlier posts, we have seen how database snapshots work, and how the Buffer Pool is used when we query Snapshot Databases. In this post, we will see how Database Snapshots work, when they are created against Mirrored Databases.

Let us assume we have a mirroring setup, as described in the below image. SQLServer1 hosts the Principal Database (MyDB) while the same database on SQLServer2 is the Mirror Database.

image

It is possible to create a Snapshot of the Mirrored Database (MyDB on SQLServer2), in case we want to offload some of the Reporting Load from the Principal Database. Of course, we have to remember that the Snapshot presents data specific to a point in time; hence, users will be reading stale data till the Snapshot is refreshed (dropped and re-created).

Pre-requisites for being able to create a Snapshot of the Mirrored Database are:

  • The Principal and Mirror should be in Connected State.
  • The Principal and Mirror should be completely Synchronized at the time when the Snapshot is being created.

The Mirroring Mode (Asynchronous, Synchronous, and Synchronous with Automatic Failover) are not important as long as the pre-requisites above are true. However, in high transaction rate OLTP systems, with Asynchronous Mirroring setup, it is difficult to find a point in time when the Principal and Mirror are in a Synchronized state; hence, it is very difficult to create Snapshot on the Mirrored Database. If the databases are not synchronized, and we attempt to create a Snapshot on the Mirror Database, we will receive an error as below:

 Msg 1822, Level 16, State 1, Line 1
The database must be online to have a database snapshot.

The error can be misleading; because it says nothing about the Mirroring State issue that causes the Snapshot creation to fail; however, we have to keep in mind that error messages related to Database Mirroring are not very descriptive at the moment. The SQL Server Development Team is working on to make the error messages more meaningful, and it may take a while before we see more descriptive messages.

When it comes to the internals of how Database Snapshots work and how the Buffer Pool reacts to queries on the Snapshot Database, the same principals as mentioned in my previous posts still hold true. Only differences being:

-
the Reads happen, not from the Online Principal Database, but from the Mirror Database, and

-
the Copy-On-Write mechanism transfers pages from the Restoring Mirror Database and not the Online Principal Database.

If you are wondering how the Reads and Writes can happen from a Database that is in Restoring State, you must realize that the State of the Database is how the SQL Server Engine exposes the Logical Database for external users. The Physical Database Files, on the disk, are still available for Reads and Writes, and the SQL Server Engine does Read data from and Write data into these file. Additionally, there are quite a few undocumented DBCC commands that can work against the Restoring Database.

What happens when a role switching occurs is more important. During role switching, the Snapshot is not moved to the new Mirror Server. If the Snapshot was created on MyDB Database on SQLServer2 (in the image above), the Snapshot will still continue to exist on SQLServer2 even after SQLServer2 becomes the Principal Server. Now, if another role switch happens and SQLServer2 becomes the Mirror Server again, the Snapshot will still continue to be on SQLServer2. However, each Role Switch will cause the Database and the Snapshot to be restarted, and hence users will be temporarily disconnected.

One interesting question that was asked to me by one of my customers was: How can I give access to the Snapshot to a particular user without giving him access to the Principal Database?

The answer is not so straight-forward. The Snapshot is a Read-Only copy of the Database; and you cannot simply create an user in the Snapshot. Below are the steps that can be followed for granting access:

For Windows Logins:

-
Use sp_grantlogin to grant the Windows Account access to the Principal Server.

-
Use sp_grantlogin to grant the Windows Account access to the Mirror Server.

-
Add the login as an user in the Principal Database with appropriate privileges. This change will be automatically propagated to the Mirror Database.

-
Now, create a Snapshot of the Mirror Database.

-
Now, drop the User and the Login from the Principal Database / Server.

For SQL Logins:

-
Use sp_addlogin to add the login to the Principal Server.

-
Add the login as an user to the Principal Database.

-
Use KB918992 to move this login (along with the Password and the SID) to the Mirror Server.

-
Now, create a Snapshot of the Mirror Database.

-
Drop the User and Login from the Principal Database / Server.

Remember, this is not an One-Time activity. The steps outlined above will have to be repeated each time you refresh (drop and recreate) the Snapshot.

Disclaimer: All information provided here is my personal opinion and is neither verified nor approved by Microsoft before it is published. All information, and code samples, if any, is provided "AS IS" with no warranties and confers no rights.