Description

Creating a Distributed Availability Group(DAG) when your Availability Groups already exist. None of the current documentation I was able to find mentioned how to get DAG working with an already existing and in production SQL AAG. The choice is either break the existing AAG or come up with a way to make to make it work without bringing down production.

Current Status

Heres what I did so my current SQL AAG stayed in place and running. In my current environment I have two pre-existing AAGroups AG1 and AG2.

alt text

A little information about DAG Groups. Availability groups that participate in a DAG do not need to be in the same DC/location. As long as they can communicate they can be part of a DAG. There is no configuration that happens in the WSFC cluster. Everything about it is maintained within SQL Server. A distributed availability group has no listener. A distributed availability group has no listener

Verify accounts and permissions

Before we create the DAG lets make sure accounts and permissions are in place to access the mirroring endpoint. Typically its already created and you just have to browse SSMS>Databases> Security.> Endpoint > Mirroring to see its name. Our is called Hadr_endpoint.

If the endpoint happens to not be there we can easily just create it with the following code :

--create listener endpoints ex. "Hadr_endpoint" , Replace NameofListener with whatever you call your endpoint. 
 
CREATE ENDPOINT [Nameoflistener]
    STATE=STARTED
    AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
FOR DATA_MIRRORING (
   ROLE = ALL,
   AUTHENTICATION = WINDOWS NEGOTIATE,
   ENCRYPTION = REQUIRED ALGORITHM AES
)
GO

 

Now back to making sure the accounts and permissions are in place to access the mirroring endpoint:

--Create login and grant CONNECT permissions to the SQL Server service account on primary node of AG1

use [master]
GO
CREATE LOGIN [Domain\SecondaryServerName$] FROM WINDOWS
GO
GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO [Domain\SecondaryServerName$]
GO
 
--Create login and grant CONNECT permissions to the SQL Server service account on the primary node of AG2

use [master]
GO
CREATE LOGIN [Domain\PrimaryServerName$] FROM WINDOWS
GO
GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO [Domain\PrimaryServerName$]
GO
  
-- Alter the endpoint so it listens on all ips.

ALTER ENDPOINT [Hadr_endpoint]
    AS TCP (LISTENER_IP = ALL)
GO

 

Create the Listener

We next have to create the Listener so that AG1 and AG2 can communicate over port 5022 , Notice the listener URL is the name of the current Listener in the primary AAG groups just a different port number. Heres where the problem is ,since we already have both primary AG1 and AG2 groups pre populated with databases. The DAG group would not create. Most of the docs dont mention that the AAG has to be empty to be added to the DAG. So I created two empty AAG groups on each cluster in cluster 1 AG1 the Dag group is called Distributed_AG1 in cluster2 AG2 is Distributed_AG2

--create dag group on primary node of AG1 (syntax must be exactly as below)
 
CREATE AVAILABILITY GROUP [AG_Distributor]
WITH (DISTRIBUTED)
AVAILABILITY GROUP ON 'SQLSERVER1_AG' WITH
(
    LISTENER_URL = 'tcp://SQLSERVER1L:5022',
    AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
    FAILOVER_MODE = MANUAL,
    SEEDING_MODE = AUTOMATIC
),
'Distributed_AG1'
WITH  
(  
    LISTENER_URL = 'tcp://SQLSERVER2L:5022',
    AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
    FAILOVER_MODE = MANUAL,
    SEEDING_MODE = AUTOMATIC
);
GO

 

Join AG2 to the DAG.

--To be run on the primary node of AG2 , notice is says join and not create.
  
ALTER AVAILABILITY GROUP [AG_Distributor]  
JOIN 
AVAILABILITY GROUP ON
'SQLSERVER1_AG'
WITH  
(  
    LISTENER_URL = 'tcp://SQLSERVER1L:5022',   
    AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,  
    FAILOVER_MODE = MANUAL,  
    SEEDING_MODE = AUTOMATIC  
),  
'Distributed_AG1'
WITH  
(  
    LISTENER_URL = 'tcp://SQLSERVER2L:5022',  
    AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,  
    FAILOVER_MODE = MANUAL,  
    SEEDING_MODE = AUTOMATIC  
);   
GO
  
--Once created grant allow permissions to the newly created AG group to create databases and replicate over.
 
ALTER AVAILABILITY GROUP [Distributed_AG1] GRANT CREATE ANY DATABASE 
GO

Verify your work

Now if you log into SSMS, and connect othe primary node of AG1, you will see the DAG group. The Primary AG SQLSERVER1_AG will always say primary. The DAG group you created above “Distributed_AG1” will always say secondary.

Connect to the Primary node of the secondary AG2 cluster ( this node is also called the forwarder) When you expand the AAG group , you will see the databases “Primary_DB1 and Primary_DB2” have already auto seeded into AG2.

AG_Distributor is the DAG group that copies all the databases from SQLSERVER1_AG into to the primary node of the SQLSERVER2_AG cluster.  

Bring it all together

Restore Databases from AG1 to AG2 (Important step)

THIS IS IMPORTANT You must restore the Databases from AG1 to AG2 ( secondary node). Remember, the primary node is called the forwarder. The forwarder will automatically join the databases and sync (auto seed). However, the secondary nodes need to be restored to be able to join the DAG.
 

Join the databases to the replicas.

To Join the database to the replicas ( secondary node of each DAG)

  1. On the primary node of AG1 Take a full backup , add check to “copy only” of the database. move the .bak file to the secondary node of AG2.
  2. On AG2 replica (secondary) Right Click database > Restore database and browse to the location of the .bak file.
  3. Under options you must change the dropdown menu to “Restore with No recovery” then click ok.
  4. Once its restored , refresh the database list and the data base will be listed as “restoring” this is normal.
  5. Expand the DAG and find the name of the database it should have a yellow bang by it. Right click and Join the availability group.
  6. Refresh the database list and all will show synchronizing.
     

Conclusion

Your DAG and AAG should now look like the diagram below. Where all the databases from AG1 are now Synchronized to AG2

alt text