1

I have created a SQL Server Distributed Availability Group (AG), which spans two underlying AGs, each of them deployed on a Windows Cluster at a different location. I have also created a Listener for each "local" AG. My applications connect to the Primary AG using its listener. However, if this AG goes down and I need to failover to the Secondary AG at the other location (which has its own listener), the applications do not automatically reconnect to this second AG since it has a different listener. Is there a way to configure the apps so that they reconnect to the Secondary AG if the first one goes down and failed over?

Thank you. J

1

Reference:

  1. Configure an Always On distributed availability group
  2. An overview of distributed SQL Server Always On Availability Groups by Rajendra Gupta
  3. Setup and Implement SQL Server 2016 Always On Distributed Availability Groups by Edwin Sarmiento

Make sure the endpoints can communicate between the different availability groups in the distributed availability group. If one availability group is set to a specific network on the endpoint, the distributed availability group does not work properly. On each server that hosts a replica in the distributed availability group, set the listener to listen on all IP addresses (LISTENER_IP = ALL).

Then you create a distributed availability group with both listeners. Example:

CREATE AVAILABILITY GROUP [distributedag]  
   WITH (DISTRIBUTED)   
   AVAILABILITY GROUP ON  
      'ag1' WITH    
      (   
         LISTENER_URL = 'tcp://ag1-listener.contoso.com:5022',    
         AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,   
         FAILOVER_MODE = MANUAL,   
         SEEDING_MODE = AUTOMATIC   
      ),   
      'ag2' WITH    
      (   
         LISTENER_URL = 'tcp://ag2-listener.contoso.com:5022',   
         AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,   
         FAILOVER_MODE = MANUAL,   
         SEEDING_MODE = AUTOMATIC   
      );    
GO

It does not have a mechanism to support the automatic failover between multiple clusters. A distributed availability group supports only manual failover using the FORCE_FAILOVER_ALLOW_DATA_LOSS parameter

You cannot configure a listener for the distributed availability group. In this case, your application cannot redirect connections to another cluster (primary) automatically. It requires an explicit configuration in the application connection string

1
  • 2
    Or you can reconfigure the listener on the other AG after you force failover or drop the distributed availability group (if the main AG is unavailable). Aug 13 at 21:04

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.