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!

Synchronous or Asynchronus? – SuperCoolMoss Jan 31 '10 at 16:13 — SuperCoolMoss, Jan 31 '10 at 16:13
Both of. :D. I want to demo about database mirroring – Vimvq1987 Feb 01 '10 at 14:01 — Vimvq1987, Feb 01 '10 at 14:01

score 2 · Answer 1 · answered Feb 09 '10 at 23:55

2

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.

answered Feb 09 '10 at 23:55

Remus Rusanu

8,253
1
19
22

This is probably the best way to do it. – tcnolan Feb 11 '10 at 00:14

score 1 · Answer 2 · answered Feb 01 '10 at 05:52

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.

is there someway to know it "automatically"? I mean, a method that we can know the downtime by a program? — Vimvq1987, Feb 01 '10 at 13:59
you mean _before_ you failover? that's not so easy to answer — Nick Kavadias, Feb 01 '10 at 14:23

score 1 · Answer 3 · answered Feb 01 '10 at 22:09

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.

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

3 Answers3