Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

AS part of a SQL-Server deployment we are doing 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 practise? 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 synchronised before executing, or do we need to prompt the DB to fully synchronise before snapshotting (as part of the create snapshot script)

share|improve this question

migrated from stackoverflow.com Oct 17 '13 at 6:30

This question came from our site for professional and enthusiast programmers.

1 Answer 1

asynchronous mirroring
It means when any transactions on Principal server are committed before being copied over to Mirror Server. Whereas in Synchronous Mode Principal Server waits before committing any transactions until they are also applied onto Mirror 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).
Reporting with snapshot script
Have you considered SNAP SHOT Replication for the reporting purposes ???

share|improve this answer
    
snapshot replication looks useful thank you –  Yakyb Oct 16 '13 at 13:16

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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