Rajendra Gupta
Add replica into existing always on replica

Add a new node into existing SQL Server Always On Availability Groups

July 23, 2020 by

This is the 5th article in the series of a comprehensive guide to SQL Server Always On Availability Groups.

Introduction

In the previous articles (see TOC at the bottom), we configured a two-node SQL Server Always On Availability Group. We performed the following steps at a high level.

  • Build three Virtual Servers with Windows Server 2016
    • SQLNode1 and SQLNode2 acts as failover cluster nodes
    • VDITest3 works as a domain controller and active directory
  • Configured Domain Controller for [MyDemoSQL] domain, Active Directory in Windows Server 2016
  • Join SQLNode1 and SQLNode2 into [MyDemoSQL] domain
  • Failover Cluster, Quorum and Storage configurations
  • SQL Server 2019 installation
  • Synchronous mode Always On configurations for SQLNode1 and SQLNode2

Suppose you get a requirement to add a new node in the existing cluster and always on configuration.

In this article, we will learn the steps to add a new node in the existing AG configuration.

  • Note: In this article, we will go over the required steps at a high-level. The previous article in this series already covers the things in detail.

Steps to add a new node into existing SQL Server Always On Availability Groups

Server build

Build a new virtual machine in the Oracle VirtualBox. Its configurations should be similar to existing cluster nodes SQLNode1 and SQLNode2. You can follow this article, A comprehensive guide to SQL Server Always On Availability Groups on Windows Server 2016 for reference purposes.

Take an RDP session for the newly created VM and log in using the Administrator account.

Take RDP to the SQLNode

Assign static IP address

Open the network properties for Ipv4 and assign a static IP, DNS server IP as per your earlier configurations.

  • In our case, DNS server IP address is 10.0.2.15
  • I assign IP address 10.0.2.44 for the new VM.

You can refer to the article, Configure Domain Controller and Active Directory for SQL Server Always On Availability Groups for static IP assignments.

Assign static IP address

Turn off the Windows firewall

You should turn off firewalls in your new virtual machine. If you use the firewall, you should open the ports for communication with the domain controller and failover cluster nodes.

Turn off the Windows firewall

Hostname and IP Verification

Verify the hostname and IP address for the new VM. You can use the HOSTNAME and IPCONFIG commands for this purpose.

Hostname and IP Verification

Verify ping response from the SQLNode3 to the Domain Controller IP address.

ping response

Add SQLNode3 as a domain member

Add the SQLNode3 as a member of [MyDemoSQL] domain: Open the server manager and click on the workgroup.

Add SQLNode3 into the domain

In the Computer name/domain changes section, enter the domain name, authenticate with domain admin credentials and restart the system after you get a Welcome message.

Domain change

You should log in with the domain credential after a server reboot.

Verify domain

Enable Failover Cluster feature

Enable Failover Clustering from the Add Roles and Features Wizard.

Enable Failover Cluster feature

Now, we need to add this new node to the existing cluster. Launch failover cluster manager and click on Add Node.

View nodes in a cluster

Add a new node into the existing failover cluster

It opens the Add Node Wizard. You should run a cluster validation to know any existing issues in the cluster.

Add a new node into the existing failover cluster

Click Next and enter the hostname of the newly created virtual machine. This server is a member of the domain; therefore, it shows you FQDN of the server.

Select servers to add into cluster

You get a validation warning because we haven’t performed the failover cluster validation with the existing and new virtual machine.

Validation Warning

We selected the cluster validation option; therefore, it opens the cluster validation wizard.

Validate a configuration wizard

In the cluster validation, it checks the configuration for the cluster, Hyper-V configuration. Storage, inventory and system configuration. We can choose specific tests or run all validation tests. I would recommend you to run all tests for validation.

Run all validation tests

You get progress status for every validation tests. Few tests might not be valid for your configuration. In this case, you get the result as the test is not applicable.

Validation status

Finally, once all the tests are completed, you can open the validation report and fix issues, if required.

View report

On the next page, you get a confirmation page that the new node is ready to add nodes in the existing cluster.

Confirm the server to add into the cluster

Click Next, and you get the message once it successfully adds the node into an existing failover cluster.

Success message

Click Finish. Launch the failover cluster manager and click on Nodes. Here, you can verify that all three nodes are part of the failover cluster. Each node is eligible for a vote to determine resource majority.

View the new node in the failover cluster

Install SQL Server 2019 and enable AG feature

The next step is to install SQL Server 2019 on the new virtual machine. In a SQL Server Always On Availability Group, we install SQL Server as a standalone configuration. You can refer to the article, Install SQL Server 2019 on Windows Server 2016 with SQL Server Always On Availability Groups to learn more about this.

The below screenshot shows a successful database engine service installation on the new virtual machine SQLNode3.

Install SQL Server 2019 and enable AG feature

Now, enable Always on Availability Groups features in the SQL Server Configuration Manager of the SQLNOde3. You must restart SQL Services after enabling AG features.

Enable Always on Availability Groups features

Verify SQL Server Always On Availability Groups dashboard health

Connect to the primary always on replica and view dashboard. It should show healthy always on the dashboard. If there are any data synchronization issues, you should fix them before adding the 3rd node in AG configuration. It helps you to troubleshoot issues in case of any failures after the 3rd node configuration.

You get a healthy AG replica with no expected data loss for existing SQLNode1 and SQLNode2.

AG dashboard health

SQL Server network configurations

