How to measure the time needed to switch a database mirroring role?

Question

I want to measure the time needed for SQL Server to switch server roles. Is there a way to know how long does it take the mirror to become the principal and ready for new updates?

Thank you very much!

Answer 1

Monitor using the profiler infrastructure for the Database Mirroring State Change Event Class. StartTime will contain the time of the event, State will contain the new state after the change. I'm not sure I remember if the state change occurs before the new principal runs the recovery or after though, you'll have to test and see. Judging from Nick's post I'd say is after, so the event is perfect for you.

Answer 2

have you tried looking in the errorlog on the server you've failed over to? should see something like this:

2010-02-01 16:33:50.600,spid23s,Starting up database 'adventureworks'.
2010-02-01 16:33:54.240,spid23s,2 transactions rolled forward in database 'adventureworks' (8). This is an informational message only. No user action is required.
2010-02-01 16:33:54.330,spid28s,0 transactions rolled back in database 'adventureworks' (8). This is an informational message only. No user action is required.
2010-02-01 16:33:54.330,spid28s,Recovery is writing a checkpoint in database 'adventureworks' (8). This is an informational message only. No user action is required.
2010-02-01 16:33:55.220,spid12s,Database mirroring is active with database 'adventureworks' as the principal copy. This is an informational message only. No user action is required.

Answer 3

A really ugly brute-force method could be to have a little app that connetcs to the principal once per second. If you configure it with explicit client redirection, you'll be able to tell at what point it fails over and you can successfully connect to the new principal.

Look up the SqlConnection .Net class - the DataSource member on a successful connection gives you the server name you're connected to.

Keeping track of the times involved is a SMOC.

Hope this helps.