Database Mirroring and Snapshots

by RoughPlace   Last Updated June 17, 2018 23:06 PM

As part of a SQL Server deployment, it was proposed to use Database mirroring as a redundancy option as well as allowing for Reporting and MI to be detached from the Operational DB.

The proposal is that we setup the database mirror using asynchronous mirroring and then at regular intervals create snapshots.

However one line in MSDN states:

To create a database snapshot on a mirror database, the database must be in the synchronized mirroring state.

How does this work in practice? And what does this mean exactly?

How often will the database be in a non-synced state? (In a relatively moderate volume workload Database (~50 users).)

Will a create snapshot script wait until the DB is synchronized before executing, or do we need to prompt the DB to fully synchronize before the snapshot is taken (as part of the create snapshot script)?

Answers 1

asynchronous mirroring
It means, a transaction is committed on the Principal server before being copied over to the Mirror Server.

Whereas in Synchronous Mode Principal Server send the logs to the Mirror server and waits for the signal from the Mirror Server, once the transactions has been applied to the Mirror Server only then it commits the transaction on the Principal Server. Also the transaction Safety needs to be set to full and you to have a Witness Server.

Asynchronous mirroring does not effect the performance of Principal Server whereas other operating modes in Database Mirroring will have effect on the performance of the principal server as in Synchronous Mode both the principal and Mirror server are in the same state and you can have a long Redo Queue (Pending Transactions to be rolled-Forward on Mirror Server).

Note: Asynchronous mirroring is only available in the Enterprise Edition.

Reporting with snapshot script
Have you considered SNAP SHOT Replication for the reporting purposes ???

October 16, 2013 11:37 AM

Related Questions

Mirroing failover command taking too much time?

Updated August 21, 2017 07:06 AM

Endpoint modification in SQL Server mirroring

Updated February 22, 2016 06:02 AM

Strange Database Mirroring Problem

Updated April 07, 2016 08:02 AM