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:
- Whitepaper: Database Mirroring: Best Practices and Performance Considerations
- Whitepaper: Database Mirroring: Alerting on Database Mirroring Events
- MSDN webcast – look at Sessions 8 and 9: TechNet Webcast Series for the ITPro – Series Links
Q5) Any other resources?
A5) Blog posts…
- SQL Server 2008: Automatic Page Repair with Database Mirroring
- Search Engine Q&A #2: Moving a database while Database Mirroring is running
- SQL Server 2008: New Performance Counters for Database Mirroring
- Search Engine Q&A #3: Database mirroring failover types and partner timeouts
- SQL Server 2008: Performance boost for Database Mirroring
- More on Database Mirroring performance and index maintenance
- Search Engine Q&A #8: How can defragging an index break synchronous mirroring? And what happens?
Enjoy!
6 thoughts on “Conference Questions Pot-Pourri #2: Database mirroring”
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
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.
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.
Regards,
Dev
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.
Is “switching from SYNCHRONIZED to SYNCHRONIZING” via throthling still present in AlwaysOn or when was was it removed/changed ?
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.