SQL Server 2019 Distributed Availability Groups

Question

SQL Server 2019 Distributed Availability Groups

DK

SSC Enthusiast

Points: 176
More actions
September 1, 2021 at 12:03 am

#3925647
Hello, I am attempting to create a SQL Server Distributed Availability Group in my Azure EC3 environment. When I attempt to alter my Distributed AG on AG2 using the below command, I get an error as shown below the command.
```
ALTER AVAILABILITY GROUP [WOASQLDISTAG]JOINAVAILABILITY GROUP ON'WAOAG1Hfv96xWgv' WITH(LISTENER_URL = 'TCP://WAOAG121jAavqBc.WAOAG1.com:5022',AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,FAILOVER_MODE = MANUAL,SEEDING_MODE = MANUAL),'WAOAG2cJ22S7mQO' WITH(LISTENER_URL = 'TCP://WAOAG22peucJsSD.WAOAG1.com:5022',AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,FAILOVER_MODE = MANUAL,SEEDING_MODE = MANUAL);GO
```
Error:
Msg 19511, Level 16, State 0, Line 8
Cannot join distributed availability group 'WOASQLDISTAG'. The local availability group 'WAOAG2cJ22S7mQO' contains one or more databases. Remove all the databases or create an empty availability group to join a distributed availability group.
I use an AWS Launch Wizard to create this environment. There are 4 nodes, 2 for each AG.
Any ideas on how to overcome this error? I have searched the internet and have had no luck with existing suggestions.
Thank you,
David

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Ant-Green SSC Guru Points: 113507 More actions · Answer 1

The error message is quite clear on that, you have a database which is already part of the availability group, to setup DAG's all the groups need to be empty, then add the databases after the DAG has been formed.

Michael L John One Orange Chip Points: 27055 More actions · Answer 2

DK wrote:

Hello, I am attempting to create a SQL Server Distributed Availability Group in my Azure EC3 environment. When I attempt to alter my Distributed AG on AG2 using the below command, I get an error as shown below the command.
ALTER AVAILABILITY GROUP [WOASQLDISTAG]JOINAVAILABILITY GROUP ON'WAOAG1Hfv96xWgv' WITH(LISTENER_URL = 'TCP://WAOAG121jAavqBc.WAOAG1.com:5022',AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,FAILOVER_MODE = MANUAL,SEEDING_MODE = MANUAL),'WAOAG2cJ22S7mQO' WITH(LISTENER_URL = 'TCP://WAOAG22peucJsSD.WAOAG1.com:5022',AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,FAILOVER_MODE = MANUAL,SEEDING_MODE = MANUAL);GO
Error:
Msg 19511, Level 16, State 0, Line 8 Cannot join distributed availability group 'WOASQLDISTAG'. The local availability group 'WAOAG2cJ22S7mQO' contains one or more databases. Remove all the databases or create an empty availability group to join a distributed availability group.
I use an AWS Launch Wizard to create this environment. There are 4 nodes, 2 for each AG.
Any ideas on how to overcome this error? I have searched the internet and have had no luck with existing suggestions.
Thank you,
David

Are you in Azure, or AWS?

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

DK SSC Enthusiast Points: 176 More actions · Answer 3

DK

SSC Enthusiast

Points: 176

September 4, 2021 at 10:31 pm

#3926890

AWS.

DK SSC Enthusiast Points: 176 More actions · Answer 4

I used an AWS Launch wizard to create the AlwaysOn Availability groups. AG1 and AG1. They are both on the same VCP and share subnets. After the 2 AGs are created, is there something I need to do prior to running the scripts below? The second one is failing.

CREATE AVAILABILITY GROUP [distributedag]  
   WITH (DISTRIBUTED)   
   AVAILABILITY GROUP ON  
      'WSQLAOAG1Io0hjh' WITH    
      (   
         LISTENER_URL = 'TCP://WSQLAOAG1gDvCxT.wsqlaoag.com:5022',    
         AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,   
         FAILOVER_MODE = MANUAL,   
         SEEDING_MODE = AUTOMATIC   
      ),   
      'WSQLAOAG2Np5ptD' WITH    
      (   
         LISTENER_URL = 'TCP://WSQLAOAG1JeODbS.wsqlaoag.com:5022',   
         AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,   
         FAILOVER_MODE = MANUAL,   
         SEEDING_MODE = AUTOMATIC   
      );    
GO   

USE [master]

GO
ALTER AVAILABILITY GROUP [WSQLAOAG2Np5ptD]
REMOVE DATABASE [TestDB];
GO

-- Run on Primary AG2
ALTER AVAILABILITY GROUP [distributedag]   
   JOIN   
   AVAILABILITY GROUP ON  
      'WSQLAOAG1gDvCxT' WITH    
      (   
         LISTENER_URL = 'TCP://WSQLAOAG1gDvCxT.wsqlaoag.com:5022',    
         AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,   
         FAILOVER_MODE = MANUAL,   
         SEEDING_MODE = AUTOMATIC   
      ),   
      'WSQLAOAG2Np5ptD' WITH    
      (   
         LISTENER_URL = 'TCP://WSQLAOAG1JeODbS.wsqlaoag.com:5022',   
         AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,   
         FAILOVER_MODE = MANUAL,   
         SEEDING_MODE = AUTOMATIC   
      );    
GO

ALTER DATABASE [TestDB] SET HADR AVAILABILITY GROUP = [WSQLAOAG2Np5ptD];