Scenario

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.

How will you find whether the SQL Server is configured in a failover cluster?

Using TSQL query

-- =================================================================
-- FAQ: How to check SQL Server environment? (Cluster or nonclustered)
-- =================================================================
-- Query

select SERVERPROPERTY ('iscluster')

-- Result
-- 0 - For standalone enviroment
-- 1 - For Cluster enviroment

Result (This returns a 0=NO or 1=YES)

How will you find the node that the SQL Server is currently running on?

Using TSQL query

SELECT SERVERPROPERTY('ComputerNamePhysicalNetBIOS')

Result

Find cluster nodes by querying a DMV

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

SELECT * FROM sys.dm_os_cluster_nodes

Result

Find information from SERVERPROPERTY

We can also find Machine Name, Server Name & Instance Name by below SERVERPROPERTY

Using TSQL statement

SELECT SERVERPROPERTY ('MachineName') as [Machine Name]
SELECT SERVERPROPERTY('InstanceName') as [Instance Name]
SELECT SERVERPROPERTY('ServerName') as [Server Name]

Result

Find the SQL cluster details & status all together!

Using TSQL query

-- =====================================================
-- 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.