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

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

This article gives an overview of SQL Server Always On Availability Group features. We configured the SQL Server Always On Availability Group from the beginning of building virtual machines.

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.

--YOU MUST EXECUTE THE FOLLOWING SCRIPT IN SQLCMD MODE.

: Connect SQLNODE3 \ INST3 ,1433

: Connect SQLNODE1 \ INST1

ALTER AVAILABILITY GROUP [ SQLAG2019 ] FAILOVER ;

GO

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

alwayson availability groups installation setup and configuration sqlcmd t-sql sql

Bootstrap 5 Complete Course with Examples

Bootstrap 5 Tutorial - Bootstrap 5 Crash Course for Beginners

Nest.JS Tutorial for Beginners

Hello Vue 3: A First Look at Vue 3 and the Composition API

Building a simple Applications with Vue 3

Deno Crash Course: Explore Deno and Create a full REST API with Deno

How to Build a Real-time Chat App with Deno and WebSockets

Convert HTML to Markdown Online

HTML entity encoder decoder Online

Introduction to Structured Query Language SQL pdf

SQL stands for Structured Query Language. SQL is a scripting language expected to store, control, and inquiry information put away in social databases. The main manifestation of SQL showed up in 1974, when a gathering in IBM built up the principal model of a social database. The primary business social database was discharged by Relational Software later turning out to be Oracle.

Configure Managed Service Accounts for SQL Server Always On Availability Groups

This article gives an overview of Configuring Managed Service Accounts for SQL Server Always On Availability Groups.

Install SQL Server 2019 on Windows Server 2016 with SQL Server

In this article, we will discuss configuring SQL Server Always On Availability Groups and perform failover validations. You should go through the following articles and build the infrastructure before proceeding with this article.

An overview of distributed SQL Server Always On Availability Groups

SQL Server 2016 provides a new feature Distributed Availability Group for disaster recovery purposes. It is a particular type of availability group that helps access the multiple failover clusters. In this article’s series, we configured a traditional Always On group. It has the following requirements.

SQL Server Always On Availability Group on the domain-independent Failover Cluster

This article configures SQL Server Always On Availability Groups on the domain independent Windows Failover Clusters. In the previous article, Deploy a domain-independent Windows Failover Cluster for SQL Server Always On Availability Groups, we learned the new capability in Windows Server 2016 to configure a domain-independent Windows failover cluster.