“The SQL Guy” Post # 2: USING THE MIRROR DATABASE FOR REPORTING/QUERYING PURPOSE

Before I get to the meat of this blog post, I have to acknowledge the fact that the technical content of “the SQL guy” blog posts is the work of Saleem Hakani from the SQL Server product team at Microsoft.  Saleem is a wealth of information and I find his posts of great value that I want to share them with you.  Thanks Saleem!!

Now, onto the topic at hand…

How many times have you thought about using the Mirror database for some read activity or for reporting purpose? SQL Server currently doesn’t support reading the data directly from the mirror database (SQL Server Denali will be supporting this feature). However, even with the current version of SQL Server, you can still read the data from the Mirror copy using Database Snapshots.

WHAT IS A DATABASE SNAPSHOT?

Database snapshot is a static, read-only, transaction-consistent snapshot of a user database as it existed at the moment of the snapshot creation. You can create Multiple Snapshots of the same database but they must all reside on the same server instance. Database Snapshots are primarily used for reporting purposes however, you can also use them for reverting changes (due to user errors, accidently deleting data or objects, etc.) to the state it was in when the snapshot was created.

USING DATABASE SNAPSHOTS WITH MIRRORED DATABASES

In the database mirroring environment, Principal DB interacts with all the users and the mirror database only receives transaction log records from the principal database as the mirror database in a DBM session will be in a “RECOVERING” state. 

With the introduction of SQL Server 2005, a new feature was added called Database Snapshots. And you can now use this feature to create a database snapshot from the Mirror database for reporting purpose. You can direct all client connection requests to the most recent  database snapshot created from the Mirror database. If you are looking for updated data in your snapshot database then you will need to create new snapshots of that database periodically to get the latest data from the mirror database.

KEEP IN MIND: You can create a database snapshot on the mirror database only when the database is fully synchronized. Also, having an excessive number of database snapshots on the mirror database may decrease the performance of the principal database. Therefore, it is recommend that you don’t create multiple database snapshots of the same mirror copy. You should delete the old copies and keep the current one for reporting purpose.

WHAT HAPPENS DURING A ROLE SWITCH?

If role switching occurs, the database and its snapshots are restarted by temporarily disconnecting users. Afterwards, the database snapshots remain on the server instance where they were created, which would now become the new principal database. Reporting users can continue to use the snapshots after the failover. However, this would place an additional load on the new principal server and if performance is a concern in your environment than it is recommended that you create a snapshot on the new mirror database when it becomes available and redirect your clients to the new snapshot, and drop the database snapshots from the former mirror database.

HOW TO CREATE A DATABASE SNAPSHOT OF THE MIRROR DATABASE

Let’s create a snapshot on the mirror database called MSSOLVE. Make sure you are connected to the mirror database instance when you create this.

Once you successfully create the database snapshot, you are now ready to use the new snapshot of the mirror database for querying/reporting purpose.

WHERE CAN I VIEW THE NEWLY CREATED DATABASE SNAPSHOT?

You may wonder why the newly created database snapshot doesn’t appear in the database list in management studio. That’s because database snapshots are listed under Database Snapshots folder right below the System Databases folder in Management Studio. In the object explorer of Management Studio, connect to the instance of Microsoft SQL Server and expand “Databases” and then expand “Database Snapshots”.

HOW TO DROP A SNAPSHOT DATABASE?

You can drop the database snapshot the exact same way as you would any other user database using the Drop Database command.