What kind of notification / alert can I get when a failover occurs?
What kind of notification or alert can I get when a failover occurs?
This was the question asked when I have had my presentation on Effective Usage of Database Mirroring in SQL Server open world during March 09th 2007. 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 :
create event notification failover
on server
for database_mirroring_state_change
to service 'myservicename', 'current database';