By:Manvendra Singh || 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.


Removing a Secondary Replica from a SQL Server AlwaysON Availability Group

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.


Removing a Secondary Replica from a SQL Server AlwaysON Availability Group

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.


Removing a Secondary Replica from a SQL Server AlwaysON Availability Group

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.


Removing a Secondary Replica from a SQL Server AlwaysON Availability Group

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.


Removing a Secondary Replica from a SQL Server AlwaysON Availability Group

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


Removing a Secondary Replica from a SQL Server AlwaysON Availability Group
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.


Removing a Secondary Replica from a SQL Server AlwaysON Availability Group

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.


Removing a Secondary Replica from a SQL Server AlwaysON Availability Group

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.


Removing a Secondary Replica from a SQL Server AlwaysON Availability Group

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


Removing a Secondary Replica from a SQL Server AlwaysON Availability Group

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 .

Last Update: 1/5/2017


Removing a Secondary Replica from a SQL Server AlwaysON Availability Group
Removing a Secondary Replica from a SQL Server AlwaysON Availability Group
About the author
Removing a Secondary Replica from a SQL Server AlwaysON Availability Group
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

More SQL Server DBA Tips...

本文数据库(mssql)相关术语:熊片数据库 mssql数据库 oracle数据库 pubmed数据库 access数据库 万方数据库

主题: SQLSQL ServerWindowsWindows ServWindows ServerTIUTUCDUSU
分页:12
转载请注明
本文标题:Removing a Secondary Replica from a SQL Server AlwaysON Availability Group
本站链接:http://www.codesec.net/view/520759.html
分享请点击:


1.凡CodeSecTeam转载的文章,均出自其它媒体或其他官网介绍,目的在于传递更多的信息,并不代表本站赞同其观点和其真实性负责;
2.转载的文章仅代表原创作者观点,与本站无关。其原创性以及文中陈述文字和内容未经本站证实,本站对该文以及其中全部或者部分内容、文字的真实性、完整性、及时性,不作出任何保证或承若;
3.如本站转载稿涉及版权等问题,请作者及时联系本站,我们会及时处理。
登录后可拥有收藏文章、关注作者等权限...
技术大类 技术大类 | 数据库(mssql) | 评论(0) | 阅读(55)