Learn more about SQL Server tools

 

Tutorials          DBA          Dev          BI          Career          Categories          Events          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Events

DBA    Dev    BI    Categories

 

Removing a Secondary Replica from a SQL Server AlwaysON Availability Group


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

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.

Validate AlwaysON Dashboard Report

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.

Validate AlwaysON Dashboard Report

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.

right click on target node whihc needs to be removed

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.

Remove replica window

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.

Refresh DBAG

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

Dashboard report post 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.

Check all replicas

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.

Remove replica

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.

Refresh dbag

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

Dashboard report of dbag

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


Last Update:






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 


Get free SQL tips:

*Enter Code refresh code     



Learn more about SQL Server tools