How to determine the global primary instance in a SQL Server distributed availability group using T-SQL?

Question

I have a SQL Server distributed availability group set up, and I'm currently connected to the forwarder instance. I would like to know how to use T-SQL to query the appropriate DMVs or catalog views to determine which instance is currently serving as the global primary in the distributed availability group.

I'm looking for a T-SQL script or query that I can execute on the forwarder instance to retrieve this information.

Any help or guidance on this topic would be greatly appreciated. Thank you in advance!

Answer 1

Basically the forwarder should not have any knowledge of the AG on the other side other than the fact that the AG is part of the Distributed AG. The Distributed AG holds the endpoint_url of the listener plus the mirroring endpoint port number, this way it should always point to the primary replica's mirroring endpoint.

The below T-SQL should work, it practically wasn't tested and there may be some odd edge cases.

SELECT 
    distar.replica_server_name AS [RemoteAGName],
    LEFT
    (
        SUBSTRING
        (
            distar.[endpoint_url],
            7 /* start after TCP:// */,
            LEN(distar.[endpoint_url]) - 6
        ),
        CHARINDEX
        (
            ':',
            distar.[endpoint_url], 
            5 /* Start after : in TCP: */
        ) - 7 /* Don't include the : */
    ) AS [ListenerName]
FROM sys.availability_groups dist
    INNER JOIN sys.availability_replicas distar
        ON dist.group_id = distar.group_id
    LEFT JOIN sys.availability_groups ag
        ON ag.name = distar.replica_server_name
WHERE dist.is_distributed = 1
    AND dist.name = 'DistributedAGName'
    AND ag.group_id IS NULL;