Distributed Availability Groups were introduced in SQL 2016 and compliment AlwaysOn Availability Groups, their main use cases are for Disaster Recovery, Multi-Site configurations, migrations to the latest Operating Systems and increasing the number of read replicas. When you come to remove a database from a DAG it is not as simple as just deleting a database as you normally do when deleting databases from a normal AG as this removes it from the primary AG but leaves a disjointed copy in the secondary/DR AG.
This guide shows you how to delete a database that is distributed across 4 servers/instances within 2 availability groups.
The image below shows how a Distributed AG replicates changes from the primary to the other nodes and is useful to explain the roles each server plays in the removal of the database.
Source: microsoft.com
Incorrect way to remove a database from a Distributed Availability Group
If you just remove the database in the same way as you would when removing one from a standard AG, it is removed from the primary AG but it remains in the secondary/DR AG, if you then try to remove it from this one you will come up against an error similar to,
"Availability group 'AG 2 name' failed to process remove-database command. The local availability replica is not in a state that could process the command. Verify that the availability group is online and that the local availability replica is the primary replica, then retry the command. (Microsoft SQL Server, Error: 41190)"
Correct way to remove a database from a Distributed Availability Group
1. Turn HADR off on forwarder on secondary AG
From forwarder server at secondary AG run the following command setting your database name
USE master
GO
ALTER DATABASE [db_name] SET HADR OFF;
GOOnce this has ran it breaks the Distributed AG replication for the database
2. Remove DB from Primary AG
On the primary server of the primary AG, remove the database from the AG
- Primary server will go back to a normal un-replicated database
- Primary replica will go into a restoring state
3. Remove it from each AG starting on secondary AG replica in the following order;
- Replica on secondary AG
- Forwarder on secondary AG
- Replica on primary AG
- Primary on primary AG
The database should now be successfully removed from all locations.