Rajendra Gupta
Connect all existing replica's

Add or remove a node from SQL Server Always On Availability Groups using T-SQL scripts

July 29, 2020 by

This is the 7th article in the series of SQL Server Always on Availability Groups.

Introduction

In this series of articles, we configured the SQL Server Always On Availability Group from the beginning of building virtual machines. We covered the following topics so far in this series.

  • We configured an Active Directory, domain controller and quorum
  • SQL Server 2019 in a three-node high availability synchronous commit AG configuration
  • Failover testing and validations

We used the graphical SSMS wizard for AG configurations so far. It is essential to know the equivalent T-SQL scripts for these configurations. You can automate the overall process using useful scripts.

In this article, we will cover SQL scripts for adding or removing a new node into the SQL Server Always On Availability Group configuration.

Before we start the SQL configuration, let’s resolve an issue in the Oracle virtual box. By default, you cannot copy any content between the host and the virtual machine. It might be required for you to copy these scripts from host to VM or VM to host.

Enable data copy between host and VM in Oracle Virtual machine

In the VM, navigate on Devices -> Insert Guest Additions CD Image…

Insert Guest Additions

It inserts a VirtualBox Guest Additions CD in respective VM.

VirtualBox CD

Double-click on this CD drive to launch the Oracle VM VirtualBox guest additional setup.

Start guest additional installation

Click Next and select the destination folder. It requires approx 1 MB space in the drive.

Destination folder

In the next step, it shows the components to install.

Select components

Reboot the VM to complete the guest additional feature for a VM.

Reboot VM

Once the VM is up, navigate to Devices -> Shared Clipboard -> Bidirectional.

Shared Clipboard

You can perform these steps on remaining VM’s as well in a similar way.

Perform SQL Server Always on Availability Group failover using T-SQL scripts

I have the following VM’s for my AG setup.

  • SQLNode1.mydemosql.com – 10.0.2.21(Secondary replica)
  • SQLNode2.mydemosql.com – 10.0.2.22 (Secondary replica)
  • SQLNode3.mydemosql.com – 10.0.2.44 (Primary replica)
  • VDITest3.mydemosql.com – 10.0.2.15 ( Domain Controller, Active directory)

Connect to the primary replica and launch the AG dashboard. It is in the synchronized state without any data loss.

SQL Server Always on Availability Group failover

For this article, we will go through GUI as well as equivalent T-SQL scripts to get dual knowledge. Launch the AG failover and select the new replica.

We want primary replica as SQLNode1\INST1 instance.

Select new replica

On the next page, select the current secondary replica that you want to promote as a primary replica.

Connect to the secondary replica

Verify the current and new primary replica. Click on the Script to generate an equivalent T-SQL script for AG failover.

Verify new primary replica

It opens a new query window with the failover script. This Script is in SQLCMD mode.

View script in SQLCMD mode

It uses an Alter Availability Group command to initiate an AG failover from SQLNode3 to SQLNode1. We need to enable the SQLCMD Mode in SSMS to execute the generated Script.

Enable SQLCMD mode

In the SQLCMD mode, you can see a background color for the connection’s strings. Press F5 to execute this Script. In the output, you get SQL connections messages.

Execute the script

Connect to the new replica SQLNode1 and view the AG dashboard. This SQL instance took over the responsibility of a primary replica after the failover.

Verify new replica

Remove a Node in SQL Server Always on Availability Groups using T-SQL

For this demonstration, we remove the SQLNode3 from the AG configuration. Later, we will add it back in the asynchronous commit mode.

To remove a node, expand the Availability replicas.

remove a node

It opens the following window to remove the secondary replica from the availability group SQLAG2019.

Remove secondary replica

Generate the Script for node removal. It uses an alter availability group to remove the replica command for this purpose.

Execute this T-SQL script, and it removes the specified replica from the AG configuration.

Execute script to remove replica

Adding a Node in SQL Server Always on Availability Groups using T-SQL

Adding a Node in SQL Server Always on Availability Groups

Connect to an existing replica

Specify new replica

Select the initial data synchronization method.

Select initial data synchronization method.

View the replica wizard summary.

View the replica wizard summary.

