Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Automatic Alert Generation in the event of SQL Server Cluster Failover

By Yogeshwar Phull,

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.

 

Total article views: 3505 | Views in the last 30 days: 26
 
Related Articles
FORUM

Current QTD, Previous QTD

inserting Current QTD, Previous QTD to 'Calendar Type' column in date dim table

FORUM

Average of current and previous record Field

Average of current and previous record Field

FORUM

Clustering question -- Active/Active

Looking to control resources on an active, active cluster in case of failover.

ARTICLE

Finding “Active” Rows for the Previous Month (SQL Spackle)

SQL Server MVP Jeff Moden walks us through the classic problem of finding all the "Active" rows for ...

FORUM

Failover Alerts

Failover Alerts

Tags
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones