Events
Mar 31, 11 PM - Apr 2, 11 PM
The biggest SQL, Fabric and Power BI learning event. March 31 – April 2. Use code FABINSIDER to save $400.
Register todayThis browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
Applies to:
SQL Server
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.
Important
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: RESOLVING PRIMARY SECONDARY |
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: PENDING_FAILOVER PENDING ONLINE OFFLINE FAILED FAILED_NO_QUORUM NULL |
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: ONLINE_IN_PROGRESS ONLINE NULL |
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: NOT_HEALTHY PARTIALLY_HEALTHY HEALTHY |
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: DISCONNECTED CONNECTED |
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. |
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.
Operational State | Description |
---|---|
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.
Operational State | Description |
---|---|
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.
Operational State | Description |
---|---|
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.
Requires VIEW SERVER PERFORMANCE STATE permission on the server.
Overview of Always On Availability Groups (SQL Server)
Monitor Availability Groups (Transact-SQL)
Events
Mar 31, 11 PM - Apr 2, 11 PM
The biggest SQL, Fabric and Power BI learning event. March 31 – April 2. Use code FABINSIDER to save $400.
Register today