Getting Notification / alert when failover occurs

Hi all,

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

I need to refresh SqlDependency after a failover.

Thanks,

Avi

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