How to Remove Database from Always On Availability Group?

This article describes how to remove availability database(s) from an Always on Availability Group by using SQL Server Management Studio and Transact-SQL. Here, we will be removing primary database from availability group that will remove corresponding secondary database from AOAG configuration as well.

You must be connected to primary replica of AOAG configuration to perform this activity. If you connect to secondary replica to remove availability databases from AOAG configuration then only secondary database from AOAG configuration will be removed. If you remove availability database or say primary database from AOAG configuration on primary replica then it will remove availability databases from always on availability group from both replicas.

Remove Availability Database from AOAG using SSMS

  1. Launch SQL Server Management Studio. Connect to the SQL Server instance that hosts the primary replica of the database or databases to be removed, and expand the server tree.
  2. Expand the Always On High Availability folder followed by Availability Groups folder showing in SSMS.
  3. You can see your Availability Group Name post expanding Availability Group folder. Now expand this folder as well.
  4. Now you can see Availability Databases folder. Expand this folder to see your availability databases.
  5. Right-click the selected database or databases, and select Remove Database from Availability Group option as shown in below screenshot.Right Click on Primary Database
  6. You will be getting below window to proceed with the database removal from AOAG configuration. In the Remove Databases from Availability Group dialog box, to remove all the listed databases, click OK.Remove database from Availability Group window
  7. If you want to remove multiple databases from AOAG configuration, use the Object Explorer Details pane to view and select all the databases that you want to remove. You can also remove a single database either from the Object Explorer pane or the Object Explorer Details pane.

This way you will remove primary database or say availability database from AOAG configuration. Data synchronization will be stopped for this database between both replicas the moment you remove it from availability group.

Later, you can decide whether you want to remove your secondary database or you want to use it by bringing it online. Make sure to change database name of secondary database to avoid any confusion because a copy of this database is already online with same name on primary replica.

Remove Availability Database from AOAG using T-SQL

Connect to SQL Server instance that are acting as primary replica of AOAG configuration.  Open New Query window to execute T-SQL query. Run below ALTER AVAILABILITY GROUP statement. Make sure to change the availability group name and availability database name that needs to be removed from AOAG configuration.

--Availability_group_name is the name of the availability group
--Availability_db_name is the name of the database to be removed
ALTER AVAILABILITY GROUP Availibility_group_name REMOVE DATABASE Availability_db_name

I have executed below command to remove my availability database Techyaz from availability group name AG_Techyaz on primary replica.

ALTER AVAILABILITY GROUP AG_Techyaz REMOVE DATABASE Techyaz;

You can see i have successfully execute this command in below screenshot.

Remove database from aoag using tsql

Now, database will be removed from always on availability group and data synchronization will be stopped for this database between both replicas. Primary database will come online for read write and secondary database on secondary replica will be in restoring state. I hope you like this article. Please follow our Facebook page and Twitter handle to get latest updates.

Read More:

Ganesh Bekkinkeri

I am working as SQL Server DBA in one of the biggest IT company in the world. I have very good knowledge on SQL Server database administration. Please contact me at info@techyaz.com if you have any question.
Summary
How to Remove Database from Always On Availability Group?
Article Name
How to Remove Database from Always On Availability Group?
Description
This article describes how to remove availability database(s) from an Always on Availability Group by using SQL Server Management Studio and Transact-SQL. Here, we will be removing primary database from availability group that will remove corresponding secondary database from availability group as well.
Author
Publisher Name
www.techyaz.com

You may also like...

1 Response

  1. May 17, 2018

    … [Trackback]

    […] Read More Infos here: techyaz.com/sql-server/alwayson/remove-database-always-availability-group/trackback/ […]

Leave a Reply

Your email address will not be published. Required fields are marked *