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.
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
Create the second database snapshot on the mirror of
AdventureWorks2022
. Users who are still usingAdventureWorks_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.
Create the third snapshot on the mirror
AdventureWorks2022
. Users who are still usingAdventureWorks_0600
orAdventureWorks_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
See Also
Database Snapshots (SQL Server)
Connect Clients to a Database Mirroring Session (SQL Server)
Feedback
https://aka.ms/ContentUserFeedback.
Kommer snart: Under hela 2024 kommer vi att fasa ut GitHub-problem som feedbackmekanism för innehåll och ersätta det med ett nytt feedbacksystem. Mer information finns i:Skicka och visa feedback för