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

Getting error "[311] Maildispatcher Thread (ID 1444) still running" when I try to restart

H
I am using SQLMail in my SQL Servers for the alert notifications. Today it has failed due to some reasons. I was trying to restart the SQLServerAgent , I got the error "[311] Maildispatcher Thread (ID 1444) still running"
It may be as the SQL Mail is conflicting with SQLServerAgent
Can you please tell me how to resolute this issue without restarting the MSSQLServer Services
Some forums say that, remove the mail profile from the SQLServerAgent Properties. This condition is true when the SQLServerAgent is running. But when it is stopped then there is no profile desplayed in the property box
Please help..
Thanks in advanc
DJ> I am using SQLMail in my SQL Servers for the alert notifications.
> Today it has failed due to some reasons. I was trying to restart
> the SQLServerAgent , I got the error "[311] Maildispatcher Thread
> (ID 1444) still running".
> It may be as the SQL Mail is conflicting with SQLServerAgent.
> Can you please tell me how to resolute this issue without
> restarting the MSSQLServer Services.
> Some forums say that, remove the mail profile from the
> SQLServerAgent Properties. This condition is true when the
> SQLServerAgent is running. But when it is stopped then there
> is no profile desplayed in the property box.
> Please help...
> Thanks in advance
> DJ
>
--
Hi DJ,
It looks like SQL Agent was abruptly stopped whilst SQL Mail was still
running. To get SQL Agent running, remove SQL mail profile by running the
following:
EXECUTE msdb.dbo.sp_set_sqlagent_properties @.email_profile = 'N'
Once you have SQL Agent started, add back the mail profile and then
troubleshoot the SQL Mail problem as you would normally do. This article
might help:
HOW TO: Troubleshoot SQL Mail with Microsoft Exchange Server
http://support.microsoft.com/?id=153159
Regards,
--
Eric Cárdenas
Senior support professional
This posting is provided "AS IS" with no warranties, and confers no rights.