Learn more about SQL Server tools

mssqltips logo
 

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

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Add secondary replica to existing SQL Server AlwaysON Availability Group


By:   |   Read Comments (3)   |   Related Tips: More > Availability Groups

Attend our free MSSQLTips Webcast - How to Simplify Routine SQL Server Administration Tasks


Problem

I described how to configure a SQL Server AlwaysON Availability Group in a multi-subnet environment in a previous tip and in this tip I'll explain how to add a replica to an existing SQL Server AlwaysON Availability Group.

Solution

We have two machines named PRI-DB1 ( IP: 10.X.3.XXX ) and PRI-DB2 ( IP: 10.X.4.XXX ). Both are hosted in same data center and both machines are running Windows Server 2012 R2 Enterprise edition and SQL Server 2014 Enterprise edition. PRI-DB1 is the primary replica and PRI-DB2 is the secondary replica using synchronous-commit mode and the failover mode is set to Automatic with no data loss which can be used for HA in case the primary replica goes down.

A requirement came in to setup a Disaster Recovery (DR) machine for the application which is hosted on the primary replica PRI-DB1. We built a machine named SEC-DB2 ( IP: 172.X.15.XXX ) for the secondary replica which will work as DR for this primary replica. The machine is hosted in a different location, so we have chosen to use asynchronous replication mode and the failover mode is set to manual, which can cause some data loss in case of a disaster.

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.

Adding a Replica to a SQL Server AlwaysON Availability Group

Step 1: Our very first step is to check and validate the existing AlwaysON configuration by launching the dashboard report from SQL Server Management Studio (SSMS). Right click on the Availability Group (DBAG in our case) and choose "Show Dashboard" to display the AlwaysON dashboard report for this configuration as shown below.

Validate AlwaysON Dashboard Report in SQL Server Management Studio

We can see there are only two replicas and their configuration is healthy and synchronized. Now we need to add the third replica to this configuration.

Step 2: The next step is to enable the Windows Server Failover Cluster feature on the DR replica, SEC-DB2, and then add our target DR replica node to the existing Windows Server Failover Cluster. The cluster name is DBCluster. Open the Failover Cluster Manager and right click on "Nodes" and choose "Add Node..." and follow the steps to add the new node.

Node addition in Failover Cluster Manager

Once the node is added to the Windows cluster, you can see all three nodes in Failover Cluster Manager as shown below.

Nodes in Failover Cluster Manager

Step 3: Once the node is added, the next step is to enable the SQL Server AlwaysON feature for the SQL Server instance to make it ready for SQL Server AlwaysOn Availability Group creation. Launch the SQL Server Configuration Manger on the target replica SEC-DB2. Right click on the SQL Server service and select properties. Select the "AlwaysOn High Availability" tab and check the box to enable it. You will see the Windows cluster name which will be automatically fetched by this service in the "Windows failover cluster name" section. Click OK to save the changes, and then stop and restart the SQL Server services to apply this change.

Enable AlwaysON Availability Groups in the SQL Server Configuration Manager

Step 4: We already have two databases named DRTest and Test in the Availability Group on the primary replica, now we need to take a full backup and then a transaction log backup of both databases and restore them using the norecovery mode on the target DR replica SEC-DB2 using the same database names to get ready for data synchronization.

Step 5: In SQL Server Management Studio, connect to PRI-DB1 which is our primary replica. Expand the "AlwaysOn High Availability" folder followed by "Availability Groups" folder and then Availability Group name DBAG. You can see three subfolders Availability Replicas, Availability Databases and Availability Group Listeners. Right click on "Availability Replicas" and choose "Add Replica..." as shown in below screenshot.

Add Replica Wizard in SQL Server Management Studio

A new window named "Add Replica to Availability Group - DBAG" will appear to proceed with the remaining steps. Click on the Next button to go to the next window.

Add Replica to Availability Group - DBAG

Step 6: On the next window, you will be asked to connect to existing secondary replicas. We have only one secondary replica that is PRI-DB2. Click on the Connect button to connect to the secondary replica.

Connect to secondary replica in the Add Replica to Availability Group Wizard

The below window will appear to let you enter the connection details for the secondary replica. Enter all required details and click on the Connect button to make a connection. Once you make a successful connection, click on the Next button to proceed.

Enter the connection details for the secondary replica

Step 7: Now we need to enter the details of target DR replica which needs to be added to the Availability Group DBAG. Click on the "Add Replica..." button to connect to the target DR replica SEC-DB2.

Add replica to the SQL Server AlwaysOn Availability Group

Enter the connection details as shown in the below picture and click on the Connect button to make a connection.

Enter the connection details and click on the Connect button to make a connection

Once our target DR replica is connected, you will see it with the other replicas as shown below. Earlier there was only one replica now there are two. We will use the same listener which was configured earlier for this configuration. You just need to add a virtual IP for this new replica as it belongs to a different subnet. So click on the "Listener" tab and then click on the "Add" button to enter the virtual IP for the existing listener. Now click on the Next button to proceed to the data synchronization page.

Specify replicas for the AlwaysOn Availability Group

Step 8: We already restored the databases to the target DR replica SEC-DB2 in step 4, so we will choose the "Join Only" option on this page and click Next to proceed.

Data synchronization with the Join Only option

Step 9: A validation process will run to check the settings as shown below.

Validation for the AlwaysOn Availability Group

Now click Next to go to the final page which is the summary page where you can recheck all of the details. Then click Finish to complete this configuration.

Summary page for the AlwaysOn Availability Group

Step 10: When the "Add Replica to Availability Group" wizard runs you will get a summary of all the rules as shown below.

Add Replica to Availability Group Rules Summary

Validate the AlwaysOn Availability Group in SSMS

Once the above wizard finishes successfully, we can validate the AlwaysOn Availability Group in SSMS. Expand the "AlwaysON High Availability" folder followed by "Availability Groups" and then DBAG. Expand the subfolder Availability Replicas to see newly added secondary replica SEC-DB2 as shown in the below picture.

Validate the AlwaysOn Availability Group in SSMS

We can also see the dashboard report of this Availability Group on the right side in the above picture.

We can also connect to the secondary replica SEC-DB2 and validate the details in the "AlwaysOn High Availability" folder as shown below.

connect to the secondary replica SEC-DB2 and validate the details in the AlwaysOn High Availability
Next Steps


Last Update:


signup button

next tip button



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 


SQL tips:

*Enter Code refresh code     



Monday, June 26, 2017 - 10:07:06 PM - Torben Back To Top

 Hi

Do you have some estimates about downtime/reboots required to add a multisubnet node to a single subnet AoAG cluster?

 


Wednesday, December 28, 2016 - 12:24:17 AM - Manvendra Back To Top

 HI Vijay,

 

Yes you need to enter different virtual IP for each subnet so if your DR server is in different subnet then you need to enter a virtual IP from this subnet to the listener configuration page.


Monday, December 26, 2016 - 10:19:24 PM - Vijay Back To Top

Can you please let me know if we actually should be using another listner ip because this dr node is in a different location. I have an exact requirment as the same. The 2 node AG setup is in DC and my client wants me to add another node in SanJose. Can you please clarify about adding another lister ip because the DR node will be in a different subnet? 

 

Thanks! 


Learn more about SQL Server tools