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 Database from Primary Replica


By:   |   Read Comments   |   Related Tips: More > Availability Groups

Attend our free MSSQLTips Webcast - How to Simplify Routine SQL Server Administration Tasks


Problem

In a previous tip we looked at how to remove a SQL Server AlwaysOn Availability Group (AOAG) database from a secondary replica.  In this tip we will look at how to remove a SQL Server database from the primary server.

Solution

In an earlier tip, I explained how to remove a secondary database from existing AOAG. In this tip, I will explain how to remove the primary SQL Server database from an existing AOAG configuration.

There are many reasons that will necessitate the removal of a database from the primary replica. Suppose you want to run a data load or large import to that database. You can remove the database from AOAG, change the recovery model to SIMPLE, import the data load and then you can re-add it to the AOAG. Another reason is for a SQL Server database restore. If you have to restore that database, you need to first remove the database from AOAG and then you can restore the database followed by re-adding it to the AOAG.

As you may know, an Availability Group supports a set of read-write primary databases and one to eight sets of corresponding secondary databases. In this example we have an AOAG configuration between two replicas. Our secondary replica is hosted in another datacenter for Disaster Recovery support.

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

Removing a Primary Database from an 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 SQL Server Management Studio (SSMS) right click on the Availability Group name and choose "Show Dashboard" to display the AlwaysON dashboard report for this configuration.

Validate AlwaysON Dashboard Report

The dashboard report for this Availability Group will be displayed on the right side of SSMS. We can see everything looks healthy and in a green state. We can see there are three availability databases that are participating in this AOAG. Now we will remove two of the primary databases "Add_DB_AOAG_GUI" and "ADD_DB_AOAG" from this configuration.

Step 2: Remove Primary Database

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

right click on database that needs to be removed

Click on "Remove Database from Availability Group..." option to proceed to the next step. If you want to remove multiple databases from this availability group, then you can use the Object Explorer Details pane to view and select all the databases that you want to remove.

Step 3: Confirm Removal

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

Remove Database from Availability Group window

Step 4: Removal Being Processed

Check the details on this page and click the "OK" button to proceed. Once you click on OK it will show that it is processing for a few seconds and then this window will disappear from the screen if removal is processed successfully. The screen will not disappear if there is any issue during the removal. The removal window disappeared post processing which means we have successfully removed this database from this configuration.

Step 5: Validate Removal of Primary Replica

Now launch the dashboard report again to validate the changes. We can see the removed database does not show in the dashboard report anymore and the AOAG has only two databases now. When you look at the database status under the database folder of the instance, you will not find "Synchronized" besides this database on the primary replica and this database status will be in a Restoring state on the secondary replica.

dashboard report

Now you can go ahead and proceed with the data load or restore whatever activity you have planned for this database. If you want to remove this database, you can now drop it from both replicas.

Remove Primary Database From AlwaysON Availability Group using T-SQL

Step 1: Review Status

We have removed a primary database from the AOAG in the above section using SSMS. Now we will remove another database "Add_DB_AOAG" from the same Availability Group using T-SQL.

Step 2: Remove Primary Database

Open a query window and connect to the primary replica for the Availability Group and run the following.

--Remove Primary DB from AOAG
--enter the name of the Availability Group and the Database to remove
ALTER AVAILABILITY GROUP DBAG_*** REMOVE DATABASE Add_DB_AOAG;  
GO

remove primary database from AOAG using T-SQL

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

Step 3: Validate

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

dashboard report of dbag

We can see the database has been removed from AOAG configuration and only one database remains as part of this Availability Group.

Now you can perform any activity like a restore or data load on this database. Although this database does not show in the AOAG dashboard report, it will be online and accessible from the primary replica and the database on the secondary replica will be in a Restoring state. You can then remove the replica if it is not needed or you can take it online if you want to use this database. It's up to you how you are going to use the database.

SSMS status of removed databases
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     



Learn more about SQL Server tools