You should also verify the TCP port for SQL Server connections. You should use a static TCP port. In case your SQL instance uses a dynamic port, open the SQL Server Configuration Manager and TCP/IP protocol properties. On this page, set the static port. By default, SQL Server works on the TCP port 1433.

SQL Server network configurations

Restore SQL Server Always On Availability Group database on the new replica SQL instance

Before adding the new node into AG configuration, restore a full backup and subsequent transaction log backup of the AG database from the primary replica to the new server SQL instance. This database should be in the restoring (Norecovery) mode.

Restore database into secondary node

Add replica into existing always on replica

In the primary replica instance, right-click on the SQL Server Always On Availability Group and choose Add Replica.

Add replica into existing always on replica

It opens the wizard to add replica into an existing availability group. The wizard gives you a high-level summary of the further steps as well.

Add replica into AG

On the next page, it asks you to connect to existing replicas. We are already connected to the primary replica using SSMS. Therefore, you see it highlights secondary AG replica for connection.

Connect to an existing node

Click Next, and you can see existing configuration for replica, endpoint, backups, listener and read-only routing.

View existing replica

Click on Add Replica, specify SQL Instance name for the new instance that we wish to join into existing AG replica and connect to it. You have an option to choose either the synchronous or asynchronous data commit mode. If you click on an automatic failover checkbox, it automatically selects the synchronous commit mode.

Configure automatic failover

On the next page, select the data synchronization method. We already restored a database copy in the SQLNode3 from the primary replica; therefore, choose the method as Join only.

data synchronization preference

Add replica wizard performs validations as per your inputs.

Result of availability group validation

View the summary of the add replica wizard tasks. You can also generate a script for your actions or click on Finish to complete the wizard actions.

Add replica wizard summary

On the next page, you see the status of each task it took to add a node in the existing SQL Server Always on Availability Group.

Wizard task status

Launch the AG dashboard, and it shows all SQL instances in the synchronized mode. It might take time for the dashboard to become healthy depending upon the transactions performed after the backups.

View AG dashboard

In the failover cluster manager, open the listener properties, and it has all nodes in the preferred owner’s list.

listener properties

Failover testing for the SQL Server Always on Availability group

We should perform a failover testing as well after adding a new node into the existing AG configurations. In the failover wizard, you should verify the failover readiness. It should show status as No data loss for a successful failover without any estimated data loss.

Select the new primary replica (in my case SQLNode3) and click on Next.

Failover testing for the SQL Server Always on Availability group

Connect to the new primary replica.

Connect to the new primary replica

Verify your choice of the new AG replica. It also shows you the affected databases as part of this failover. We have configured [SQLShackDemo] in the AG replica, so you get this database name in the list.

Verify old and new primary replica

Click Finish to initial failover process, and it performs a manual failover successfully as shown below.

failover status

Verify the new replica and data synchronization status using the AG dashboard.

Failover verification

You can also verify that the failover cluster owner is the new primary replica SQLNode3.

SQL Listener owner

Conclusion

In this article, we walk through the process to add a node into the existing SQL Server Always On Availability Group. We will further explore the availability group related configurations in the upcoming articles of this series.

Table of contents

A comprehensive guide to SQL Server Always On Availability Groups on Windows Server 2016
Configure Domain Controller and Active Directory for SQL Server Always On Availability Groups
Configure failover clusters, storage controllers and quorum configurations for SQL Server Always On Availability Groups
Install SQL Server 2019 on Windows Server 2016 with SQL Server Always On Availability Groups
Add a new node into existing SQL Server Always On Availability Groups
Configure Managed Service Accounts for SQL Server Always On Availability Groups
Add or remove a node from SQL Server Always On Availability Groups using T-SQL scripts
Database-level health detection in SQL Server Always On Availability Groups
Automatic Page Repair in SQL Server Always On Availability Groups
Deploy a domain-independent Windows Failover Cluster for SQL Server Always On Availability Groups
An overview of distributed SQL Server Always On Availability Groups
Deploy a distributed SQL Server Always On Availability Group
Monitor and failover a Distributed SQL Server Always On Availability Group
Transparent Data Encryption for SQL Server Always On Availability Groups
Configure SQL Server replication for a database in SQL Server Always On Availability Groups
Configuring SQL Server replication for distribution databases in SQL Server Always On Availability Groups
Explore Cross-database MSDTC for distributed transactions in SQL Server Always On Availability Groups
Deploy MSDTC for distributed transactions in SQL Server Always On Availability Groups
Restore an existing availability group database participating in SQL Server Always On Availability Groups
Exploring AG dashboards for monitoring SQL Server Always On Availability Groups
Backup compression in TDE enabled databases in SQL Server Always On Availability Groups
Impact of dropping a login in the active directory tied to SQL Server Always On Availability Groups
Refresh SQL Server Always On Availability Group databases using DBATools PowerShell
Refresh SQL Server Always On Availability Group databases using DBATools PowerShell
Configure SQL Server Reporting Services databases in SQL Server Always On Availability Groups
Suspend and Resume Data Movement in SQL Server Always On Availability Groups
Explore failover types in SQL Server Always On Availability Groups
Explore SQL Server PolyBase External Tables in SQL Server Always On Availability Groups
SQL Server Always On Availability Groups for SQL Server Linux instances
Column-level SQL Server encryption with SQL Server Always On Availability Groups
Make the most of secondary replicas in SQL Server Always On Availability Groups
Apply SQL Server patches or cumulative updates in SQL Server Always On Availability Groups
Monitor SQL Server Always On Availability groups using extended events

Rajendra Gupta
2,269 Views