This is the 7th article in the series of SQL Server Always on Availability Groups.
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.
ALTER AVAILABILITY GROUP [SQLAG2019] FAILOVER;
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.
/****** Object: AvailabilityReplica [SQLNODE3\INST3] Script Date: 7/22/2020 9:35:26 AM ******/
ALTER AVAILABILITY GROUP [SQLAG2019]
REMOVE REPLICA ON N'SQLNODE3\INST3';
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$]123456789:Connect SQLNODE1\INST1use [master]GOGRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO [MYDEMOSQL\gmsasqlservice$]GO
It connects with the existing secondary replica and assigns the connect permission to the endpoint12345678910111213141516:Connect SQLNODE3\INST3IF (SELECT state FROM sys.endpoints WHERE name = N'Hadr_endpoint') <> 0BEGINALTER ENDPOINT [Hadr_endpoint] STATE = STARTEDENDGOuse [master]GOGRANT 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)12345678910111213141516:Connect SQLNODE3\INST3IF (SELECT state FROM sys.endpoints WHERE name = N'Hadr_endpoint') <> 0BEGINALTER ENDPOINT [Hadr_endpoint] STATE = STARTEDENDGOuse [master]GOGRANT 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 session123456789101112:Connect SQLNODE3\INST3IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='AlwaysOn_health')BEGINALTER EVENT SESSION [AlwaysOn_health] ON SERVER WITH (STARTUP_STATE=ON);ENDIF NOT EXISTS(SELECT * FROM sys.dm_xe_sessions WHERE name='AlwaysOn_health')BEGINALTER EVENT SESSION [AlwaysOn_health] ON SERVER STATE=START;ENDGO
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
12345678910:Connect SQLNODE1\INST1USE [master]GOALTER 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
- 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 group12345:Connect SQLNODE3\INST3ALTER 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 directory1234567891011121314151617181920212223242526272829303132333435:Connect SQLNODE1\INST1USE [master]GOALTER AVAILABILITY GROUP [SQLAG2019]MODIFY REPLICA ON N'SQLNODE2\INST1' WITH (SEEDING_MODE = MANUAL)GOUSE [master]GOALTER AVAILABILITY GROUP [SQLAG2019]MODIFY REPLICA ON N'SQLNODE3\INST3' WITH (SEEDING_MODE = MANUAL)GOUSE [master]GOALTER AVAILABILITY GROUP [SQLAG2019]ADD DATABASE [MyNewDB];GO:Connect SQLNODE1\INST1BACKUP DATABASE [MyNewDB] TO DISK = N'\\SQLNODE1\sql\MyNewDB.bak' WITHCOPY_ONLY, FORMAT, INIT, SKIP, REWIND, NOUNLOAD, COMPRESSION, STATS = 5GO
Restore the full backup from the shared directory into the secondary replica’s SQLNode2 and SQLNode31234567891011:Connect SQLNODE2\INST1RESTORE DATABASE [MyNewDB] FROM DISK = N'\\SQLNODE1\sql\MyNewDB.bak' WITH NORECOVERY, NOUNLOAD, STATS = 5GO:Connect SQLNODE3\INST3RESTORE DATABASE [MyNewDB] FROM DISK = N'\\SQLNODE1\sql\MyNewDB.bak' WITH NORECOVERY, NOUNLOAD, STATS = 5GO
It again connects to the primary replica and takes transaction log backup12345:Connect SQLNODE1\INST1BACKUP LOG [MyNewDB] TO DISK = N'\\SQLNODE1\sql\MyNewDB.trn' WITH NOFORMAT, INIT, NOSKIP, REWIND, NOUNLOAD, COMPRESSION, STATS = 5GO
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]
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
- Direct Query Mode in Power BI for Azure Analysis Services - April 19, 2021
- Azure Analysis Services and Power BI Live connections - April 14, 2021
- An overview of Power BI data models - April 12, 2021