Share via

Removing database from AG

Chaitanya Kiran 756 Reputation points
2024-05-26T09:31:01.73+00:00

I have an AG.

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

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,074 questions

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Erland Sommarskog 103.5K Reputation points MVP
    2024-05-26T10:01:09.72+00:00

    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.

  2. MikeyQiaoMSFT-0444 1,095 Reputation points
    2024-05-27T08:42:24.57+00:00

    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".

    0 comments
  3. Ben Miller-(DBADuck) 0 Reputation points MVP
    2024-05-28T23:02:20.7233333+00:00

    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.

    0 comments