| title | sys.dm_hadr_cluster_members (Transact-SQL) | ||||
|---|---|---|---|---|---|
| description | Returns a row for each of the members that constitute the WSFC quorum, and the state of each of them. | ||||
| author | rwestMSFT | ||||
| ms.author | randolphwest | ||||
| ms.date | 10/17/2023 | ||||
| ms.service | sql | ||||
| ms.subservice | system-objects | ||||
| ms.topic | reference | ||||
| ms.custom |
|
||||
| f1_keywords |
|
||||
| helpviewer_keywords |
|
||||
| dev_langs |
|
||||
| monikerRange | =azuresqldb-current || >=sql-server-2016 || >=sql-server-linux-2017 || =fabric-sqldb |
[!INCLUDE SQL Server Azure SQL Database-fabricsqldb]
If the WSFC node that hosts a local instance of [!INCLUDE ssNoVersion] that is enabled for [!INCLUDE ssHADR] 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 [!INCLUDE ssHADR] 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 [!INCLUDE ssSQL14], 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. |
[!INCLUDE dmv-cluster-column-display]
For [!INCLUDE sssql19-md] and previous versions, requires VIEW SERVER STATE permission on the server.
For [!INCLUDE sssql22-md] and later versions, requires VIEW SERVER PERFORMANCE STATE permission on the server.