Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

PracticalSQLDba

I have total of 11 years of IT experience with Application development, Database Development and Database Administration. I have worked with different version of SQL server from 7.0 to 2008.Started my carrier as VB ,VC++ and database developer in a banking sector for implementing their core banking solution. Currently working as Database Administrator with wide knowledge in performance tuning, high availability solution, troubleshooting and server monitoring. This blog is my humble attempt to share my knowledge and what I learned from my day to day work.

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





Comments

Leave a comment on the original post [www.practicalsqldba.com, opens in a new window]

Loading comments...