Recently we had a requirement where we were asked to generate automatic alerts in the event of cluster failover.

We have a two node active/passive cluster, and we have around 10 instances including SQL Server 2005 and SQL Server 2008. I did a lot of research to find the failover information in SQL Server itself, but could not find anything. So, I thought of devising a plan by which we can generate automatic email notification in the event of a failover.

Major Challenge

When I was thinking about the plan, the major challenge was to capture the physical hostname of the node on which an instance is currently running. I thought of a couple of options:

• HOST_NAME() function - This gives SQL Server instance's virtual name instead of physical hostname in the case of a clustered environment.
• SERVERPROPERTY('ComputerNamePhysicalNetBIOS') - This one is also not reliable as it unexpectedly gives null value sometimes.

So finally I found the xp_regread extended stored procedure that fetches information directly from the registry. By using this extended stored procedure, we can fetch physical hostname of the node and use that in the query.

Steps to Implement

We'll use the names "node1" and "node2" as examples for our two cluster nodes, and "SQLExample" as the SQL instance name. (Please note node1 and node2 are physical hostnames)

Consider node1 is currently active and node2 is passive. Below are the steps to implement this plan:

Step 1: Create a table named [CLUSTERFAILOVERMONITOR] in the master database of the SQL Server instance, with a column [PREVIOUS_ACTIVE_NODE]. Use the query below for creating the table.

create table CLUSTERFAILOVERMONITOR
(
PREVIOUS_ACTIVE_NODE varchar(30)
)

Step 2: Insert the value of the currently active node in that table. Use the query below for this.

insert into CLUSTERFAILOVERMONITOR

values

(

'node1'

)

Step 3: Create a job with code shown below in it. The job will execute on the master database, will run every minute (can be run every 5-10 seconds in SQL Server 2008) and when the failover happens it will send a notification.

After the failover has occurred, this job will change the value of PREVIOUS_ACTIVE_NODE column in the CLUSTERFAILOVERMONITOR table that we created in step1, and is therefore ready to monitor the next failover event.

Declare @var1 varchar(30)
SELECT @var1= PREVIOUS_ACTIVE_NODE FROM CLUSTERFAILOVERMONITOR
CREATE TABLE PHYSICALHOSTNAME
(
VALUE VARCHAR(30),
CURRENT_ACTIVE_NODE VARCHAR(30)
)
INSERT INTO PHYSICALHOSTNAME
exec master..xp_regread 'HKEY_LOCAL_Machine',
'SYSTEM\CurrentControlSet\Control\ComputerName\ComputerName\',
'ComputerName'
declare @var2 varchar(30)
SELECT @VAR2=CURRENT_ACTIVE_NODE FROM PHYSICALHOSTNAME
if @VAR1<>@VAR2
Begin
EXEC msdb..sp_send_dbmail @profile_name='DBAMail',
@recipients='xyz@company.com',
@subject=' Failover occurrence notification - SQLExample',
@body='Cluster failover has occured for instance SQLExample. Below given are the previous and current active nodes.',
@QUERY='SET NOCOUNT ON;SELECT PREVIOUS_ACTIVE_NODE FROM CLUSTERFAILOVERMONITOR;SELECT CURRENT_ACTIVE_NODE FROM PHYSICALHOSTNAME;SET NOCOUNT oFF'
update CLUSTERFAILOVERMONITOR set PREVIOUS_ACTIVE_NODE=@VAR2
End
DROP TABLE PHYSICALHOSTNAME

Obviously the output can be decorated as much as someone wants to give the final email result a better look.

By using this plan we can have an automatic alert sent, no matter how many times a failover occurs. This method is not robust, but offers a simple effective solution to monitoring failover events in your environment, without any fuss or added cost.