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…

It inserts a VirtualBox Guest Additions CD in respective VM.

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

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

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

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

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

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.

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.

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

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

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

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.

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.

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.

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