Remove Database from SQL Server AlwaysON Availability Group Secondary Replica

By:   |   Comments   |   Related: > Availability Groups

If you have a need to remove a SQL Server Availability Group database from a secondary replica then you should read this article. I'll explain the steps to remove the secondary database using both the SQL Server Management Studio (SSMS) and T-SQL code.

As you may know, a SQL Server Availability Group supports a set of read-write primary databases and one to eight sets of corresponding secondary databases. For our example, we have Availability Group configuration with three databases. We will make changes to remove one of the databases from the Availability Group. This could be for maintenance or just the need to refresh and re-synch the database if it is out of synch for some reason.


Removing Secondary Database From AlwaysON Availability Group using SSMS

Step 1: Review and Validate

Our first step is to check and validate the existing AlwaysON Availability Group configuration by launching the dashboard report in SSMS. To do so in SSMS, on the primary server, right click on Availability Group name and choose "Show Dashboard" to display the AlwaysON dashboard report as shown below. Note: I masked some the data from my server, hence the red boxes.

Validate AlwaysON Dashboard Report

We can see above that everything looks healthy and is in green state.

Step 2: Remove Secondary Database

Now we need to remove one of the secondary databases - "Add_DB_AOAG_GUI" from this configuration.

In SSMS, expand the folder "AlwaysOn High Availability" on the secondary replica where you want to remove the secondary database. Now expand folder "Availability Databases" under AOAG name. You can see all three availability databases are showing a green status. Right click on the identified database you want to remove from this configuration as shown in the below screenshot.

right click on secondary database that needs to be removed

There are multiple options you will find here. Click on the "Remove Secondary Database..." option to proceed to the next step.

Step 3: Confirm Removal

Once you click on the "Remove Secondary Database..." option another window "Remove Secondary Database From Availability Group..." will appear. You can see the name of the identified secondary database that needs to be removed from the configuration.

Remove secondary database window

Step 4: Removal Being Processed

Once you click on OK it will show you that it is processing for few seconds and then this window will disappear from the screen if removal is successful. The screen will not disappear if there is an issue during removal.

Step 5: Validate Removal of Secondary Replica

Now open the SSMS dashboard report again to validate the changes. We can see the status of secondary database "Add_DB_AOAG_GUI" is in red and the data synchronization status shows "Not Synchronizing". This is because database Add_DB_AOAG_GUI has been removed from the secondary replica and is not receiving any data from the primary.

dashboard report

If you click on Warnings that are shown in the dashboard report you will get the below information.

dashboard report

Removing Secondary Database From AlwaysON Availability Group using T-SQL

Step 1: Review Status

This task can be done using a T-SQL statement as well. We can see we have removed one secondary database from the secondary replica in the above section using SSMS. Now we will remove another one of the secondary databases, "Add_DB_AOAG". You can see the status of this database is showing green and synchronized in the above screenshots.

Step 2: Remove Secondary Database

Now launch a new query window and connect to the secondary replica from where you want to remove your secondary database from the Availability Group configuration.

--Remove Secondary DB from AOAG
remove secondary database using T-SQL

Once the command executes successfully you are done with the secondary database removal.

Step 3: Validate

Now we will check and validate if the secondary database has been removed from this AG. Launch the SSMS dashboard report for this Availability Group to check the details.

dashboard report of dbag

We can see database has been removed from the Availability Group configuration on the secondary replica. Now you can perform any activity like a restore that needs to be done on this secondary database.

Next Steps
  • Stay tuned for additional tips related to how to remove a database from an Availability Group and other related tasks for Availability Groups.
  • Go and check error logs, alerts on all replicas for any unusual events/logs.
  • Explore more knowledge on SQL Server Database Administration Tips.
  • Check out these other Availability Group tips.

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Manvendra Singh Manvendra Singh has over 5 years of experience with SQL Server and has focused on Database Mirroring, Replication, Log Shipping, etc.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

get free sql tips
agree to terms