Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

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

Posted on 2011-03-08
4
870 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
ID: 35073760
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
ID: 35074955
0
 

Author Comment

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

Author Closing Comment

by:dteshome
ID: 35085504
Thank you!  This is a very clever approach.

Dan
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Learn about cloud computing and its benefits for small business owners.
In this Micro Tutorial viewers will learn how to restore single file or folder from Bare Metal backup image of their system. Tutorial shows how to restore files and folders from system backup. Often it is not needed to restore entire system when onl…
This tutorial will show how to configure a single USB drive with a separate folder for each day of the week. This will allow each of the backups to be kept separate preventing the previous day’s backup from being overwritten. The USB drive must be s…

829 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