Worth Corner, Crawley, RH10 7SL

Support Hours - Mon-Sat, 8.00-18.00

Welcome to our SQL Server blog

This blog was created in order to pass on some of our knowledge and advice in the hope that the community might find it useful.

Obviously Microsoft SQL Server is our area of expertise and therefore this is our most thorough blog with everything from the basic through to the the incredibly advanced and, as such, we should have something for everyone within our pages.


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.

SQL  World  CEO
Kevin  Urquhart

iPhone Selfie

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

Categories


© Copyright 2020 SQLTraining Ltd.