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.
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 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 1The 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:
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:
For SQL Logins:
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.
I have been reading your blogs/post quite a while . I really like the way you explain SQL features/issues/internals . I am sure your blogs will help me to improve my in-depth knowledge of SQL . I may trouble you by asking so many question in near future. In this space Just wanted to thank for sharing your knowledge with us. This is article is very good , it helps me to understand Snapshots better.
Can you explain how to create snapshot hourly on a data base?