Get SQL Server Cluster Information - Stored Procedure

Problem

I always need to get cluster information of my server whenever I am investigating a database server. 

I tried searching in internet as someone might have already posted something similar but could not find one. 

I decided to create a stored procedure so that I can deploy it on all of my servers and be able to retrieve the cluster information quicker.

Below are cluster information that I need:

1. Instance Name?

2. Server (Cluster Node) Name/s

3. Is it Standalone or Clustered?

4. If Clustered, is it using Always On Availability Group (AG), Failover Cluster Instance (FCI) or combination of FCI and AG?

5. If Clustered, which node is Active/Primary and Passive/Secondary

6. If using Availability Group, what is the Availability Group Name and Listener Name being used?

7. What is the SQL Server version and patch level? are the patch level the same on all nodes?

8. Lastly, I need to make sure it works on older versions of SQL Server. I have tested this in SQL Server 2008 R2 and higher (SQL Server 2019 is the latest version at the time of this writing).

Solution

I will try to explain a few sections of the script that I used to return some of the requirements above.

Check SQL Server version:

Below is the query I used to check the SQL Server version, if value is less than 11, the version is below SQL Server 2012:

SELECT @SQLServerMajorBuildVersion = LEFT(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR), CHARINDEX('.',CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR))-1)

The sys.dm_os_cluster_nodes table for SQL Server 2008 R2 does not contain the is_current_owner column. The query to determine the primary node is different.

Check if the Database Instance is Clustered:

Below is the query I used to check if the the database server is clustered:

To check if failover cluster instance is being used, check this server property if the value is 1:

SELECT SERVERPROPERTY('IsClustered')

To check if availability group is being used, check this server property if the value is 1:

SELECT SERVERPROPERTY('IsHadrEnabled')

If both property values are 1, the database server is configure to use both failover cluster instance and availability group.

SELECT SERVERPROPERTY('IsHadrEnabled')) = 1 AND (SELECT SERVERPROPERTY('IsClustered')) = 1

Check Primary Node for Failover Cluster Instance:

I queried the sys.dm_os_cluster_nodes to get the nodes that are included in the cluster.

For SQL Server 2008 R2, I used this case statement:

CASE UPPER(NodeName)

    WHEN UPPER(CAST(SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS VARCHAR)) THEN 'PRIMARY'

    ELSE 'SECONDARY'

END AS HighAvailabilityRoleDesc

For SQL Server 2012 and higher, I used this case statement:

CASE is_current_owner 

    WHEN 1 THEN 'PRIMARY'

    ELSE 'SECONDARY'

END AS HighAvailabilityRoleDesc

Check Primary Node for Availability Group:

To get the primary node for Availability Group, you need to use the sys.dm_hadr_availability_group_states table and the primary_replica column. I have to join this with the sys.availability_replicas table and use a case statement to hardcode the values as PRIMARY or SECONDARY if I wanted it to run on any nodes.

The sys.dm_hadr_availability_replica_states table only returns multiple values when being queried in the primary server but only returns one row when querying the secondary node. Microsoft have stated this as an important information in their documentation.

To obtain information about every replica in a given availability group, query sys.dm_hadr_availability_replica_states on the server instance that is hosting the primary replica. When queried on a server instance that is hosting a secondary replica of an availability group, this dynamic management view returns only local information for the availability group.

SELECT UPPER(CAST(SERVERPROPERTY('ServerName') AS VARCHAR)) AS InstanceName

,UPPER(ar.replica_server_name) AS ServerName

,SERVERPROPERTY('IsClustered') AS IsClustered

,SERVERPROPERTY ('IsHadrEnabled') AS IsAvailabilityGroupEnabled

,ag.name AS AvailabilityGroupName

,agl.dns_name AS AvailabilityGroupListenerName

,CASE ar.replica_server_name WHEN hags.primary_replica THEN 'PRIMARY'

ELSE 'SECONDARY'

END AS HighAvailabilityRoleDesc

,'AG' AS SQLServerClusteringMethod

,@@VERSION AS SQLServerVersion

FROM sys.availability_groups AS ag

LEFT OUTER JOIN sys.availability_replicas AS ar ON ag.group_id = ar.group_id

