[AlwaysOn2017] DMV and DMF-Sys.dm_hadr_cluster_members of AlwaysOn
15 、 Sys.dm_hadr_cluster_members
If the WSFC node hosting a local instance of an SQL Server-enabled AlwaysOn availability group has a SQL quorum, a row is returned for each member and the status of each member that makes up the quorum. This includes all nodes in the cluster (returning the Clusterenum function with the CLUSTER_ENUM_NODE type) and the disk or file share witness, if any. The row returned for a given member contains information about the member's status. For example, when a five-node cluster with a majority of nodes quorum Sys.dm_hadr_cluster_members is enabled, the server instance from querying that the AlwaysOn availability group resides on the node with quorum Sys.dm_hadr_cluster_members reflects the state of the "NODE_DOWN" closed node.
If the WSFC node does not have a quorum, no rows are returned.
Use this dynamic management view to answer the following questions:
Which nodes are currently running on the WSFC cluster?
How many failures can a WSFC cluster tolerate before it loses arbitration in the case of a majority of nodes?
Prompt
From SQL Server 2014 (12.x), this dynamic management view supports Alwayson failover clustering instances in addition to AlwaysOn availability groups.
Column name
Data type
Description
Member_name
Nvarchar (128)
The member name, which can be a computer name, drive letter, or file share path.
Member_type
Member_type_desc
Tinyint
Nvarchar (50)
The type of member, which can be one of the following values:
0 = CLUSTER_NODE WSFC node
1 = DISK_WITNESS disk witness
2 = FILE_SHARE_WITNESS file sharing witness
3 = CLOUD_WITNESS Cloud Witness
Member_state
Member_state_desc
Tinyint
Nvarchar (60)
Member status, which can be one of the following values:
0 = DOWN offline
1 = UP online
Number_of_quorum_votes
Tinyint
The number of arbitration votes owned by this arbitration member. For no majority: disk is only supported by arbitration, this value defaults to 0. For other quorum types, this value defaults to 1.
