Failover of of SQL Server Distributed Availability Group

Question

Following the steps in Manual Failover of SQL Server DAG

To verify if both Availability Groups are ready for failover, use the T-SQL query below, running in SQLCMD Mode:

:CONNECT TDPRD071 
SELECT ag.name, drs.database_id, db_name(drs.database_id) as database_name, 
 drs.group_id, drs.replica_id, drs.last_hardened_lsn 
FROM sys.dm_hadr_database_replica_states drs 
INNER JOIN sys.availability_groups ag ON drs.group_id = ag.group_id;
:CONNECT TDDR071 
SELECT ag.name, drs.database_id, db_name(drs.database_id) as database_name, 
 drs.group_id, drs.replica_id, drs.last_hardened_lsn 
FROM sys.dm_hadr_database_replica_states drs 
INNER JOIN sys.availability_groups ag ON drs.group_id = ag.group_id;

The screenshot of the results of running the above queries from the global primary, as shown in the article, shows connection to each AG in the DAG.

However, when I run the queries on the global primary of my DAG, the results I get are only from connection to the Forwarder. Hence the two sets of results displayed are from connection to AG02 only.

If however, I run the two queries individually (both from the global primary) they connect correctly to AG01 or AG02 as the case may be.

Any suggestions as to why I may be getting this behaviour please?

thank you.

Answer 1

If however, I run the two queries individually (both from the global primary) they connect correctly to AG01 or AG02 as the case may be.

You're using SQLCMD mode for the queries in SSMS, you'll need a GO between the different servers' queries as below.

:CONNECT TDPRD071 
SELECT ag.name, drs.database_id, db_name(drs.database_id) as database_name, drs.group_id, drs.replica_id, drs.last_hardened_lsn FROM sys.dm_hadr_database_replica_states drs INNER JOIN sys.availability_groups ag ON drs.group_id = ag.group_id;
GO
:CONNECT TDDR071 
SELECT ag.name, drs.database_id, db_name(drs.database_id) as database_name, drs.group_id, drs.replica_id, drs.last_hardened_lsn FROM sys.dm_hadr_database_replica_states drs INNER JOIN sys.availability_groups ag ON drs.group_id = ag.group_id;
GO

• PTL_SQL: you are absolutely correct, the problem is solved, thank you ever so much