LEFT OUTER JOIN sys.availability_group_listeners AS agl ON ag.group_id = agl.group_id

LEFT OUTER JOIN sys.dm_hadr_availability_group_states as hags ON ag.group_id = hags.group_id

ORDER BY 5,2; 

Below is the entire code of the stored procedure:

SET ANSI_NULLS ON;

SET ANSI_PADDING ON;

SET ANSI_WARNINGS ON;

SET ARITHABORT ON;

SET CONCAT_NULL_YIELDS_NULL ON;

SET QUOTED_IDENTIFIER ON;

SET STATISTICS IO OFF;

SET STATISTICS TIME OFF;

GO

IF OBJECT_ID('dbo.sp_ClusterInfo') IS NULL

  EXEC ('CREATE PROCEDURE dbo.sp_ClusterInfo AS RETURN 0;');

GO

ALTER PROCEDURE dbo.sp_ClusterInfo

AS

SET NOCOUNT ON;

SET STATISTICS XML OFF;

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

DECLARE @SQLServerMajorBuildVersion AS INT 

SELECT @SQLServerMajorBuildVersion = LEFT(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR), CHARINDEX('.',CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR))-1)

IF @SQLServerMajorBuildVersion < 11

BEGIN

       IF (SELECT SERVERPROPERTY('IsClustered')) = 1

       BEGIN

              SELECT UPPER(CAST(SERVERPROPERTY('ServerName') AS VARCHAR)) AS InstanceName  

              ,UPPER(NodeName) AS ServerName

              ,SERVERPROPERTY('IsClustered') AS IsClustered

              ,SERVERPROPERTY ('IsHadrEnabled') AS IsAvailabilityGroupEnabled

              ,NULL AS AvailabilityGroupName

              ,NULL AS AvailabilityGroupListenerName

              ,CASE UPPER(NodeName)

                     WHEN UPPER(CAST(SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS VARCHAR)) THEN 'PRIMARY'

                     ELSE 'SECONDARY'

              END AS HighAvailabilityRoleDesc

              ,'FCI' AS SQLServerClusteringMethod

              ,@@VERSION AS SQLServerVersion  

              FROM sys.dm_os_cluster_nodes

              ORDER BY NodeName;  

       END

       ELSE

       BEGIN

              SELECT UPPER(CAST(SERVERPROPERTY('ServerName') AS VARCHAR)) AS InstanceName  

              ,UPPER(CAST(SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS VARCHAR)) AS ServerName

              ,SERVERPROPERTY('IsClustered') AS IsClustered

              ,SERVERPROPERTY ('IsHadrEnabled') AS IsAvailabilityGroupEnabled

              ,NULL AS AvailabilityGroupName

              ,NULL AS AvailabilityGroupListenerName

              ,NULL AS HighAvailabilityRoleDesc

              ,'Standalone' AS SQLServerClusteringMethod  

              ,@@VERSION AS SQLServerVersion  

       END

END

ELSE

