How to solve secondary availability group is an unhealthy status in SQL server distributed availability group?

Question

Hello,

I would like to help one thing that is my SQL server issue. When I create distributed availability group, secondary available group is show an unhealthy status in SQL server Clustering. Please see the below photo. I already alter in secondary AG with SQL query scripts. Could you please help me how to solve it? Thank You.

Answer 1

Hi @Paing Hein Kyaw,

Thanks for your information.

1. You can change the secondary replica to synchronous-commit availability mode.
2. This article tells us the factors that disrupt data synchronization, hope it can help you understand well. Availability modes for an availability group - SQL Server Always On | Microsoft Learn.

Best regards,

Lucy Chen

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

https://docs.microsoft.com/en-us/answers/support/email-notifications

Answer 2

Hello Paing,

Thanks for posting your question in the Microsoft Q&A forum.

1. Ensure that the availability mode for the secondary AG is set to "SYNCHRONOUS_COMMIT". This is required for the secondary AG to be in a healthy state within the DAG.
2. You can check and update the availability mode using the following T-SQL command:

ALTER AVAILABILITY GROUP [Distributed_AG_Name] MODIFY REPLICA ON'Secondary_AG_Name' WITH (AVAILABILITY_MODE = SYNCHRONOUS_COMMIT);

• Verify the data replication status using the following T-SQL command:

SELECT 
    ag.name AS 'Availability Group',
    rs.role_desc AS 'Role',
    rs.synchronization_health_desc AS 'Synchronization Health' 
FROM sys.dm_hadr_availability_replica_states rs 
JOIN sys.availability_groups ag 
   ON rs.group_id = ag.group_id 
WHERE ag.name = 'Distributed_AG_Name';

---

Please don't forget to close up the thread here by upvoting and accept it as an answer if it is helpful