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.