This is the 7^th 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…

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.

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.

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.

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

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.

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

Select the initial data synchronization method.

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$]

  1 2 3 4 5 6 7 8 9

  :Connect SQLNODE1\INST1   use [master]              GO              GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO [MYDEMOSQL\gmsasqlservice$]              GO

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

  1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

  :Connect SQLNODE3\INST3   IF (SELECT state FROM sys.endpoints WHERE name = N'Hadr_endpoint') <> 0 BEGIN     ALTER ENDPOINT [Hadr_endpoint] STATE = STARTED END              GO              use [master]              GO              GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO [MYDEMOSQL\gMSAsqlservice$]              GO

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

  1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

  :Connect SQLNODE3\INST3   IF (SELECT state FROM sys.endpoints WHERE name = N'Hadr_endpoint') <> 0 BEGIN     ALTER ENDPOINT [Hadr_endpoint] STATE = STARTED END              GO              use [master]              GO              GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO [MYDEMOSQL\gMSAsqlservice$]              GO

• 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

  1 2 3 4 5 6 7 8 9 10 11 12

  :Connect SQLNODE3\INST3   IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='AlwaysOn_health') BEGIN   ALTER EVENT SESSION [AlwaysOn_health] ON SERVER WITH (STARTUP_STATE=ON); END IF NOT EXISTS(SELECT * FROM sys.dm_xe_sessions WHERE name='AlwaysOn_health') BEGIN   ALTER EVENT SESSION [AlwaysOn_health] ON SERVER STATE=START; END              GO

• 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
  1 2 3 4 5 6 7 8 9 10

  :Connect SQLNODE1\INST1   USE [master]              GO              ALTER AVAILABILITY GROUP [SQLAG2019] ADD REPLICA ON N'SQLNODE3\INST3' WITH (ENDPOINT_URL = N'TCP://SQLNode3.MyDemoSQL.com:5022', FAILOVER_MODE = MANUAL, AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT, BACKUP_PRIORITY = 50, SECONDARY_ROLE(ALLOW_CONNECTIONS = NO));              GO

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

  1 2 3 4 5

  :Connect SQLNODE3\INST3   ALTER AVAILABILITY GROUP [SQLAG2019] JOIN;              GO

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

  

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

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.

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.

Connect to all existing secondary replicas.

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.

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

  

Generate the Script from the validation page.

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

  1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35

  :Connect SQLNODE1\INST1   USE [master]              GO              ALTER AVAILABILITY GROUP [SQLAG2019] MODIFY REPLICA ON N'SQLNODE2\INST1' WITH (SEEDING_MODE = MANUAL)              GO          USE [master]              GO              ALTER AVAILABILITY GROUP [SQLAG2019] MODIFY REPLICA ON N'SQLNODE3\INST3' WITH (SEEDING_MODE = MANUAL)              GO              USE [master]              GO              ALTER AVAILABILITY GROUP [SQLAG2019] ADD DATABASE [MyNewDB];              GO              :Connect SQLNODE1\INST1              BACKUP DATABASE [MyNewDB] TO  DISK = N'\\SQLNODE1\sql\MyNewDB.bak' WITH   COPY_ONLY, FORMAT, INIT, SKIP, REWIND, NOUNLOAD, COMPRESSION,  STATS = 5              GO

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

  1 2 3 4 5 6 7 8 9 10 11

  :Connect SQLNODE2\INST1   RESTORE DATABASE [MyNewDB] FROM  DISK = N'\\SQLNODE1\sql\MyNewDB.bak' WITH  NORECOVERY,  NOUNLOAD,  STATS = 5              GO              :Connect SQLNODE3\INST3              RESTORE DATABASE [MyNewDB] FROM  DISK = N'\\SQLNODE1\sql\MyNewDB.bak' WITH  NORECOVERY,  NOUNLOAD,  STATS = 5              GO

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

  1 2 3 4 5

  :Connect SQLNODE1\INST1   BACKUP LOG [MyNewDB] TO  DISK = N'\\SQLNODE1\sql\MyNewDB.trn' WITH NOFORMAT, INIT, NOSKIP, REWIND, NOUNLOAD, COMPRESSION,  STATS = 5              GO

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

  

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

  

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]

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

• Author
• Recent Posts

Rajendra Gupta

Hi! I am Rajendra Gupta, Database Specialist and Architect, helping organizations implement Microsoft SQL Server, Azure, Couchbase, AWS solutions fast and efficiently, fix related issues, and Performance Tuning with over 14 years of experience.

I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.

I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.

Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.

Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com

View all posts by Rajendra Gupta

Latest posts by Rajendra Gupta (see all)

• Understanding PostgreSQL SUBSTRING function - September 21, 2024
• How to install PostgreSQL on Ubuntu - July 13, 2023
• How to use the CROSSTAB function in PostgreSQL - February 17, 2023