Returns a row for each local availability replica and a row for each remote availability replica in the same AlwaysOn availability group as a local replica. Each row contains information about the state of a given availability replica.
To obtain information about every availability 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.
Applies to: SQL Server (SQL Server 2012 through current version).
Unique identifier of the availability replica.
Unique identifier of the availability group.
Whether the availability 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 availability replica. On secondary replicas, this is the only available value for the availability group to which the replica belongs.
Current AlwaysOn Availability Groups role of a local availability replica or a connected remote availability replica, one of:
0 = Resolving
1 = Primary
2 = Secondary
For information about AlwaysOn Availability Groups roles, see Overview of AlwaysOn Availability Groups (SQL Server).
Description of role, one of:
Current operational state of the availability 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.
Description of operational_state, one of:
Rollup of the database_state column of the sys.dm_hadr_database_replica_states dynamic management view, one of:
Description of recovery_health, one of:
Reflects a rollup of the database synchronization state (synchronization_state)of all joined availability databases (also known as "database replicas") and the availability mode of the availability replica (synchronous-commit or asynchronous-commit mode). The rollup will reflect the least healthy accumulated state the databases on the availability replica.
Description of synchronization_health, one of:
Whether a secondary replica is currently connected to the primary replica, one of:
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.
Description of connection_state, one of:
Number of the last connection error.
Text of the last_connect_error_number message.
Date and time timestamp indicating when the last_connect_error_number error occurred.
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 table summarizes the operational states that are possible for each role: RESOLVING, PRIMARY, and SECONDARY.
Meaning of Operational States for Each Role
When an availability replica is in the RESOLVING role, the possible operational states are as follows:
When an availability replica is performing the PRIMARY role, it is currently the primary replica. The possible operational states are as follows:
When an availability replica is performing the SECONDARY role, it is currently a secondary replica. The possible operational states are as follows: