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
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.
Comments:
NB: Comments will only appear once they have been moderated.