Remove SQL Server AlwaysOn Availability Group Configuration

By: Manvendra Singh   |   Comments (1)   |   Related: > Availability Groups

Problem

Solution

The AlwaysOn Availability Groups feature is a high-availability and disaster recovery solution that provides an enterprise-level alternative to database mirroring. AlwaysOn Availability Groups provide a rich set of options that improve database availability and that enable improved resource use. Here we are focusing on removing multiple or a single AlwaysOn Availability Group.

The best practice is to remove the Availability Group only while connected to the server instance that hosts the primary replica. When the Availability Group is dropped from the primary replica, changes are allowed in the former primary databases. Deleting an Availability Group from a secondary replica leaves the primary replica in the RESTORING state and changes are not allowed on the databases.

NOTE: MAKE SURE TO TEST THIS SOLUTION IN A LOWER LIFE CYCLE ENVIRNOMENT FIRST. DO NOT MAKE ANY CHANGES IN PRODUCTION WITHOUT PROPER TESTINGS IN LOWER-LIFE CYCLE ENVIRNOMENTS.

Removing AlwaysOn Availability Group using Management Studio

Step 1: Our very first step is to check and validate the existing AlwaysOn Availability Group configuration by launching the dashboard report. Connect to the server instance that hosts the primary replica, if possible, or connect to another server instance that is enabled for the AlwaysOn Availability Group on a WSFC node that has the correct security credentials for the Availability Group. Expand the server tree and expand the AlwaysOn High Availability node and the Availability Groups node. Right click on the Availability Group (named DBAG in our example) and choose "Show Dashboard" to display the AlwaysOn dashboard report for this configuration.

The dashboard report for the availability group DBAG will be displayed in the right side pane of SSMS once you click on the "Show Dashboard" option as shown in the above screenshot. We can see there are three replicas and their configuration is healthy and synchronized. Now we need to remove this entire configuration.

Step 2: Expand the folder "AlwaysOn High Availability" on the primary replica PRI-DB1. Now expand the folder Availability Groups. You can see the name of all the availability groups here although we have only one availability group, DBAG. Now right click on the availability group named DBAG and choose the "Delete..." option to remove this configuration as shown in the below screenshot.

Step 3: Once you clicked on the "Delete..." option another window, "Remove Availability Group 'DBAG'", will appear on your screen. You can see the name of the target Availability Group DBAG which needs to be removed in this window. If you have multiple availability groups whose primary replicas are on same server, you can use the Object Explorer Details pane to view and select all the availability groups that you want to delete.

Step 4: Check the details on this page and click on the "OK" button to proceed. Once you click "OK" it will show you processing for few seconds and then this window will disappear from the screen if the removal is processed successful. Note, the screen will not disappear if there is an issue during removal.

The removal window disappeared post processing which means we have successfully removed the AlwaysOn Availability Group DBAG. You can still see the AlwaysOn Availability Group in SSMS post removal, so you need to refresh the "Availability Group" folder by right clicking on this folder and selecting Refresh as shown below. You can do the same on the secondary replicas to refresh the folders. Now you can see there are no Availability Groups remaining on either the primary replica or secondary replicas.

Step 5: We can launch the Dashboard report for Availability Groups to check the details post removal. Click on the Availability Group folder on the primary replica PRI-DB1, right click and choose "Show Dashboard". You will see nothing in the right side pane if you had only one Availability Group which was removed. If you have multiple Availability Groups then you can see the rest of the Availability Groups that still remain.

Dropping an Availability Group deletes any associated Availability Group Listener as well. If a server instance that hosts one of the availability replicas is offline when you delete an Availability Group, after coming online the server instance will drop the local availability replica.

Removing AlwaysOn Availability Group using T-SQL

Step 1: This task can be done using a T-SQL statement as well. Before going ahead we can check all available Availability Groups by expanding "AlwaysOn High Availability" node and the "Availability Groups" node on primary replica in SSMS. We can consider image 1 of this tip to see DBAG availability group along with its all possible replicas and we need to remove it from this SQL Server using a T-SQL statement.

Step 2: Now launch new query window and connect to primary replica PRI-DB1 to execute the below T-SQL statement to remove the target Availability Group DBAG.

--DBAG is the name of target Availability Group.
DROP AVAILABILITY GROUP DBAG

Once the command executes successfully you are done with the removal of AlwaysOn Availability Group DBAG.

Step 3: Now we will check and validate whether the Availability Group DBAG has been removed or not. We can see the dashboard report to check the details. Right click on "Availability Group" folder and choose "Show Dashboard", there should be nothing showing in the right side pane if you had only one Availability Group which you removed in the previous steps. We can see the same in the below screenshot.

We can also connect to the secondary replicas and validate the details in the "AlwaysOn High Availability" folder to validate whether it's been removed or not.

Step 4: You might be able to see the Availability Group details in SSMS as shown in the below image post removal this is because you have not refreshed the folders.

Right click on the "Availability Group" folder on the primary replica and choose "Refresh..." to update the changes we did in the above steps.

Now you can see everything has disappeared from the "Availability Group" folder in the below screenshot. You can do the same exercise on the secondary replicas as well if you still the Availability Group details.

Next Steps

• Check the error logs and alerts on all replicas for any unusual events/logs.
• Learn more with these SQL Server AlwaysOn Availability Group tips.
• Explore more knowledge on SQL Server Database Administration 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