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
- 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.
- Expand the Always On High Availability folder followed by Availability Groups folder showing in SSMS.
- You can see your Availability Group Name post expanding Availability Group folder. Now expand this folder as well.
- Now you can see Availability Databases folder. Expand this folder to see your availability databases.
- Right-click the selected database or databases, and select Remove Database from Availability Group option as shown in below screenshot.
- 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.
- 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.
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.
- Fix AOAG Listener Error 19471
- Understanding Backup Preferences for Availability Databases
- Fixing Application Connectivity Issue after Automatic Failover
- Error 35250: Joining database on Secondary Replica resulted in an error
Latest posts by Ganesh Bekkinkeri (see all)
- How to Install SSMA for Oracle to Migrate Oracle Database to SQL Server - May 12, 2018
- How to Add a New Database to Always On Availability Group? - January 22, 2018
- How to Remove Database from Always On Availability Group? - January 19, 2018