Simple Cluster Failover Checker
Published: Jan 09, 2015
Obviously I would be shocked if you don’t already have something in place which will let you know that your SQL Server Cluster has failed over to another node. But I always like to have my own backups to any other automated alerting in order to keep me informed.

This, therefore, is just a simple piece of code that I have running every minute in a scheduled task which keeps an eye on the current node and sends me an email if it detects a failover.

As mentioned, this shouldn’t be your only monitor, but just a backup that you can customise as you see fit.

The code is a simple as this:

if object_id('currentNode') is null
begin
   create table
currentNode
  
(
      
modifyDate datetime,
      
node varchar(10)
   )
  
insert into currentNode
  
select current_timestamp, convert(varchar, serverProperty('ComputerNamePhysicalNetBIOS'))
end

declare
@node varchar(10) = (select convert(varchar, serverProperty('ComputerNamePhysicalNetBIOS')))

if @node != (select node from currentNode)
begin
   declare
@tableHTML nvarchar(max)
  
select @tableHTML = '<div style="font-family:calibri; font-size:14pt;">
<b>Node Switch</b>
</div><br><br>
<div style="font-family:calibri; font-size:12pt;">
'
+ @@serverName + ' has detected that the active node has switched from <b>' + node + '</b> to <b>'
                          
+ case node when 'Node1' then 'Node2' else 'Node1' end -- name your nodes accordingly
                          
+ '</b> on ' + left(convert(varchar, modifyDate, 113), len(convert(varchar, modifyDate, 113))-4) + '.
</div><br><br>
<div style="font-family:calibri; font-size:11pt;">
<i>Note:  If this is not a planned failover, please refer this alert to tech support for further investigation.</i>
</div>'
  
from currentNode

  
exec msdb.dbo.sp_send_dbmail
      
@profile_name = 'Mail Server', -- enter your mail server name here
      
@recipients = 'myEmail@myEmail.com', -- enter appropriate email address here
      
@body = @tableHTML,
      
@subject = 'Cluster Node Switch',
      
@importance = 'High',
      
@body_format = 'HTML'

  
update currentNode
  
set modifyDate = current_timestamp,
      
node = @node
end
else
begin
   update
currentNode
  
set modifyDate = current_timestamp
end


It will provide you with a nicely formatted email to let you know what’s happened.
Leave a Comment
Your email address will not be published. All fields are mandatory.
NB: Comments will only appear once they have been moderated.

Your SQL Trainer
Kevin Urquhart

I am a SQL Server DBA, Architect, Developer, and Trainer for SQL Training. This is my blog in which I’m simply trying to share my SQL knowledge and experiences with the world.

Kevin Urquhart
SQL Server Consultant, London

Categories


Archives


Copyright © 2018 London SQL Server Training Courses @ SQL Training | Website Design From Scott Heron