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