Database Snapshots

As you have probably figured out, the mirror database is in NORECOVERY mode, so you cannot read the mirror database. You will want to read data from the mirror database for some reason, however.

SQL Server 2005 (Enterprise Edition and Developer Edition) has a new feature called Database Snapshots. A database snapshot is a read-only, static view of a database (the source database). This feature will come handy to read the mirror database. Multiple snapshots can exist on a source database and always reside on the same server instance as the database. Each database snapshot is transactionally consistent with the source database as of the moment of the snapshot's creation. A snapshot persists until it is explicitly dropped by the database owner.

Using this feature, you can create a snapshot on the mirror database. You can read the snapshot database as you would read any other database. The database snapshot operates at data-page level. Before a page of the source database is modified for the first time, the original page is copied from the source database to the snapshot. This process is called a copy-on-write operation. The snapshot stores the original page, preserving the data records as they existed when the snapshot was created. Subsequent updates to records in a modified page on the source database do not affect the contents of the snapshot. In this way, the snapshot preserves the original pages for all data records that have ever been modified since the snapshot was taken. Even if you change the source database, the snapshot will still have the same data from the time when it was created. (See the topic "Using Database Snapshots with Database Mirroring" in SQL Server Books Online for more information.)

The following is an example of how to create a snapshot on the TestMirroring database:

 CREATE DATABASE TestMirroring_Snapshot ON ( NAME = TestMirroring_Data1, FILENAME = C:\Test\Test_Mirroring_snapshot_Data1.SS') AS SNAPSHOT OF TestMirroring GO 

Since new data changes will be continuous on the mirror database, if you want to read the changed data in the snapshot after you have created it, you will need to drop the snapshot and recreate it again. You can drop the snapshot in the same manner as you would drop a database:

 DROP DATABASE TestMirroring_Snapshot 

Taking a database snapshot will have some performance impact on the mirror server, so please evaluate the impact if you want to create many snapshots on multiple databases on a server. Most important, from a DBM perspective, having too many snapshots on a mirror database can slow down the redo and cause the database to fall more and more behind the principal, potentially creating huge failover times. Also, prepare an area of disk space as big as the size of the source database, because as data changes on the source database, the snapshot will start copying original pages to snapshot files, and it will start growing. Replication is a great reporting solution, so you can consider that also.