Data Loss warning when trying to do failover even though Synchronized on SQL Server 2019

Question

Data Loss warning when trying to do failover even though Synchronized on SQL Server 2019. This is a basic availability group running in a cluster-less installation.

In the dashboard I see this:

This is the error I get:

I also checked sys.dm_hadr_database_replica_cluster_states and I see that both show is_failover_ready 1.

Answer 1

What you have here is a Read-Scale AG, and the behavior you're seeing in the failover wizard is by design. This solution is for offloading read-only workloads or for disaster recovery scenarios only, not high availability due to there not being an underlying cluster. The DMVs you're using are for internal SQL Server use only and MS indicates that you should ignore the result set from those with read-scale AGs. There are some great blog posts out there by Edwin Sarmiento, and in MS documentation outlining this. Will link these below for your reference:

• https://www.mssqltips.com/sqlservertip/6905/sql-server-read-scale-always-on-availability-groups/
• https://www.mssqltips.com/sqlservertip/6925/read-scale-always-on-availability-group-to-offloading-reporting/
• https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/read-scale-availability-groups?view=sql-server-ver15

Here are explicit steps according to Microsoft Documentation walking you through a manual failover of a Read-Scale AG (You're going to want to scroll down to the section that says "Fail over the primary replica on a read-scale availability group"):

• https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/perform-a-planned-manual-failover-of-an-availability-group-sql-server?view=sql-server-ver15#ReadScaleOutOnly

Answer 2

I don't have a definitive answer on whether the error can be ignored but I can confirm on the same SQL Version 2019, Read-Scale (clusterless) AG, I'm getting the same result.

Synchonized state, no data loss on the dashboard, is_failover_ready = 1 in the sys.dm_hadr_database_replica_cluster_states.
But still the error/warning during the failover wizard.
Sorry I can't confirm more than that.