sys.dm_hadr_cluster_members (Transact-SQL)
Applies to: 
SQL Server
Azure SQL Database
If the WSFC node that hosts a local instance of SQL Server that is enabled for Always On availability groups has WSFC quorum, returns a row for each of the members that constitute the quorum and the state of each of them. This includes of all nodes in the cluster (returned with CLUSTER_ENUM_NODE type by the Clusterenum function) and the disk or file-share witness, if any. The row returned for a given member contains information about the state of that member. For example, for a five node cluster with majority node quorum in which one node is down, when sys.dm_hadr_cluster_members is queried from a server instance that is enabled for Always On availability groups that resides on a node with quorum, sys.dm_hadr_cluster_members reflects the state of the down node as NODE_DOWN.
If the WSFC node has no quorum, no rows are returned.
Use this dynamic management view to answer the following questions:
What nodes are currently running on the WSFC cluster?
How many more failures can the WSFC cluster tolerate before losing quorum in a majority-node case?
Tip
Beginning in SQL Server 2014 (12.x), this dynamic management view supports Always On failover cluster instances (FCIs) in addition to availability groups (AGs).
| Column name | Data type | Description |
|---|---|---|
member_name |
nvarchar(128) | Member name, which can be a computer name, a drive letter, or a file share path. |
member_type |
tinyint | The type of member, one of: 0 = WSFC node 1 = Disk witness 2 = File share witness 3 = Cloud Witness |
member_type_desc |
nvarchar(50) | Description of member_type, one of:CLUSTER_NODE DISK_WITNESS FILE_SHARE_WITNESS CLOUD_WITNESS |
member_state |
tinyint | The member state, one of: 0 = Offline 1 = Online |
member_state_desc |
nvarchar(60) | Description of member_state, one of:UP DOWN |
number_of_quorum_votes |
tinyint | Number of quorum votes possessed by this quorum member. For No Majority: Disk Only quorums, this value defaults to 0. For other quorum types, this value defaults to 1. |
Remarks
In a Windows Server Failover Cluster (WSFC), the cluster columns display the Windows cluster details. In cases where there's no Windows cluster, such as read-scale availability groups, or availability groups on Linux, columns related to the cluster might display data about an internal default cluster. These columns are for internal use only and can be disregarded.
Permissions
For SQL Server 2019 (15.x) and previous versions, requires VIEW SERVER STATE permission on the server.
For SQL Server 2022 (16.x) and later versions, requires VIEW SERVER PERFORMANCE STATE permission on the server.