Simple Cluster Failover Checker
Published: 8th October 2013
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.
NB: Comments will only appear once they have been moderated.