Track Your Cluster Failovers T-SQL Tuesday #33

Another awesome discussion for T-SQL Tuesday, and big thank you to Mike Fal. The question is what sort of tips and tricks you use with SQL Server. The first thing that came to mind for me was how I track the history of Windows cluster failovers. I am not a big fan of having to dive into error logs, events and emails to just find the history of my servers. Recently I ran into a situation where a Clustered SQL Server Install was performing better on one node of the cluster than the other node. I have a history of the performance, but I did not have a quick easy way to look at the history of what node was hosting when. The solution I came up with was to use my dbUtility database to track when a node was hosting. This way, I don’t have to go looking around error logs, or emails looking for any failover events. The solution is quite simple.

First I created a table to host the information that I wanted to track.

Simple enough? Below is a small version of the information that I track.

CREATE Schema CLU

GO

CREATE TABLE [CLU].[ClusterLogging](

[ClusterLoggingID] [int] IDENTITY(1,1) NOT NULL,

    [ClusterName] [sysname] NOT NULL,

    [PrimaryHostName] [sysname] NOT NULL,

    [ReadingDate] [datetime] DEFAULT getdate() NULL,

CONSTRAINT [PK_ClusterLoggingID] PRIMARY KEY CLUSTERED

    ([ClusterLoggingID]))

GO

Once the table and the Schema are there, I created a stored procedure that will check and find out the host is that is currently running. This information can be pulled from the Server Property Function without much difficulty, once I have that information, I checked to see if this is a new host, if it is a new status and log the information if it is new. This is the procedure that I use.

CREATE PROCEDURE CLU.Log_Cluster_Status

AS

Set NOCOUNT ON

DECLARE @PrimaryHostName SYSNAME

DECLARE @ClusterName SYSNAME

DECLARE @CurrentStatusID INT

SET @ClusterName = CAST(SERVERPROPERTY (‘MachineName’) AS SYSNAME)

SET @PrimaryHostName = CAST(SERVERPROPERTY (‘ComputerNamePhysicalNetBIOS’) AS SYSNAME)

— Is this a new entry? If so insert seed.

If Not Exists (Select ClusterName, PrimaryHostName From CLU.ClusterLogging

            WHERE ClusterName like @ClusterName

            AND PrimaryHostName like @PrimaryHostName)

    Begin

        Insert CLU.ClusterLogging (ClusterName, PrimaryHostName)

        VALUES (@ClusterName, @PrimaryHostName)

    END

— What is the ID of the current Status

SET @CurrentStatusID = (Select Max(ClusterLoggingID) From CLU.ClusterLogging

            WHERE Clustername like @ClusterName

            AND PrimaryHostName like @PrimaryHostName)

— Does the current hostname match the most recent status?

If @PrimaryHostName not like (Select PrimaryHostName From CLU.ClusterLogging

            WHERE ClusterLoggingID = @CurrentStatusID)

    Begin

        Insert CLU.ClusterLogging (ClusterName, PrimaryHostName)

        VALUES (@ClusterName, @PrimaryHostName)

    END

GO

All that is left is to schedule the execution of the stored procedure, I imagine it could be triggered as a startup procedure, but I just choose to run it on a schedule basis. I know that the times may be off a little depending on how often I run it however; it gets me in the ball park. Now I know that I can query the CLU.ClusterLogging table in my utility database and I will have a decent idea of what happened and when. Much more efficient than digging through large error logs that only go back so far.