Storing mirror database failover details to the respective server or on repository

by BeginnerDBA   Last Updated August 02, 2017 14:06 PM

I would need some practical suggestions on how to achieve below:

We have quite a few servers involved in database mirroring. As part of various activities and other fixes, we have database failover happening now and then.

What i am generally doing is browsing the sql error log to find the failover details for most of them

Is there a way , we can store or track the failover details for them either locally on server or on repository server?

Thanks



Answers 1


The concept is to create an extended event session listening to the DATABASE_MIRRORING_STATE_CHANGE event. This post covers the mechanics of querying extended event info using TSQL on live server, though haven't used this approach.

Otherwise, you can create the extended events session via SSMS, and configure that session to write the output to a file (xel) which can either be opened by SSMS or by using a system function that can convert the data to xml. The XEL ("event file") itself is binary. easiest way IMO is just to configure some kind of script to copy those files to a central instance, then open and process there.

Fields that DATABASE_MIRRORING_STATE_CHANGE event captures: https://docs.microsoft.com/en-us/sql/relational-databases/event-classes/database-mirroring-state-change-event-class.

See https://msdn.microsoft.com/en-us/library/hh213147.aspx for step by step to set up the an extended events session that dumps results to an event file. (needs to be done per instance, though can be scripted). Fairly quick setup to see if it's giving you all the details you need.

You can include the DATABASE_MIRRORING_STATE_CHANGE event class as part of a trace as well.

Xingzhou Liu
Xingzhou Liu
August 02, 2017 18:05 PM

Related Questions





Database Mirroring error, but everything looks OK

Updated March 28, 2017 14:06 PM

Mirrored TDE DB Performance

Updated August 10, 2018 20:06 PM