Subscribe to this blog
Briefcase
Print
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".
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 =@@SERVERNAMESELECT @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 '+@NodeNameSELECT @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".
If you liked this post, do like my page on FaceBook at http://www.facebook.com/practicalSqlDba
www.PracticalSqlDba.com
Loading comments...