Was this page helpful?
Your feedback about this content is important. Let us know what you think.
Additional feedback?
1500 characters remaining
Export (0) Print
Expand All

sys.dm_hadr_cluster_members (Transact-SQL)

 

Applies To: SQL Server 2014, SQL Server 2016 Preview

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?

Applies to: SQL Server (SQL Server 2012 through current version), Azure SQL Database.

System_CAPS_tipTip

Beginning in SQL Server 2014, this dynamic management view supports AlwaysOn Failover Cluster Instances in addition to AlwaysOn 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:

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.

Requires VIEW SERVER STATE permission on the server.

Community Additions

ADD
Show:
© 2015 Microsoft