Solved

Need tool and/or script for Monitoring Windows Cluster - Node Failover , Resource Failover, etc.

Posted on 2011-03-08
4
858 Views
Last Modified: 2012-05-11
Hi,

I have two Windows 2003 servers clustered, Active/Passive.  The app hosted is SQL Server 2005. I work for a low budget company and cannot afford to purchase a monitoring tool;

I need to be able to monitor when the nodes failover.  I would appreciate input re: free tools and/or  that can help do this.

Thank you.
D
0
Comment
Question by:dteshome
  • 2
4 Comments
 
LVL 39

Accepted Solution

by:
lcohan earned 500 total points
Comment Utility
Here's what I do from SQL to monitor which node I'm on and determine if failover occured: I have a SQL job scheduled to run every minute and executes the code below that sends an email alert if there there was a failover:

if (select cast((select ServerProperty('ComputerNamePhysicalNetBIOS') as server_name) as sysname) as server_name) <> (select server_name from MyDB..active_node)
begin

      DECLARE @sqlstr varchar(4000),
            @passive_node varchar(100),
            @active_node varchar(100)
      set @passive_node = (select server_name from MyDB..active_node);
      set @active_node  = (select cast((select ServerProperty('ComputerNamePhysicalNetBIOS') as server_name) as sysname) as server_name);

      SET @sqlstr = 'SQL PROD failed over from node: ' + @passive_node + '     to: ' + @active_node +char(13) +char(13)
                              + 'Check SQL Logs for more details';


      EXEC msdb.dbo.sp_send_dbmail
            @profile_name = '911',
            @recipients = '911@whatever.com',
            @subject = 'SQL PROD Failover Alert!!!',
            @body = @sqlstr;

      update MyDB..active_node set server_name = (select cast((select ServerProperty('ComputerNamePhysicalNetBIOS') as server_name) as sysname) as server_name);

end
GO


--------
please note the code above relies on table:

create table MyDB..active_node (server_name      varchar(100))
0
 
LVL 25

Expert Comment

by:madunix
Comment Utility
0
 

Author Comment

by:dteshome
Comment Utility
Thank you for the info, I will sure use it in the future.
Dan
0
 

Author Closing Comment

by:dteshome
Comment Utility
Thank you!  This is a very clever approach.

Dan
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Backing up data is essential for any office small or large. Most think that a simple USB drive will suffice. Even the USB drives themselves display words like backup.   Most novices will ask themselves the question “Will this work for my business…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
In this Micro Tutorial viewers will learn how to use Windows Server Backup to create full image of their system. Tutorial shows how to install Windows Server Backup Feature on Windows 2012R2 and how to configure scheduled Bare Metal Recovery backup.…
This tutorial will walk an individual through the steps necessary to configure their installation of BackupExec 2012 to use network shared disk space. Verify that the path to the shared storage is valid and that data can be written to that location:…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now