How to find SQL Server Cluster information

 How to find SQL Server Cluster information

In this post, I'll walk you through how to retrieve information about a SQL Server cluster.

SELECT SERVERPROPERTY('IsClustered'), SERVERPROPERTY('ComputerNamePhysicalNetBIOS'),
       SERVERPROPERTY('MachineName'), SERVERPROPERTY('InstanceName'), SERVERPROPERTY('ServerName')
;

WITH ClusterActiveNode AS (
    SELECT SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS NodeName, CAST('Active' AS VARCHAR(10)) AS Active
),
ClusterNodes AS (
    SELECT NodeName FROM sys.dm_os_cluster_nodes
)
SELECT b.nodename, ISNULL(active, 'Passive') AS NodeStatus
FROM ClusterNodes AS b
LEFT JOIN ClusterActiveNode AS a ON a.NodeName = b.nodename;

Machine Name: This is the name of the Windows computer where the server instance is running. If the instance is clustered, it returns the name of the virtual server for a SQL Server instance running on a Microsoft Cluster Service.
Instance Name: The name of the specific SQL Server instance the user is connected to.
Server Name: This provides both the Windows server and instance information related to a specified SQL Server instance.

The following query displays the location of the SQL Server failover cluster diagnostic log (SQL Server Error Log). This information is valuable for troubleshooting and also reveals the locations of other error and diagnostic logs.

SELECT is_enabled, [path], max_size, max_files
FROM sys.dm_os_server_diagnostics_log_configurations WITH (NOLOCK) OPTION (RECOMPILE);

To find information about your storage, you can use the query below:

/* Using In-Built Function to Identify Nodes of SQL Server Failover Cluster */
SELECT * FROM fn_virtualservernodes();
GO
EXEC XP_FIXEDDRIVES;

/* Using DMV to Identify Nodes of SQL Server Failover Cluster */
SELECT * FROM sys.dm_os_cluster_nodes;
GO
SELECT * FROM fn_servershareddrives();

/* Using DMV Function to Identify Shared Drives Used by SQL Server Failover Cluster Instance */
SELECT * FROM sys.dm_io_cluster_shared_drives;

EXEC master.dbo.xp_readerrorlog 1, 1, N'NETBIOS', NULL, NULL, NULL, N'desc';