All we need is an easy explanation of the problem, so here it is.
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
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
- Configure an Always On distributed availability group
- An overview of distributed SQL Server Always On Availability Groups by Rajendra Gupta
- 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.
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
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