Get Global Primary details from All the replicas in Distributed AlwaysOn

Question

In Distributed AlwaysOn, I could not find any DMVs in SQL Server which could provide the details about Global Primary from replicas in Forwarder side (both Primary Forwarder and its local replicas).

Is there a way to at-least pull the Global Primary replica (Read-Write) from forwarder side ?

Accepted Answer

Hi @Sajal Bagchi ,

Thank you for reaching out and welcome to Microsoft Q&A.

Is there a way to at-least pull the Global Primary replica from forwarder side?

Currently, we can only get the information in SQL Server Management Studio for a distributed availability group on the primary replica for the availability groups. The details of the distributed availability groups are in the DMV in SQL Server.

Please check out this official document about Distributed Always On, hope this can help you well.

Feel free to share your issue here if you have any confusions.

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

In a Distributed Availability Group (DAG) setup, identifying the Global Primary replica from the Forwarder side (both Primary Forwarder and its local replicas) requires a detailed understanding of how to query the system views and DMVs in SQL Server. While there is no direct DMV that explicitly provides the Global Primary information from the Forwarder side, you can piece together the necessary details using a combination of DMVs and system functions.

Here is a step-by-step approach to achieve this:

1. Query Availability Replicas and Groups

Start by identifying all replicas and their roles using the DMVs related to Availability Groups.


-- On Forwarder node

SELECT 

    ar.replica_server_name,

    ar.role_desc,

    ag.name AS availability_group_name,

    ar.availability_mode_desc,

    ar.failover_mode_desc

FROM 

    sys.availability_replicas ar

JOIN 

    sys.availability_groups ag ON ar.group_id = ag.group_id;

2. Identify Local Primary Replica

Determine the primary replica for the local Availability Group on the Forwarder side.


-- On Forwarder node

SELECT 

    ags.primary_replica,

    ag.name AS availability_group_name

FROM 

    sys.dm_hadr_availability_group_states ags

JOIN 

    sys.availability_groups ag ON ags.group_id = ag.group_id;

3. Query Remote Forwarders for Global Primary

To identify the Global Primary, you need to connect to the forwarders and run a query that will fetch the primary replica of the remote Availability Groups.

4. Combining Data for Global Primary

Here’s a consolidated script that can help you identify the Global Primary from the forwarder side by combining the above steps:


-- On Forwarder node

WITH LocalAG AS (

    SELECT 

        ags.group_id,

        ags.primary_replica AS local_primary_replica,

        ag.name AS availability_group_name

    FROM 

        sys.dm_hadr_availability_group_states ags

    JOIN 

        sys.availability_groups ag ON ags.group_id = ag.group_id

),

GlobalAG AS (

    SELECT 

        ar.replica_server_name AS global_primary_replica,

        ag.name AS availability_group_name

    FROM 

        sys.availability_replicas ar

    JOIN 

        sys.availability_groups ag ON ar.group_id = ag.group_id

    WHERE 

        ar.role_desc = 'PRIMARY'

)

SELECT 

    LAG.availability_group_name,

    LAG.local_primary_replica,

    GAG.global_primary_replica

FROM 

    LocalAG LAG

LEFT JOIN 

    GlobalAG GAG ON LAG.availability_group_name = GAG.availability_group_name;

5. Verify Global Primary

To ensure the Global Primary information is accurate, you can connect to each forwarder node and validate the primary replica using the above query. This approach consolidates data from local and global contexts to identify the Global Primary replica.

Automating the Process

You might consider automating this process using a SQL Server Agent job or a script that can run on a schedule, collect the required information, and send notifications or store the results in a central repository.

While there is no direct DMV to fetch the Global Primary replica from the Forwarder side in a Distributed AlwaysOn setup, you can use a combination of DMVs to derive this information. By querying sys.dm_hadr_availability_group_states, sys.availability_groups, and sys.availability_replicas, you can piece together the necessary details to identify the Global Primary replica. This approach requires running the queries on the forwarder nodes and correlating the data to get the complete picture.

Share via