SQL Server Distributed Availability Group databases not syncing after a Global Primary Replica Failover

Question

SQL Server Distributed Availability Group Environment

AG1 => Node1 (AG1-Global Primary) and Node2 (AG1 - Secondary Replica)

AG2 => Node3 (AG2-Forwarder) and Node4 (AG2 - Secondary Replica)

DAGE => Distributed Availability Group Environment

DAGE Availability Replicas => AG1 & AG2

If I failover the Global Primary Role from (AG1) Node1 to (AG1) Node2, the (DAGE) Distributed Availability Group Replica (AG1) stops synchronizing with the Forwarder (AG2) Replica. (Why?)

When I failback the Global Primary Role from (AG1) Node2 back to (AG1) Node1, the (DAGE) Distributed Availability Group Replica (AG1) starts / resumes synchronizing with the Forwarder (AG2) Replica.

• Mark Stewart: Can you please edit your question to add the [sql-server] tag in addition to your original tag [distributed-availability-groups, and the appropriate version tag, such as [sql-server*-2005*].
• Sean Gallardy: You'd have to dump out the entire configuration, including listeners, mirroring endpoint, etc., which is a lot to ask for rando's in a forum.

Answer 1

Turns out the above behavior is normal

• Following a failover to either secondary node on the Primary AG (AG1) or the Primary AG of the secondary AG (AG2)- (The Forwarder), the Listener URL ends up pointing to the wrong Primary Availability Group Listener Endpoint URL.
• Reason: After a failover, the Listener IP goes offline on the old Primary and comes online on the new Primary (old Secondary).
• If you noted during the Distributed Availability Group creation process, the Listeners from each Availability Group (AG1 & AG2) make up the Distributed Availability Group Object.
• Unfortunately this information switch is not updated following the failover and the Listener URL is not updated. Thus it remains pointing to an off-lined IP Address.

(The SQL query below displays the Off-Lined Listener URL and Status)

• Run this to determine which URL requires updating (DISCONNECTED & NOT_HEALTHY)

SELECT r.replica_server_name, r.endpoint_url,
rs.connected_state_desc, rs.role_desc, rs.operational_state_desc,
rs.recovery_health_desc,rs.synchronization_health_desc,
r.availability_mode_desc, r.failover_mode_desc
FROM sys.dm_hadr_availability_replica_states rs
INNER JOIN sys.availability_replicas r
ON rs.replica_id=r.replica_id
ORDER BY r.replica_server_name

AFTER FAILOVER & DDL TO FIX THE ISSUE

Run this (in the SQLCMD Mode) on the Global PRIMARY REPLICA of the Primary Availability Group 1

`Availability Group (AG)

`DistAG_NAME - Distributed Availability Group Name

AG1_GlobalPrimaryReplicaServer - Primary Replica Server on AG 1

AG2_GlobalPrimaryReplicaServer - Primary Replica Server on AG 2

AG1_Instance1 - Global Primary Replica SQL Server Instance on AG1

AG2_Instance1 - Global Primary Replica SQL Server Instance on AG2

:CONNECT **AG1_GlobalPrimaryReplicaServer\Instance**
USE [master]
GO
ALTER AVAILABILITY GROUP [DistAG_NAME] 
MODIFY AVAILABILITY GROUP ON 
 'AG1' WITH 
 ( 
 LISTENER_URL = 'TCP://**AG2_PrimaryReplicaServer**.TESTDOMAIN.COM:PORT'
 )

Run this (in the SQLCMD Mode) on the PRIMARY REPLICA of the Primary Availability Group 2

--Run this on the primary replica of the secondary Availability Group

:CONNECT **AG2_GlobalPrimaryReplicaServer\Instance**
USE [master]
GO
ALTER AVAILABILITY GROUP [DistAG_NAME] 
MODIFY AVAILABILITY GROUP ON 
 '**AG2**' WITH 
 ( 
 LISTENER_URL = 'TCP://**AG2_PrimaryReplicaServer**.TESTDOMAIN.COM:PORT'
 )

To address the issue, Microsoft provided me with this link: Update Distributed Availability Group Endpoint URL, and I found an additional reference, which gives the background details of the setup process: Setup Distributed Availability Groups

• mustaccio: You should include the salient details from the link directly to your answer, lest it becomes useless when the link goes stale.
• HvyOnEz Solid-Ground: (I will address in subsequent post - very lengthy)
• HvyOnEz Solid-Ground: Added salient details from the links