I’ve seen a lot of great blog posts and content out there talking about Distributed Availability Groups (DAGs). However, I haven’t come across much of anything covering the actual administration of them. Especially for those choosing to use a DAG as their Disaster Recovery (DR) strategy. If you’re choosing to use a DAG for your DR, then you need to know how to perform basic administration. The goal of this post is to cover the gaps in knowledge out there, and cover the nuances of DAG administration as the methods and steps reveal nuances that normal Availability Groups do not necessarily present.

This post is going to cover how to remove and add a database from a Distributed Availability Group. Removing a database from an AG and having to add it back is a common maintenance or troubleshooting step that many DBAs face. But when it comes to DAGs there are some extra nuances and steps you have to keep in mind depending on if you’re working on the global primary AG or the forwarder.

Removing A Database From The Global Primary AG

Removing a database from the Global Primary AG of a DAG follows normal protocol as if it were a normal AG. You can either:

• Connect to the Global Primary Replica.
• Expand Always On High Availability → Expand the Global Primary AG → Availability Databases → Right click the database you want to remove → Remove Database from Availability Group…
• Click OK in the Wizard.

Or, you can run the following command on the Global Primary Replica:

:CONNECT GlobalPrimaryReplicaHere
ALTER AVAILABILITY GROUP [GlobalPrimaryAGHere] REMOVE
DATABASE [DatabaseNameHere]

After you do this, the database on the secondary replicas of the Global Primary AG will go into a restoring state. You can then drop the database from those replicas or do whatever maintenance you would like to do from that point.

The database in the Forwarder AG will remain in that Forwarder AG without issue, but will not be receiving any more transactions from the Global Primary at that point. You have to add the database back to the Global Primary AG for transactions to replicate to the Forwarder AG again.

Adding The Database Back To The Global Primary AG

You can add the database back to the Global Primary AG in any method you see fit as you would with a normal AG (Auto Seeding, Backup-Restore-Join, etc.).

Forwarder AG Cleanup

After you add the database back to the Global Primary AG, you will notice that all Forwarder Replicas of the database in the Forwarder AG will be in a Not Synchronizing state. To fix this you have to do the following:

--STEP 1: Turn HADR off for database on Forwarder Primary--
:CONNECT ForwarderPrimaryReplicaHere
ALTER DATABASE [DatabaseNameHere] SET HADR OFF
--STEP 1b: Apply any t-log backups to BOTH forwarder
--primary database and forwarder secondary replica
--that were taken while the database was not synchronizing
--STEP 2: Add database to Forwarder AG on Forwarder Primary Replica--
:CONNECT ForwarderPrimaryReplicaHere
ALTER DATABASE [DatabaseNameHere] SET HADR
AVAILABILITY GROUP = [ForwarderAGHere]
--STEP 3: Add database to Forwarder AG on Forwarder Secondary Replica once you see it in a Restoring... state--
:CONNECT ForwarderSecondaryReplicaHere
ALTER DATABASE [DatabaseNameHere] SET HADR
AVAILABILITY GROUP = [ForwarderAGHere]

Note: If at any point you see the following error message when attempting to add the database back to the AG, you must first apply any t-log backups taken while the database was removed from the AG. Then try the steps above again:

The remote copy of database “DatabaseName” has not been rolled forward to a point in time that is encompassed in the local copy of the database log.

Removing A Database From The Forwarder AG

In the event you need to remove a database temporarily from the Forwarder AG, you cannot approach it in the way you would the Global Primary. This is because the Forwarder AG, although has a “Primary Replica” like the Global AG, is still considered a Secondary Replica in the eyes of SQL Server. So, if you try to run the above command on the Forwarder Primary Replica, you will get an error saying that the AG is not in a state where it can process the command.

To remove a database temporarily from the Forwarder AG for either maintenance, or if you have to re-seed / re-synchronize it within the Forwarder AG, do the following:

--STEP 1: Connect to the Forwarder Primary Replica and turn off HADR--
:CONNECT ForwarderPrimaryReplicaHere
ALTER DATABASE [DatabaseName] SET HADR OFF

The database on both the Forwarder Primary Replica and the Forwarder Secondary Replica(s) will then go into a Restoring… state. The database and the Global Primary AG will remain in a healthy state. You can now right click → Delete the database on the Forwarder Primary Replica and or the Forwarder Secondary Replica(s) if you wish.

Adding A Database Back To The Forwarder AG

NOTE: The following steps assume that you have already done the above by first connecting to the Forwarder Primary Replica and turning off HADR for the Forwarder Primary Database and you have already deleted the database from the Forwarder Secondary Replica(s).

If you just needed to remove the database from the Forwarder AG to re-seed it or re-sync it to the Forwarder Secondary Replica(s), here’s how you do that:

--STEP 1: Connect to the Forwarder Primary Replica and turn HADR back on--
:CONNECT ForwarderPrimaryReplicaHere
ALTER DATABASE [DatabaseNameHere] SET HADR
AVAILABILITY GROUP = [ForwarderAGHere]

Doing this adds the database back to the Forwarder AG on the Forwarder Primary Replica, while the database on the Forwarder Secondary Replica(s) will remain in a Restoring… state, or deleted if you already did that. This means that as far as the DAG is concerned, the database on the Forwarder Primary Replica is now eligible again for transactions to flow to it.

Next, you’ll want to either want to auto seed the database from the Forwarder Primary Replica to the Forwarder Secondary Replica(s), or manually sync them. I prefer to Auto Seed them when they’re smaller in size since this is on the DR side, but if they’re multi terabyte you will want to manually seed them back. You accomplish auto seeding by doing the following:

:CONNECT ForwarderPrimaryReplicaHere
ALTER AVAILABILITY GROUP [ForwarderAGHere]  
MODIFY REPLICA ON 'ForwarderSecondaryReplicaHere' 
WITH (SEEDING_MODE = AUTOMATIC)

This will push the database back across to the Forwarder Secondary Replica(s).

You will want to change the seeding mode back to manual when you’re done:

:CONNECT ForwarderPrimaryReplicaHere
ALTER AVAILABILITY GROUP [ForwarderAGHere]  
MODIFY REPLICA ON 'ForwarderSecondaryReplicaHere' 
WITH (SEEDING_MODE = MANUAL)

Note: If at any point you see the following error message when attempting to add the database back to the AG, you must first apply any t-log backups taken while the database was removed from the AG. Then try the steps above again:

The remote copy of database “DatabaseName” has not been rolled forward to a point in time that is encompassed in the local copy of the database log.

If you want to manually sync the database in the Forwarder AG, you’ll have to restore the latest Full + Diff + all t-logs to get it current with the primary and then join to the AG.

Summary

There are many reasons you may need to take one or more databases out of the Global Primary AG or Forwarder AG. It could be for maintenance, synchronization issues, or some other reason. Knowing how to approach this and understand the nuances and expected behavior when doing this as it relates to a DAG will better set you up for success long term if you’re using this topology for something longer term like DR and not just a temporary scaffolding for a migration.