Conference Questions Pot-Pourri #2: Database mirroring

Sitting here in our Disaster Recovery class at SQL Connections and Kimberly’s on till lunch so I’m banging out a quick blog post covering the database mirroring (DBM) specific questions.

Q1) Can I use IP addresses instead of server names when using the DBM Monitor?

A1) Unfortunately not.

Q2) Is there any in-built throttling mechanism in DBM to allow the mirror to catch-up when synchronizing a synchronous mirroring session?

A2) Yes, if there’s more than 1MB of transaction log on the principal that hasn’t been sent to the mirror, the mirroring session state will be switched from SYNCHRONIZED to SYNCHRONIZING and the principal itself will start adding a few milliseconds delay to transaction commits until the amount of unsent log drops below 1MB

Q3) Are there any tips when setting up a mirroring session using backups?

A3) Yes, make sure that all the backups are restored WITH NORECOVERY on the mirror. The database has to be unrecovered otherwise the mirroring session cannot start and you’re back to square one with restoring the mirror database.

Q4) What are the performance considerations with DBM – both in terms of the impact on the application workload and on DBM itself?

A4) Here are some links to resources that discuss this:

Q5) Any other resources?

A5) Blog posts…


6 thoughts on “Conference Questions Pot-Pourri #2: Database mirroring

  1. Hello Paul,

    Is there any way to find when the database mirroring was broken and by whom. I am facing this issue with senior managements and Auditors as to who and when the mirroring was broken.
    Funny part is the Alerting tool did not capture the breaking of mirroring and i am not able to find any information in the error log or the Windows Event viewer.
    Not sure if i was looking at wrong place.
    Is there other place where the information could be stored.

    Thanks and Warm Regards

    Praveen chandran

    1. It’ll be in the transaction log because there is a state change that’s logged. I don’t have a mirroring configuration right now so you’ll need to investigate yourself. Once you identify the transaction, you can use the Transaction SID and suser_sname to figure out who did it.

  2. Hi Paul,
    I know it is a very old post still would like to ask a question on synchronous mirroring.
    Does principal waits for acknowledgment from mirror before it commit the tran or it commits first but does not send confirmation to client before it gets acknowledgment from mirror.


    1. With sync dbm, the transaction can’t commit on the principal until acknowledgement from the mirror that the log block with the LOP_COMMIT_TRAN log record is written to the mirror’s log drive.

  3. Is “switching from SYNCHRONIZED to SYNCHRONIZING” via throthling still present in AlwaysOn or when was was it removed/changed ?

    1. Not a DBM question, but from Jonathan: The HADR_SYNCHRONIZING_THROTTLE wait type is still in 2022 so I would say that it is still there. I don’t know of any changes that would make it no longer implemented. I don’t think I have seen that wait type occur frequently on 2016+ due to the changes that optimized control flow between primary and secondary replica’s, but I also don’t have a lot of AG’s that I manage where the secondary is in ASYNC commit for a prolonged period with the log queue would backup and then I try to force it over to SYNC commit where the throttling would have to be implemented to get the secondary caught up to synchronized.

Leave a Reply

Your email address will not be published. Required fields are marked *

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.