Removing a Secondary Replica from a SQL Server AlwaysON Availability Group

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

Problem

If you receive a requirement to remove a secondary replica from your existing SQL Server AlwaysOn Availability Group then you're in the right place. In this tip I will explain the steps to remove a secondary replica using both the SQL Server Management Studio (SSMS) GUI and T-SQL.

Solution

As you may know a SQL Server Availability Group supports a set of read-write primary databases and one to eight sets of corresponding secondary databases. Here we have one primary replica and two secondary replicas on machines named PRI-DB1 ( IP: 10.X.3.XXX ) which works as primary replica, PRI-DB2 ( IP: 10.X.4.XXX ) works as secondary replica configured for automatic failover and SEC-DB2 ( IP: 172.X.15.XXX ) works as secondary replica for Disaster Recovery (DR) purposes. All machines are running Windows Server 2012 R2 enterprise edition and SQL Server 2014 enterprise edition. The data replication between PRI-DB1 and PRI-DB2 uses synchronous-commit mode and the failover mode is set to Automatic with no data loss which can be used for High Availability (HA) in case the primary replica goes down. Data replication between PRI-DB1 and SEC-DB2 is set to use asynchronous mode for DR purposes.

A requirement arose to remove the DR replica (SEC-DB2) from this AlwaysOn configuration. I performed the below steps to complete the process. You can follow this step by step process to remove a secondary replica from a SQL Server AlwaysOn Availability Group.

NOTE : MAKE SURE TO IMPLEMENT THIS SOLUTION IN A LOWER LIFE CYCLE SYSTEM FIRST. DO NOT MAKE ANY CHANGES IN PRODUCTION WITHOUT PROPER TESTING IN A LOWER-LIFE CYCLE ENVIRNOMENT.

Removing Secondary Replica From AlwaysOn Availability Group using SSMS

Step 1: Our first step is to check and validate the existing AlwaysOn Availability Group configuration by launching the dashboard report from our primary server PRI-DB1. Right click on the Availability Group (DBAG for our example) and choose "Show Dashboard" to display the AlwaysOn dashboard report for this configuration.

The dashboard report for this Availability Group will be displayed in the right pane of SSMS once you click on "Show Dashboard" as shown below. We can see there are three replicas and their configuration is healthy and synchronized. Now we need to remove the replica SEC-DB2 from this configuration.

Step 2: Expand the folder "AlwaysOn High Availability" on the primary replica PRI-DB1 and then expand the folder "Availability Replicas" under AG named DBAG. You can see all three replicas here with their current state and whether they are acting as the primary or secondary. Now right click on the target node you want to remove from this configuration, which is SEC-DB2 for our example. Then click on "Remove from Availability Group..." to proceed to the next step.

Step 3: Once you click on "Remove from Availability Group..." a new window "Remove Secondary Replica From Availability Group 'DBAG'" will open. You can see the name of the target replica to be removed along with the connection details that show we are connected to the primary replica for this exercise.

Check the details on this page and click "OK" to proceed. Once you click OK it will show it is processing for a few seconds and then this window will disappear after successfully completing. The screen will not disappear if there is an issue during removal.

Step 4: Now right click on Availability Group DBAG and refresh it to show the applied changes. We can now see there are only two replicas for this Availability Group.

We can also launch the dashboard report for this Availability Group to check the state after the removal.

Removing Secondary Replica From AlwaysOn Availability Group using T-SQL

Step 1: This task can also be done using a T-SQL statement. Let's first check all available replicas for the Availability Group DBAG. Connect to the primary replica PRI-DB1 and expand the "AlwaysOn High Availability" folder and the "Availability Replicas" folder. We can see there are three replicas in the below picture.

Step 2: Now launch a new query window and connect to the primary replica PRI-DB1 to execute the below T-SQL statement to remove the targeted secondary replica.

--DBAG is AG name.
ALTER AVAILABILITY GROUP DBAG REMOVE REPLICA ON 'Corp\SEC-DB2';

Once the command executes successfully the secondary replica has been removed.

Step 3: We can validate whether the secondary replica SEC-DB2 has been removed. Refresh the Availability Group DBAG on the primary replica to see the number of replicas present where we can see there are only two replicas remaining.

We can also launch the dashboard report for this Availability Group to check the state after the removal.

We can also connect to the secondary replica SEC-DB2 and validate the details in the "AlwaysOn High Availability" folder to validate that it has been removed from the Availability Group.

Next Steps

• Also check error logs and alerts on all replicas for any unusual events.
• Explore more knowledge on SQL Server Database Administration Tips.
• Read these other AlwaysOn Availability Tips.

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