Database Mirroring and Database Snapshots

  • Article

You can take advantage of a mirror database that you are maintaining for availability purposes to offload reporting. To use a mirror database for reporting, you can create a database snapshots on the mirror database and direct client connection requests to the most recent snapshot. A database snapshot is a static, read-only, transaction-consistent snapshot of its source database as it existed at the moment of the snapshot's creation. To create a database snapshot on a mirror database, the database must be in the synchronized mirroring state.

Unlike the mirror database itself, a database snapshot is accessible to clients. As long as the mirror server is communicating with the principal server, you can direct reporting clients to connect to a snapshot. Note that because a database snapshot is static, new data is not available. To make relatively recent data available to your users, you must create a new database snapshot periodically and have applications direct incoming client connections to the newest snapshot.

A new database snapshot 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, we recommend that you keep only a few relatively recent snapshots on your mirror databases. Typically, after you create a replacement snapshot, you should redirect incoming queries to the new snapshot and drop the earlier snapshot after any current queries complete.

Note

For more information about database snapshots, see Database Snapshots.

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, we recommend that you create a snapshot on the new mirror database when it becomes available, redirect clients to the new snapshot, and drop all of the database snapshots from the former mirror database.

Note

For a dedicated reporting solution that scales out well, consider replication. For more information, see Data Warehousing and Reporting.

Example

This example creates snapshots on a mirrored database.

Assume that the database of a database mirroring session is AdventureWorks. This example creates three database snapshots on the mirror copy of the AdventureWorks database, which resides on the F drive. The snapshots are named AdventureWorks_0600, AdventureWorks_1200, and AdventureWorks_1800 to identify their approximate creation times.

  1. Create the first database snapshot on the mirror of AdventureWorks.

    CREATE DATABASE AdventureWorks_0600
ON (NAME = 'datafile', FILENAME = 'F:\AdventureWorks_0600.SNP')
   AS SNAPSHOT OF AdventureWorks

  2. Create the second database snapshot on the mirror of AdventureWorks. Users who are still using AdventureWorks_0600 can continue to use it.

    CREATE DATABASE AdventureWorks_1200
ON (NAME = 'datafile', FILENAME = 'F:\AdventureWorks_1200.SNP')
   AS SNAPSHOT OF AdventureWorks

    At this point, new client connections can be programmatically directed to the latest snapshot.

  3. Create the third snapshot on the mirror AdventureWorks. Users who are still using AdventureWorks_0600 or AdventureWorks_1200 can continue to use them.

    CREATE DATABASE AdventureWorks_1800
ON (NAME = 'datafile', FILENAME = 'F:\AdventureWorks_1800.SNP')
    AS SNAPSHOT OF AdventureWorks

    At this point, new client connections can be programmatically directed to the latest snapshot.

To create a database snapshot
To view a database snapshot
To delete a database snapshot

See Also

Concepts

Database Snapshots

Other Resources

Connecting Clients to a Mirrored Database

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release History

14 April 2006
New content:
  • Added that the database must be in the synchronized mirroring state.
  • Added information about resource usage.

5 December 2005
Changed content:
  • Expanded the discussion of the impact of role switching on database snapshots.