Remove SQL Server AlwaysOn Database from Primary Replica

By: Manvendra Singh   |   Updated: 2017-10-11   |   Comments (2)   |   Related: > Availability Groups

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.

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.

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.

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.

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

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.

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.

Next Steps

• Check the error logs, alerts on all replicas for any unusual events/logs.
• Explore more knowledge on SQL Server Database Administration Tips.
• Check out this other tip: Remove Database from SQL Server AlwaysON Availability Group Secondary Replica.

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