SQL SERVER:How Can I Get Notification Alert When Fail Over Happened ?

It was long time I was searching for simple solution to get notification alert in my inbox when ever fail over happened in the cluster environment. SCOM will help us to do that , but I do not have direct control over it and the mail first goes to the Infrastructure support team and they forward the alert to the respective team.

Let us see how  DBA's can schedule an email alert with out depending on the SCOM or any other third party tool. When  fail over happen, both SQL server and Agent service will get restarted.In sql server job scheduler, there is an option to schedule a job when SQL server agent starts. For that we have to select the schedule type as "Start Automatically when sql server agent start".

To get an alert when a fail over happened, create a job with following code in the job step

DECLARE @importance AS VARCHAR(6)
DECLARE @body AS NVARCHAR(1000)
DECLARE @Subject  AS NVARCHAR(1000)
DECLARE @InstanceName AS VARCHAR(100)DECLARE @NodeName AS NVARCHAR(100)
DECLARE @recipientsList VARCHAR(100)SELECT  @recipientsList ='abc@yahoo.com,xyz@gmail.com'
SELECT  @InstanceName =@@SERVERNAME
SELECT  @NodeName = CAST(SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS NVARCHAR(200))
SELECT  @Subject  = 'Fail over Happened for Instance '+@InstanceName
SELECT  @body = 'Fail over Happened for Instance '+@InstanceName + '. This instance is currently running on the node '+@NodeName
SELECT  @importance ='High' 
EXEC msdb.dbo.sp_send_dbmail
     @profile_name ='MyMailProfile',
     @recipients=@recipientsList,
     @subject = @subject ,
     @body = @body,
     @body_format = 'HTML' ,
     @importance=@importance


In the schedule pane select schedule Type as "Start Automatically when sql server agent start".

Do this on all instances of the cluster and it is ready . Wait for next fail over. Mail will be there in your inbox.Please be careful that you will get a mail even if the instance got restarted in the same instance or you just stopped and started the agent service. But that can be easily ignored or can be avoid by tweaking the above code little bit.

If you liked this post, do like my page on FaceBook at http://www.facebook.com/practicalSqlDba