none
Getting Notification / alert when failover occurs

    Question

  •  

    Hi all,

     

    What kind of notification / alert can I get when a failover occurs?

     

     

     I need to refresh SqlDependency after a failover.

     

    Thanks,

    Avi

    Thursday, March 08, 2007 9:15 AM

All replies

  • Hello Avi,

    You want to stop the SqlDependency and start again with a different connection string or what?

    One possible notification is the profiler trace event, Database Mirroring State Change event: http://msdn2.microsoft.com/en-us/library/ms191502.aspx. This event is fired more often than when failover occurs, but two of the states are indicating failover (7 - Manual Failover and 8 - Automatic Failover). To programatically receive these I would go with event notifications, http://msdn2.microsoft.com/en-us/library/ms189453.aspx. :

    create event notification failover

          on server

          for database_mirroring_state_change

          to service 'myservicename', 'current database';

     

    In your application you must post a WAITFOR(RECEIVE ...) on the 'myservicename' queue to get the failover notifications. For this, you'll need to have some basic skills for programming Service Broker.

     

    BTW, note that Service Broker routing has built-in awarness of mirroring (see 'Mirrorr Address' in http://msdn2.microsoft.com/en-us/library/ms166052.aspx). But you cannot use this for SqlDependency's messages (SqlDependency uses Service Broker to receive the query notifications) because the Query Notifications are per SQL instance and do not failover with the database.

     

    HTH,

    ~ Remus

    Saturday, March 10, 2007 5:42 PM
    Moderator
  • I have follow up question on this one.

    1. Does WAITFOR(RECEIVE...) in your application waits for failover notification from Primary server or from secondary server or from both?

    2. If it only listens notification from Primary Server, what happens when Primary server crashes and secondary takes over role of primary. How will application know in such case.

    Thanks
    Govind
    Monday, March 16, 2009 6:04 PM
  • This is a pretty old question. But I believe that people still expect the answer. I just wrote a blog about this:

    http://bobhl.blogspot.com/2011/08/how-to-detect-database-mirroring.html

    As periodically DB access cannot be avoided, the easier way to detect the failover is to check the DataSource property of an opened database connection.

    • Proposed as answer by Bob H.L. _ Sunday, March 11, 2012 10:40 PM
    Monday, August 15, 2011 3:05 AM