Automatic alert when cluster fail over happened

When  fail over happen, both SQL server and Agent service will get restarted, Then we will get Notification Alert When Fail Over Happened 


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 @recipients VARCHAR(100)

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 ='sqlmail',

         @recipients ='bajeyudu@SQLDBANOW.com;',

     --@recipients=@recipientsList,

     @subject = @subject ,

     @body = @body,

     @body_format = 'HTML' ,

     @importance=@importance

No comments:

Post a Comment