A cluster includes two or more physical servers, called nodes; identical configuration is recommended. One is identified as the active node, on which a SQL Server instance is running the production workload, and the other is a passive node, on which SQL Server is installed but not running. In this post, we have tried to share a few ways to gather information about any running SQL cluster setup.
Using TSQL query
1 |
SELECT SERVERPROPERTY('IsClustered') |
Result (This returns a 0=NO or 1=YES)
Using TSQL query
1 |
SELECT SERVERPROPERTY('ComputerNamePhysicalNetBIOS') |
Result
We can also find cluster nodes by querying a data management view (DMV), but this DMV doesn’t show which nodes are active.
Using DMV
1 |
SELECT * FROM sys.dm_os_cluster_nodes |
Result
We can also find Machine Name, Server Name & Instance Name by below SERVERPROPERTY
Using TSQL statement
1 2 3 |
SELECT SERVERPROPERTY ('MachineName') as [Machine Name] SELECT SERVERPROPERTY('InstanceName') as [Instance Name] SELECT SERVERPROPERTY('ServerName') as [Server Name] |
Result
Using TSQL query
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
-- ===================================================== -- Owner: Soyeluddin Biswas == Date: 08/11/2019 -- Email: soyel@ourtechideas.com & st.biswas99@gmail.com -- ===================================================== use master go select @@SERVERNAME as [SQL Instance] ,NodeName as [Cluster Node] ,CASE WHEN status=0 THEN 'RUNNING' WHEN status=1 THEN 'Down' WHEN status=2 THEN 'Paused' WHEN status=3 THEN 'Joining' WHEN status=-1 THEN 'Unknown' END as [Node Status] ,CASE WHEN is_current_owner=0 THEN 'No' WHEN is_current_owner=1 THEN 'Yes' END as [Current Owner] from sys.dm_os_cluster_nodes |
Result
Using Failover Cluster Manager GUI
Open Failover Cluster Manager and click on Roles. Then go to the bottom of the window and click on Resources. You will get the same cluster information as described in the below screenshot.
when you click on nodes, you will see all the nodes participated in the cluster.
DMV ‘sys.dm_exec_requests’ provides details on all of the processes running in SQL Server.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
SELECT qs.Session_ID, Blocking_Session_ID, qs.Status, Wait_Type, Wait_Time, Wait_Resource, SUBSTRING(st.text, (qs.statement_start_offset/2)+1, ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(st.text) ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) + 1) AS statement_text, GetDate() SnapshotDateTime, --Open_Transaction_Count, ss.PROGRAM_NAME, ss.HOST_NAME, ss.Login_Name FROM sys.dm_exec_requests AS qs INNER JOIN sys.dm_exec_sessions ss ON qs.session_id = ss.session_id CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st WHERE Wait_Time > 0 ORDER BY Wait_Time DESC |
“Please let us know if there…