Applies to: SQL Server (all supported versions)
Returns a row for each local replica and a row for each remote replica in the same Always On availability group as a local replica. Each row contains information about the state of a given replica.
To obtain information about every replica in a given availability group, query sys.dm_hadr_availability_replica_states on the server instance that is hosting the primary replica. When queried on a server instance that is hosting a secondary replica of an availability group, this dynamic management view returns only local information for the availability group.
|Column name||Data type||Description|
|replica_id||uniqueidentifier||Unique identifier of the replica.|
|group_id||uniqueidentifier||Unique identifier of the availability group.|
|is_local||bit||Whether the replica is local, one of:
0 = Indicates a remote secondary replica in an availability group whose primary replica is hosted by the local server instance. This value occurs only on the primary replica location.
1 = Indicates a local replica. On secondary replicas, this is the only available value for the availability group to which the replica belongs.
|role||tinyint||Current Always On availability groups role of a local replica or a connected remote replica, one of:
0 = Resolving
1 = Primary
2 = Secondary
For information about Always On availability groups roles, see Overview of Always On Availability Groups (SQL Server).
|role_desc||nvarchar(60)||Description of role, one of:
|operational_state||tinyint||Current operational state of the replica, one of:
0 = Pending failover
1 = Pending
2 = Online
3 = Offline
4 = Failed
5 = Failed, no quorum
NULL = Replica is not local.
For more information, see Roles and Operational States, later in this topic.
|operational_state_desc||nvarchar(60)||Description of operational_state, one of:
|recovery_health||tinyint||Rollup of the database_state column of the sys.dm_hadr_database_replica_states dynamic management view. The following are the possible values and their descriptions.
0 : In progress. At least one joined database has a database state other than ONLINE (database_state is not 0).
1 : Online. All the joined databases have a database state of ONLINE (database_state is 0).
NULL : is_local = 0
|recovery_health_desc||nvarchar(60)||Description of recovery_health, one of:
|synchronization_health||tinyint||Reflects a rollup of the database synchronization state (synchronization_state)of all joined availability databases (also known as replicas) and the availability mode of the replica (synchronous-commit or asynchronous-commit mode). The rollup will reflect the least healthy accumulated state the databases on the replica. Below are the possible values and their descriptions.
0 : Not healthy. At least one joined database is in the NOT SYNCHRONIZING state.
1 : Partially healthy. Some replicas are not in the target synchronization state: synchronous-commit replicas should be synchronized, and asynchronous-commit replicas should be synchronizing.
2 : Healthy. All replicas are in the target synchronization state: synchronous-commit replicas are synchronized, and asynchronous-commit replicas are synchronizing.
|synchronization_health_desc||nvarchar(60)||Description of synchronization_health, one of:
|connected_state||tinyint||Whether a secondary replica is currently connected to the primary replica. The possible values are shown below with their descriptions.
0 : Disconnected. The response of an availability replica to the DISCONNECTED state depends on its role: On the primary replica, if a secondary replica is disconnected, its secondary databases are marked as NOT SYNCHRONIZED on the primary replica, which waits for the secondary to reconnect; On a secondary replica, upon detecting that it is disconnected, the secondary replica attempts to reconnect to the primary replica.
1 : Connected.
Each primary replica tracks the connection state for every secondary replica in the same availability group. Secondary replicas track the connection state of only the primary replica.
|connected_state_desc||nvarchar(60)||Description of connection_state, one of:
|last_connect_error_number||int||Number of the last connection error.|
|last_connect_error_description||nvarchar(1024)||Text of the last_connect_error_number message.|
|last_connect_error_timestamp||datetime||Date and time timestamp indicating when the last_connect_error_number error occurred.|
Roles and Operational States
The role, role, reflects the state of a given availability replica and the operational state, operational_state, describes whether the replica is ready to process client requests for all the database of the availability replica. The following is a summary of the operational states that are possible for each role: RESOLVING, PRIMARY, and SECONDARY.
RESOLVING: When an availability replica is in the RESOLVING role, the possible operational states are as shown in the following table.
|PENDING_FAILOVER||A failover command is being processed for the availability group.|
|OFFLINE||All configuration data for the availability replica has been updated on WSFC cluster and, also, in local metadata, but the availability group currently lacks a primary replica.|
|FAILED||A read failure has occurred during an attempt trying to retrieve information from the WSFC cluster.|
|FAILED_NO_QUORUM||The local WSFC node does not have quorum. This is an inferred state.|
PRIMARY: When an availability replica is performing the PRIMARY role, it is currently the primary replica. The possible operational states are as shown in the following table.
|PENDING||This is a transient state, but a primary replica can be stuck in this state if workers are not available to process requests.|
|ONLINE||The availability group resource is online, and all database worker threads have been picked up.|
|FAILED||The availability replica is unable to read to and/or write from the WSFC cluster.|
SECONDARY: When an availability replica is performing the SECONDARY role, it is currently a secondary replica. The possible operational states are as shown in the table below.
|ONLINE||The local secondary replica is connected to the primary replica.|
|FAILED||The local secondary replica is unable to read to and/or write from the WSFC cluster.|
|NULL||On a primary replica, this value is returned when the row relates to a secondary replica.|
Requires VIEW SERVER STATE permission on the server.