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

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
dteshomeAsked:
Who is Participating?
 
lcohanConnect With a Mentor Database AnalystCommented:
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
 
dteshomeAuthor Commented:
Thank you for the info, I will sure use it in the future.
Dan
0
 
dteshomeAuthor Commented:
Thank you!  This is a very clever approach.

Dan
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.