Updated: June 10, 2016
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 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?
Beginning in SQL Server 2014, this dynamic management view supports Always On Failover Cluster Instances in addition to Always On Availability Groups.
|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:|
|member_state||tinyint||The member state, one of:|
0 = Offline
1 = Online
|member_state_desc||nvarchar(60)||Description of member_state, one of:|
|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.|
Requires VIEW SERVER STATE permission on the server.
Always On Availability Groups Dynamic Management Views and Functions (Transact-SQL)
Always On Availability Groups Catalog Views (Transact-SQL)
Monitor Availability Groups (Transact-SQL)
AlwaysOn Availability Groups (SQL Server)