Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Remove SQL Server AlwaysOn Availability Group Configuration


By:   |   Read Comments (1)   |   Related Tips: More > Availability Groups

Attend these FREE SQL Server 2017 webcasts >> click to register


Problem
If you have to remove an AlwaysOn Availability Group from your SQL Server instance then follow this tip. I'll explain step by step how to remove an AlwaysOn Availability Group using both SQL Server Management Studio and T-SQL commands.
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.

Validate AlwaysON Dashboard Report in SQL Server Management Studio

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.

right click on DBAG whihc needs to be removed in SQL Server Management Studio

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.

Remove Availability Group in SQL Server Management Studio

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.

no Availability Groups remaining on either the primary replica or secondary replicas in SQL Server Management Studio

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.

Dashboard report post removal in SQL Server Management Studio

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

Drop Availability Group with T-SQL

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.

Dashboard report for Availability Groups

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.

Availability Groups in SSMS

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

Refresh Availability Groups in SSMS

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.

Refreshed Availability Groups in SSMS
Next Steps


Last Update:


signup button

next tip button



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.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Tuesday, March 28, 2017 - 8:11:21 AM - Gopalakrishnan Arthanarisamy Back To Top

Superb.

 


Learn more about SQL Server tools