In the database mirroring environment Principal interacts with all the users but the mirror server only receives transaction log data from the principal server and other than that mirror database doesn’t do anything as its in RECOVERING state all the time. In this knowledge nugget, we will explore how you can use Mirror database for Reporting or Querying purpose.
What are Database Snapshots?
Database snapshot is a static, read-only, transaction-consistent snapshot of its source database as it existed at the moment of the snapshot creation. With Database Snapshots you can take advantage of the mirror database for high availability purposes to offload reporting. You can create a database snapshot on the mirror database and direct client connection requests to the most recent snapshot. You can create a database snapshot on the mirror database only when the database is fully SYNCHRONIZED.
As long as both mirror and principal server are communicating with each other, database snapshots can be accessible to clients. Keep in mind that as database snapshot is static, new data is not available. You must create new database snapshots periodically and have applications direct incoming client connections to the newest snapshot.
The new database is almost empty, but it grows over time as more and more database pages are updated for the first time. Because every snapshot on a database grows incrementally in this way, each database snapshot consumes as much resources as a normal database. Depending on the configurations of the mirror server and principal server, having an excessive number of database snapshots on a mirror database might decrease performance on the principal database. Therefore, it is recommended that if you can live without touching the mirror databases / servers, great and if not you can keep only a few relatively recent snapshots on your mirror databases.
What Happens During a Role Switch?
If role switching occurs, the database and its snapshots are restarted, temporarily disconnecting users. Afterwards, the database snapshots remain on the server instance where they were created, which has become the new principal database.
Users can continue to use the snapshots after the failover. However, this places an additional load on the new principal server. 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 clients to the new snapshot, and drop all of the database snapshots from the former mirror database.
Creating a Database Snapshot of a user database
Lets create a snapshot on the mirror database, make sure you are connected to the mirror database:
USE MASTER; CREATE DATABASE SQLCOMMUNITY_SnapSHOT_0400 ON (Name= 'SQLCOMMUNITY_Data', --This is the logical name of the data file of SQLCOMMUNITY database FILENAME='H:\MSSQL\DATA\SQLCOMMUNITY_0400&.snp') AS SNAPSHOT OF SQLCOMMUNITY Go
You are now ready to use database snapshots for querying mirror databases.
How and where to view database snapshots?
In the object explorer, connect to the instance of Microsoft SQL Server and expand Databases and then expand Database Snapshots and select the snapshot you want to view.
Dropping a Database Snapshot
Once you are done working with database snapshots, you may drop the database snapshots the same way as you would any user databases:
-- Deleting a snapshot database Use Master; Drop Database SQLCOMMUNITY_SnapSHOT_0400 Go
Recent Comments