How to Identify the Most Up-to-Date Secondary Replica in a SQL Server Always On Availability Group when the primary replica is unavailable?

Question

In a SQL Server Always On Availability Groups setup, I'm seeking guidance on determining the most up-to-date secondary replica when the primary is unavailable, to perform a failover to minimize data loss.

Assume a situation where the primary replica is down. The objective is to identify the secondary replica with the most up-to-date data in the absence of real-time communication with the primary. For that, I can't find any reliable query or metric in the SQL server.

SELECT replica_id, redo_queue_size FROM sys.dm_hadr_database_replica_states;

Is the redo_queue size a reliable indicator in scenarios where the primary replica is unavailable?

Are there alternative methods or queries to assess the data synchronization state among secondary replicas during a primary replica outage?

What considerations or best practices should be taken into account when interpreting redo_queue size or other metrics for failover decision? such as

last_hardened_lsn, end_of_log_lsn, last_commit_lsn, last_hardened_time from sys.dm_hadr_database_replica_states

In PostgreSQL, you can use the Write-Ahead Logging (WAL) position to determine the synchronization status and identify which secondary replica has the most up-to-date data. The WAL position serves as a reliable indicator of the replication progress in PostgreSQL. Is there any similar query available in MS SQL Server?

Additionally, I'm curious about how Windows Server Failover Clustering (WSFC) or Pacemaker (a cluster resource manager for Linux) decides which replica should be promoted to the primary role when the primary replica is unavailable.

I appreciate any insights, experiences, or recommendations from the community. Thank you!

Answer 1

Assume a situation where the primary replica is down. The objective is to identify the secondary replica with the most up-to-date data in the absence of real-time communication with the primary. For that, I can't find any reliable query or metric in the SQL server.

You'll want to use last_hardened_lsn from sys.dm_hadr_database_replica_states to identify which database is the furthest ahead. the larger the number, the more up to date it is.

Is the redo_queue size a reliable indicator in scenarios where the primary replica is unavailable?

The redo queue is how much needs to be redone, this doesn't tell you how close or far away from any other replica it is.

Are there alternative methods or queries to assess the data synchronization state among secondary replicas during a primary replica outage?

You can check sys.dm_hadr_availability_replica_states on the primary, or if the primary is truly down and there way no automatic failover, then use the previously described method to find a suitable new primary replica to fail to, assuming the primary is considered dead and unsalvageable. Otherwise, do your best to bring the original primary up.

What considerations or best practices should be taken into account when interpreting redo_queue size or other metrics for failover decision?

The only time I'd ever look at the redo queue size in terms of failover is if there were multiple replicas that were all at the same hardened point but differed in redo queue. Then I'd pick the replica with the lowest redo queue in order to get back up the quickest. The redo queue will need to be... redone... before the database replica can come online.

Additionally, I'm curious about how Windows Server Failover Clustering (WSFC) or Pacemaker (a cluster resource manager for Linux) decides which replica should be promoted to the primary role when the primary replica is unavailable.

This depends on a whole host of items but will only occur if there is a secondary replica that is marked as synchronous commit and is synchronized to the original primary. The clustering portions have no idea about the inner workings of SQL Server, so a combination of cluster settings (to attempt or otherwise mark a replica as a possible failover location) and SQL Server internal states (information about the current AG, replicas, etc.) are used. The clustering components may attempt a failover, but depending on what SQL Server believes to be true, may not accept the failover. Some of these items are described in this Learn article.