How to remove a secondary database from an availability group and rejoin it

Question

I have an availability group (AG) with multiple databases (DB-A, DB-B, DB-C), and multiple secondaries (SEC-B, SEC-C) and one of the databases will not resume synchronization on just one of the secondaries.

For this example, DB-C is not synchronizing on SEC-C and no amount of restarting SQL Server or resuming HADR will get it to start again.

• I don't want to remove the replica (secondary SEC-C) from the AG because I would then have to resync all of the databases (DB-A, DB-B, and DB-C), and that would take more time than is necessary.

• I also don't want to completely remove the database (DB-C) from the AG because there are other secondaries (SEC-B) where there is no problem, and I don't want to have to resync it or temporarily lose my HADR on the secondary where it is working.

How can I remove just this one secondary database from the AG, resynchronize it, and add it back to the AG?

Answer 1

This can be done by simply removing the secondary database from the availability group (AG), restoring backups to get it back in sync, and then adding it back to the AG. SQL Server Management Studio has support for removing a secondary database from an AG and adding it back, but it doesn't have a wizard (as of this writing) for doing all of the steps required to backup, restore, and rejoin.

1. Remove the secondary database from the AG by running the following T-SQL on the secondary (SEC-C in the OP example) where the database is not synchronizing:

   ALTER DATABASE DB-C SET HADR OFF

   See Remove a Secondary Database from an Availability Group. You can also do this step in SSMS by drilling down to the database under the AG on the secondary, right-clicking on it, and selecting the option to remove it.

2. Drop the database from the secondary.

3. Take a full backup and at least one log backup on one of the database replicas that is still in the AG and synchronized, and restore them to the secondary. If it's a big and busy database and you're restoring to a DR site, this could take more than a day. Be sure to leave the database in recovery mode (restore with NORECOVERY)!

   See Prepare a secondary database for an Always On availability group.

4. When you've got the secondary database caught up with the latest log backup restored to it, join it to the AG by running the following command on the secondary (SEC-C in the OP example):

   ALTER DATABASE DB-C SET HADR AVAILABILITY GROUP = AG_name

   See Join a secondary database to an Always On availability group for more information, including how to do this step in SSMS.

This answer is intended to be a high level, succinct guide. This is all well-documented by Microsoft, but it's spread out over multiple pages, which of course have far more info, and the pages are in different categories. The page to remove a secondary database is under:

Business continuity -> Always On availability groups -> How-to -> Operation

and the other pages referenced are under

Business continuity -> Always On availability groups -> How-to -> Configure availability group.

Answer 2

You can use dbatools as well.

Below will get a database from a given availablity group and then will remove it.

Get-DbaAgDatabase -SqlInstance SecondaryServerName -AvailabilityGroup AG101 -Database DBNameYouWantToRemove | remove-dbaagdatabase 

then add the database back to ag group.

Get-DbaAgDatabase -SqlInstance PrimaryServerName -Database DBNameYouWantToAdd | Add-DbaAgDatabase -AvailabilityGroup SharePoint -Secondary SecondaryServerName -AvailabilityGroup AG101