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/s3. 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/Secondary6. 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_ownerWHEN 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 SQLServerVersionFROM sys.availability_groups AS agLEFT OUTER JOIN sys.availability_replicas AS ar ON ag.group_id = ar.group_idLEFT OUTER JOIN sys.availability_group_listeners AS agl ON ag.group_id = agl.group_idLEFT OUTER JOIN sys.dm_hadr_availability_group_states as hags ON ag.group_id = hags.group_idORDER 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.
