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

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.

    
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 at 16:50

1 Answer 1

up vote 0 down vote accepted

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.