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

Paing Hein Kyaw 40 Reputation points
2024-04-01T16:08:31.54+00:00

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.

User's image

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,690 questions
{count} votes

2 answers

Sort by: Most helpful
  1. hossein jalilian 2,370 Reputation points
    2024-04-01T19:50:23.95+00:00

    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


  2. LucyChen-MSFT 885 Reputation points Microsoft Vendor
    2024-04-02T05:40:57.14+00:00

    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

    0 comments No comments