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
   create table
modifyDate datetime,
node varchar(10)
insert into currentNode
select current_timestamp, convert(varchar, serverProperty('ComputerNamePhysicalNetBIOS'))

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

if @node != (select node from currentNode)
@tableHTML nvarchar(max)
select @tableHTML = '<div style="font-family:calibri; font-size:14pt;">
<b>Node Switch</b>
<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 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>
from currentNode

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

update currentNode
set modifyDate = current_timestamp,
node = @node
set modifyDate = current_timestamp

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



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