Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

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!

share|improve this question
    
Synchronous or Asynchronus? –  SuperCoolMoss Jan 31 '10 at 16:13
    
Both of. :D. I want to demo about database mirroring –  Vimvq1987 Feb 1 '10 at 14:01

3 Answers 3

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.

share|improve this answer
    
This is probably the best way to do it. –  tcnolan Feb 11 '10 at 0:14

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.
share|improve this answer
    
is there someway to know it "automatically"? I mean, a method that we can know the downtime by a program? –  Vimvq1987 Feb 1 '10 at 13:59
    
you mean before you failover? that's not so easy to answer –  Nick Kavadias Feb 1 '10 at 14:23

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.