sys.dm_hadr_cluster_members (Transact-SQL)
If the WSFC node that hosts a local instance of SQL Server that is enabled for AlwaysOn 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 that is enabled for AlwaysOn 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?
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 |
member_type_desc |
nvarchar(50) |
Description of member_type, one of: CLUSTER_NODE DISK_WITNESS FILE_SHARE_WITNESS |
member_state |
tinyint |
The member state, one of: 0 = Offline 1 = Online |
member_state_desc |
nvarchar(60) |
Description of member_state, one of: OFFLINE ONLINE |
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. |
Permissions
Requires VIEW SERVER STATE permission on the server.
See Also
Concepts
Monitor Availability Groups (Transact-SQL)
AlwaysOn Availability Groups (SQL Server)
Other Resources
AlwaysOn Availability Groups Dynamic Management Views and Functions (Transact-SQL)