Remove database from Distributed Availability Group replica AG ONLY!

Question

all!

I have an existing always on availability group on-prem and last week created a second one in AWS. After that, I created a distributed availability group with on-prem as primary and AWS as the replica AG.

Being new to SQL Server, I configured automatic seeding without thinking for a second that it would immediately start seeding all of the databases. But that’s not really my issue.

There are only about 10 databases that need replicated to the secondary AG and only five of those to the secondary replica of the secondary AG. I’ve tried setting “HADR OFF” but it only works on the secondary replica. The command runs fine on the forwarder but it never actually gets removed.

I’ve found a lot of articles about removing a DB from a DAG, but they all say to run the commands from the primary replica on the primary AG. But that’ll remove the DB from the on-prem AG too, won’t it? I only want it removed from the secondary AG.

Has anyone done this before or know how to do it? Would removing the DAG and starting over but choosing to seed manually allow me to pick and choose the databases that get replicated via the DAG?

If you’re still reading, thank you for taking the time!

Answer 1

Would removing the DAG and starting over but choosing to seed manually allow me to pick and choose the databases that get replicated via the DAG?

Distributed Availability Groups are Availability Groups of Availability Groups. Yes, I understand how that sounds, I didn't make up the terminology.

Thus, whatever is in the original Availability Group must be in the other. They must be mirrors of each other, this means whatever databases are in the original Availability Groups must exist in the other Availability Group if a Distributed Availability Group has been created with those two Availability Groups.

Since Distributed Availability Groups do not work on the individual database level, it is not possible to do what you're asking. If you'd like to have only 5 out of the 10 databases in the Distributed Availability Group, then remove the 5 you don't want from the original Availability Group on the Global Primary side.

While it is technically possible to run mismatched, the log will not truncate for the databases without a copy on the other side, and thus you'll have a whole set of other issues. This is a horrible idea, do not do it.