1

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.

3
  • 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*]. Jan 7 at 19:17
  • Done, thank you. Jan 10 at 14:49
  • 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. Feb 4 at 19:23

1 Answer 1

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

3
  • You should include the salient details from the link directly to your answer, lest it becomes useless when the link goes stale.
    – mustaccio
    Mar 16 at 11:48
  • (I will address in subsequent post - very lengthy) Mar 16 at 15:55
  • Added salient details from the links Jun 30 at 15:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.