Removing database from AG

Question

I have an AG.

On secondary replica, if we remove database from AG, what will be the database status on primary replica

Answer 1

The same as before. Or more precisely, to remove the database from the AG, you need to be on the primary.

I tried this:

ALTER AVAILABILITY GROUP NormalAgge REMOVE DATABASE Northwind 

This was the output:

Msg 41190, Level 16, State 8, Line 3 Availability group 'NormalAgge' 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.

Answer 2

Hi，Chaitanya Kiran

The operation you mentioned of removing the database on the secondary replica is often used to resolve issues where the primary replica is unable to synchronize due to some reasons.

Regarding the secondary replica：

When a secondary database is removed, it is no longer joined to the availability group and all information about the removed secondary database is discarded by the availability group. The removed secondary database is placed in the RESTORING state.

The data synchronization status will become "Not Synchronized"

Refer to this.

Regarding the primary replica：

Removing a secondary replica does not directly affect the status of the primary replica's database, nor does it affect the data synchronization status.

Refer to

https://www.mssqltips.com/sqlservertip/4819/remove-database-from-sql-server-alwayson-availability-group-secondary-replica/

Best Regards,

Mikey Qiao

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Answer 3

When removing a database from an AG, the Primary replica will have the database in a recovered state (normal looking in that it doesn't have Synchronized, or Synchronizing as a status) because it is the primary. On the Secondary it will be in Restoring mode because it is no longer in the AG.