Click on Script to generate a SQL script in the new query window. Let me give an overview of this Script.

  • First, it connects with the primary replica SQLNode1 and grants connect permission to the HADR endpoint for the SQL Service account. In my case, SQL Services are running under group managed service account [MyDemoSQL\gmssqlservice$]

  • It connects with the existing secondary replica and assigns the connect permission to the endpoint

  • It connects with the new SQLNode3 instance. It starts the endpoint (Alter Endpoint) and gives the connect permissions (grant connect)

  • It starts the extended event session for SQL Server Always On SQLNode3. It uses alter event session T-SQL for it. You can monitor AG using this extended event session

  • It connects with the primary replica and adds the new replica into the availability group [SQLAG2019]. Note the following arguments in the alter availability group command

    • Endpoint_URL: It creates the endpoint for the new SQL instance for AG communication purposes. The endpoint is TCP://SQLNode3.MyDemoSQL.Com:5022
    • Availability_Mode: We have chosen the asynchronous data mode for AG configuration. It adds value to this argument as Asyncronous_Commit
    • Secondary_role: We can allow read-only connections on the secondary AG replica. By default, SQL Server does not allow any connection to the secondary replica. Therefore, you have the value as Allow_Connections = No for this argument
    • Failover_mode: In an asynchronous data commit mode, SQL Server support only manual failover

  • Now, it connects with the new secondary replica and joins it in the availability group

  • Finally, it waits for the replica communication for the new AG replica. You get an error message in case there are any communication issues

    SQL script generated by SQL Server

Enable the SQLCMD mode to execute this Script. In the query output, it gives connections messages, as shown below.

script output

Launch the AG dashboard. You see SQLNode3 is part of the SQLAG2019 availability group in the asynchronous mode.

  • Note: In an asynchronous mode, you get status as Synchronizing, and you might see data loss as well.

AG dashboard

Adding a new database in the SQL Server Always on Availability Group replica using T-SQL

In this section, let’s add a new database into the SQL Server Always on Availability Group.

  • Create a new database in the primary replica
  • This database should be in full recovery model
  • Perform a native full database backup

In the primary replica instance, right-click on the availability database -> Add database.

It shows the existing AG database and the database eligible for an AG configuration. Once you performed the full backup for a full recovery model database, it becomes eligible for AG.

Select database and status

Connect to all existing secondary replicas.

Connect all existing replica's

We can add a database to an availability group with various initial data synchronization methods. Previously, we used the Join Only method, where we manually restored the full backup into a secondary replica.

Let’s choose the full database and log backup initial data synchronization. For this method, we required a shared folder accessible for all AG nodes.

Full backup and log backup

  • Note: You should have similar database directories across all SQL instances in an AG configuration. Otherwise, you get an error message like below

    Error message

Generate the Script from the validation page.

View summary and generate script

Let’s quickly go through the script sections:

  • On the primary SQL node, it alters the availability group and adds the database into the availability database

  • It performs a full backup in the \\SQLNode1\SQL directory

  • Restore the full backup from the shared directory into the secondary replica’s SQLNode2 and SQLNode3

  • It again connects to the primary replica and takes transaction log backup

  • Restore transaction log backups into the SQLNode2, waits for the replica communication, and sets this database into the availability group SQLAG2019

    SQL script for restoring t-logs and add in the node2

  • Similarly, it restores transaction log backups into the SQLNode3, waits for the replica communication, and sets this database into the availability group SQLAG2019

    SQL script for restoring t-logs and add in the node3

Execute the whole Script in SQLCMD mode and refresh the AG dashboard from the primary replica. We can see two AG databases now – [SQLShackDemo] and [MyNewDB]

View new database in the AG replica

Conclusion

In this article, we explored T-SQL scripts for various SQL Server Always On Availability Group tasks. It is useful for DBAs to understand the overall task flow. We can achieve this using T-SQL scripts.

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
Configure Managed Service Accounts for 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
Configure a SQL Server Always On Availability Group on the domain-independent Failover Cluster
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
The Hub and Spoke model of policy-based management for SQL Server Always On Availability Groups
Custom policies for AG dashboards of SQL Server Always On Availability Groups
Explore dynamic management views for monitoring SQL Server Always On Availability Groups
Dynamic management views for monitoring availability replicas and databases for SQL Server Always On Availability
Configure SQL Server Always On Availability Groups using Windows PowerShell scripts
Configure Integration Services Catalog Database SSISDB in SQL Server Always On Availability Groups
Synchronize logins between Availability replicas in SQL Server Always On Availability Group
Session timeouts in SQL Server Always On Availability Groups
Lease Timeouts and Health Checks in SQL Server Always On Availability Groups
Rajendra Gupta
Latest posts by Rajendra Gupta (see all)
168 Views