BEGIN

       IF (SELECT SERVERPROPERTY('IsHadrEnabled')) = 1 AND (SELECT SERVERPROPERTY('IsClustered')) = 1

       BEGIN

              SELECT UPPER(CAST(SERVERPROPERTY('ServerName') AS VARCHAR)) AS InstanceName

              ,UPPER(ar.replica_server_name) AS ServerName

              ,SERVERPROPERTY('IsClustered') AS IsClustered

              ,SERVERPROPERTY ('IsHadrEnabled') AS IsAvailabilityGroupEnabled

              ,ag.name AS AvailabilityGroupName

              ,agl.dns_name AS AvailabilityGroupListenerName

                       ,CASE ar.replica_server_name WHEN dhags.primary_replica THEN 'PRIMARY'

                       ELSE 'SECONDARY'

              END AS HighAvailabilityRoleDesc

              ,'FCI/AG' AS SQLServerClusteringMethod

              ,@@VERSION AS SQLServerVersion  

              FROM sys.availability_groups AS ag

              LEFT OUTER JOIN sys.availability_replicas AS ar ON ag.group_id = ar.group_id

              LEFT OUTER JOIN sys.availability_group_listeners AS agl ON ag.group_id = agl.group_id

                       LEFT OUTER JOIN sys.dm_hadr_availability_group_states as dhags ON ag.group_id = dhags.group_id

              WHERE UPPER(CAST(SERVERPROPERTY('ServerName') AS VARCHAR)) <> ar.replica_server_name

              UNION ALL

              SELECT UPPER(CAST(SERVERPROPERTY('ServerName') AS VARCHAR)) AS InstanceName  

              ,UPPER(NodeName) AS ServerName

              ,SERVERPROPERTY('IsClustered') AS IsClustered

              ,SERVERPROPERTY ('IsHadrEnabled') AS IsAvailabilityGroupEnabled

              ,NULL AS AvailabilityGroupName

              ,NULL AS AvailabilityGroupListenerName

              ,CASE is_current_owner 

                     WHEN 1 THEN 'PRIMARY'

                     ELSE 'SECONDARY'

              END AS HighAvailabilityRoleDesc

              ,'FCI/AG' AS SQLServerClusteringMethod

              ,@@VERSION AS SQLServerVersion  

              FROM sys.dm_os_cluster_nodes

              ORDER BY 5,2;  

       END

       ELSE

       BEGIN

              IF (SELECT SERVERPROPERTY('IsHadrEnabled')) = 1 

              BEGIN

   SELECT UPPER(CAST(SERVERPROPERTY('ServerName') AS VARCHAR)) AS InstanceName

   ,UPPER(ar.replica_server_name) AS ServerName

   ,SERVERPROPERTY('IsClustered') AS IsClustered

   ,SERVERPROPERTY ('IsHadrEnabled') AS IsAvailabilityGroupEnabled

   ,ag.name AS AvailabilityGroupName

   ,agl.dns_name AS AvailabilityGroupListenerName

   ,CASE ar.replica_server_name WHEN dhags.primary_replica THEN 'PRIMARY'

   ELSE 'SECONDARY'

   END AS HighAvailabilityRoleDesc

   ,'AG' AS SQLServerClusteringMethod

   ,@@VERSION AS SQLServerVersion

   FROM sys.availability_groups AS ag

   LEFT OUTER JOIN sys.availability_replicas AS ar ON ag.group_id = ar.group_id

   LEFT OUTER JOIN sys.availability_group_listeners AS agl ON ag.group_id = agl.group_id

   LEFT OUTER JOIN sys.dm_hadr_availability_group_states as dhags ON ag.group_id = dhags.group_id

   ORDER BY 5,2;  

              END

              ELSE 

              BEGIN

                     IF (SELECT SERVERPROPERTY('IsClustered')) = 1

                     BEGIN

                           SELECT UPPER(CAST(SERVERPROPERTY('ServerName') AS VARCHAR)) AS InstanceName  

                           ,UPPER(NodeName) AS ServerName

                           ,SERVERPROPERTY('IsClustered') AS IsClustered

                           ,SERVERPROPERTY ('IsHadrEnabled') AS IsAvailabilityGroupEnabled

                           ,NULL AS AvailabilityGroupName

                           ,NULL AS AvailabilityGroupListenerName

                           ,CASE is_current_owner 

                                  WHEN 1 THEN 'PRIMARY'

                                  ELSE 'SECONDARY'

                           END AS HighAvailabilityRoleDesc

                           ,'FCI' AS SQLServerClusteringMethod

                           ,@@VERSION AS SQLServerVersion  

                           FROM sys.dm_os_cluster_nodes

                           ORDER BY NodeName;  

                     END

                     ELSE

                     BEGIN

                           SELECT UPPER(CAST(SERVERPROPERTY('ServerName') AS VARCHAR)) AS InstanceName  

                           ,UPPER(CAST(SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS VARCHAR)) AS ServerName

                           ,SERVERPROPERTY('IsClustered') AS IsClustered

                           ,SERVERPROPERTY ('IsHadrEnabled') AS IsAvailabilityGroupEnabled

                           ,NULL AS AvailabilityGroupName

                           ,NULL AS AvailabilityGroupListenerName

                           ,NULL AS HighAvailabilityRoleDesc

                           ,'Standalone' AS SQLServerClusteringMethod

                           ,@@VERSION AS SQLServerVersion;  

                     END

              END

       END

END;

Update: You can now download the latest version from github.