Database Mirroring and Database Snapshots (SQL Server)

Applies to: SQL Server

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 snapshot 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 (SQL Server).

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 SQL Server Replication.

Example

This example creates snapshots on a mirrored database.

Assume that the database of a database mirroring session is AdventureWorks2022. 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 AdventureWorks2022.

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

2. Create the second database snapshot on the mirror of AdventureWorks2022. 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 AdventureWorks2022  
   

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

3. Create the third snapshot on the mirror AdventureWorks2022. 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 AdventureWorks2022  
   

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

Related Tasks

• Create a Database Snapshot (Transact-SQL)

• View a Database Snapshot (SQL Server)

• Drop a Database Snapshot (Transact-SQL)

See Also

Database Snapshots (SQL Server)
Connect Clients to a Database Mirroring Session (SQL Server)