0

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)?

Improve this question
2
  • What version of SQL Server? Also, what exactly are you trying to accomplish with the use of the snapshot that the mirror would not suffice?
    – SQLRockstar
    Sep 5, 2014 at 16:50
  • thank you this helps, we are on SQL 2012 Enterprise, just learned we cannot use Snapshot on Mirroring with High-Performance Asynchronous State? that's obstacle for company, we will look into Synchronous mode then
    – user129291
    Jun 17, 2018 at 17:57

1 Answer 1

Reset to default
0

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 ???

Improve this answer
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.