Sample Header Ad - 728x90

Cannot remove Database from Distributed AlwaysOn Group on Secondary Windows Cluster

7 votes
3 answers
22297 views
We have Distributed AG across two windows clusters. - Clus01 - Has an AG set up with no DB inside it (AG1) - Clus02 - Has an AG set up with no DB inside it (AG2) - Clus01 - Has a DistAG set up with AG1 (primary) and AG2 (secondary) We - Joined Clus02 to the DistAG - Added a database to AG1 on Clus01 - Restored this DB in recovery to AG2 - Added this DB to AG2 on Clus02 (Showing as Primary, but really is secondary within the DistAG) DB on Clus02 was now working as expected...any updates on DB on Clus01 would use DistAG to update Clus02 DB. We often refresh this environment from live (this is preprod). so we have to restore the DB onto CLus01. For our normal AGs, we just remove the DB from CLUS01, restore to CLUS01, backup and restore to CLUS02 and join to AG again. For DistAG, I cannot 1. Remove the DB from AG2 on CLUS02. ALTER AVAILABILITY GROUP [AG_G2CoreReporting] REMOVE DATABASE [Genesis]; GO We get the error > Msg 41190, Level 16, State 8, Line 6 Availability group 'AG2' 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. 2. Remove the DB from AG1 on CLUS01. DB on AG2 on CLUS02 goes into not syncronizing state and I cannot remove (same error as above), so cannot restore DB over the top. 3. Remove the replica on the DistAG by running on CLUS01 ALTER AVAILABILITY GROUP [DistAG] REMOVE REPLICA ON N'AG2'; We get the error > Msg 15151, Level 16, State 1, Line 5 Cannot alter the availability group 'AG2', because it does not exist or you do not have permission. Is there anyone that knows how I can refresh an environment with DistAG without having to remove the whole DistAG and set it up again?
Asked by DJSQL (73 rep)
Mar 7, 2018, 04:28 PM
Last activity: Jan 12, 2024, 12:51 PM