Remove Database from SQL Server AlwaysON Availability Group Secondary Replica

By: Manvendra Singh   |   Updated: 2017-09-26   |   Comments   |   Related: > Availability Groups

Problem

Solution

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.

NOTE: MAKE SURE TO TEST IN LOWER LIFE CYCLE FIRST. DO NOT MAKE ANY CHANGES IN PRODUCTION WITHOUT PROPER TESTING.

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.

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.

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.

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.

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

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
ALTER DATABASE Add_DB_AOAG SET HADR OFF;  
GO

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.

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.

About the author

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