SQL Monitor 6

State, commit mode, and failover information

Within the Availability replicas section of the Availability group overview, the State and Failover columns show:

  • the state, commit mode, and failover mode for all availability replicas
  • the state and failover information for all availability databases

Availability replicas

State, commit mode, and failover mode

The states in the table below are listed in order of severity, with the most severe first.

StateExplanation

Not joined

At least one database in the replica is not configured to be part of the availability group on this replica, but is joined on the other replicas.

Not connected

The secondary replica isn't connected to the primary replica.

Failed

A read failure has occurred during an attempt to retrieve information from the WSFC cluster.

Resolving

When, during a failover, the replica is not yet primary or secondary, it is said to be in the resolving state.

Not synchronizing*

Something is stopping the replica from synchronizing (asynchronous replicas only). For example, the availability group exists but the database is not yet restored on the secondary replica.

Synchronizing*

The availability replica is catching up with the primary replica.

Synchronized*

Everything’s up-to-date. If the commit mode is asynchronous, this only applies to primary replicas. If the commit mode is synchronous, this can apply to primary or secondary replicas.

*For these three states, the state shown for the replica reflects the state of whichever of its databases is in the most serious condition. For example, if a replica contains two databases whose states are Synchronizing and Not synchronizing, the replica shows Not synchronizing as its state.

 

Commit modeExplanation

Synchronous

In synchronous-commit mode, the primary replica doesn't commit any transactions until it receives acknowledgement that all synchronous secondary replicas have finished hardening the commit in their log. This means no data will be lost on failover, because the primary and secondary replica databases are synchronized.

However, the synchronous replication process causes a certain amount of transaction latency. This depends on factors such as the size of the rate of log growth, the send queue, and the rate at which log records are sent and received.

Asynchronous

In asynchronous-commit mode, the primary replica commits transactions without waiting for acknowledgement that the asynchronous-commit secondary replicas have hardened the log. This means there's no latency during the commit process.

However, there could be data loss on failover, depending on how far the secondary is behind the primary at the time of the failover.

PrimaryThe primary replica has "primary" in parentheses, instead of "synchronous" or "asynchronous".
 
Failover modeExplanation

Automatic

Automatic failover is initiated automatically in response to a failure. For automatic failover to work, the commit mode must be synchronous and the target replica must be synchronized.

Manual

Manual failover must be initiated by an administrator. For manual failover to work, the target replica must be synchronized.

Availability databases

State and failover information

The states in the table below are listed in order of severity, with the most severe first.

StateExplanation

Not joined

The database is not configured to be part of the availability group on this replica, but is joined on the other replicas.

Not synchronizing

Something is stopping the replica from synchronizing (asynchronous replicas only). For example, the availability group exists but the database is not yet restored on the secondary replica.

Synchronizing

The availability replica is catching up with the primary replica.

Synchronized

Everything’s up-to-date. If the commit mode is asynchronous, this only applies to primary replicas. If the commit mode is synchronous, this can apply to primary or secondary replicas.

Time behind

The amount of time the database would take to fail over.

Databases can also show the states Not connected, Failed, and Resolving. These are copies of their respective replica's state. For example, if a replica's state is Failed, all databases within the replica also show Failed as their states.

For more information on these, see the table of replica states above.

Failover informationExplanation
Number of LSNs at riskThe number of transaction log records that would be lost if there was a failover.
No data lossNo data would be lost if there was a failover.
Data lossData would be lost if there was a failover.

Do you have any feedback on this documentation?

Let us know at sqlmonitorfeedback@red-gate.com


Didn't find what you were looking for?