Deploying an Always On availability group with an internal network load balancer

Written by

Updated at March 7, 2025

Get your cloud ready
- Required paid resources
Create a network infrastructure
Create an internal network load balancer
- Create a listener
- Create and connect the target group to the network load balancer
Prepare VMs for the availability group
Test the availability group
How to delete the resources you created

Warning

In Yandex Cloud, you can only use Microsoft products with your own licenses and on dedicated hosts. For more information, see Use of personal licenses for Microsoft products.

This tutorial will tell you how to deploy an Always On availability group in Yandex Cloud and enable load balancing across the nodes using an internal network load balancer. You will set up network interfaces to combine multiple subnets into a single common subnet. Therefore, you do not need Multisubnet Failover. It is the replica to where you will be writing that will get the primary IP address. The same replica will have an open port, and the load balancer will route traffic to this port. Since the port specified for connecting to the load balancer becomes unavailable, you will use an additional non-standard port to receive traffic.

To set up an Always On availability group with an internal network load balancer:

If you no longer need the resources you created, delete them.

Get your cloud ready

Go to the management console and log in to Yandex Cloud or create an account if you do not have one yet.
On the Yandex Cloud Billing page, make sure you have a billing account linked and it has the ACTIVE or TRIAL_ACTIVE status. If you do not have a billing account, create one.

If you have an active billing account, you can go to the cloud page to create or select a folder for your infrastructure to operate in.

Learn more about clouds and folders.

Note

Make sure that the billing account contains user details required to meet the Microsoft licensing policy requirements. You can launch the product only if you have these details.

Required paid resources

The cost of supporting the availability group includes:

Fee for a continuously running VM (see Yandex Compute Cloud pricing).
Fee for using a network load balancer (see Yandex Network Load Balancer pricing).
Fee for using a dynamic or static public IP address (see Yandex Virtual Private Cloud pricing).

You can use license mobility and bring your own SQL Server license to Yandex Cloud.

Create a network infrastructure

Prepare the network infrastructure to host the availability group.

Create a network named ya-network:
Management console

Bash

PowerShell
1. Open the Virtual Private Cloud section of the folder where you want to create a cloud network.
2. Click Create network.
3. Enter the network name: ya-network.
4. Click Create network.
If you do not have the Yandex Cloud CLI yet, install and initialize it.

The folder specified in the CLI profile is used by default. You can specify a different folder through the --folder-name or --folder-id parameter.
```
yc vpc network create --name ya-network
```
Install the Yandex Cloud command line interface to use CLI commands in PowerShell.
```
yc vpc network create --name ya-network
```

Create subnets that will host your VMs and network load balancer:

Three subnets to host SQLServer VMs: ya-sqlserver-rc1a, ya-sqlserver-rc1b, and ya-sqlserver-rc1d. Each subnet will be linked to the mssql route table.
ya-ilb-rc1a subnet for the network load balancer.
ya-ad-rc1a subnet for Active Directory.

Management console

Bash

PowerShell

Open the Virtual Private Cloud section in the folder where you want to create a subnet.
Select the ya-network network.
Click Add subnet.
Fill out the form: enter ya-sqlserver-rc1a as the subnet name and select the ru-central1-a availability zone from the drop-down list.
Enter the subnet CIDR: IP address and subnet mask: 192.168.1.0/28.
Click Create subnet.

Repeat these steps for the subnets with the following names and CIDR:

ya-sqlserver-rc1b in the ru-central1-b availability zone: 192.168.1.16/28.
ya-sqlserver-rc1d in the ru-central1-d availability zone: 192.168.1.32/28.
ya-ilb-rc1a in the ru-central1-a availability zone: 192.168.1.48/28.
ya-ad-rc1a in the ru-central1-a availability zone: 10.0.0.0/28.

yc vpc subnet create \
   --name ya-sqlserver-rc1a \
   --zone ru-central1-a \
   --range 192.168.1.0/28 \
   --network-name ya-network

yc vpc subnet create \
  --name ya-sqlserver-rc1b \
  --zone ru-central1-b \
  --range 192.168.1.16/28 \
  --network-name ya-network

yc vpc subnet create \
   --name ya-sqlserver-rc1d \
   --zone ru-central1-d \
   --range 192.168.1.32/28 \
   --network-name ya-network

yc vpc subnet create \
   --name ya-ilb-rc1a \
   --zone ru-central1-a \
   --range 192.168.1.48/28 \
   --network-name ya-network

yc vpc subnet create \
  --name ya-ad-rc1a \
  --zone ru-central1-a \
  --range 10.0.0.0/28 \
  --network-name ya-network

yc vpc subnet create `
   --name ya-sqlserver-rc1a `
   --zone ru-central1-a `
   --range 192.168.1.0/28 `
   --network-name ya-network

yc vpc subnet create `
   --name ya-sqlserver-rc1b `
   --zone ru-central1-b `
   --range 192.168.1.16/28 `
   --network-name ya-network

yc vpc subnet create `
   --name ya-sqlserver-rc1d `
   --zone ru-central1-d `
   --range 192.168.1.32/28 `
   --network-name ya-network

yc vpc subnet create `
   --name ya-ilb-rc1a `
   --zone ru-central1-a `
   --range 192.168.1.48/28 `
   --network-name ya-network

yc vpc subnet create `
   --name ya-ad-rc1a `
   --zone ru-central1-a `
   --range 10.0.0.0/28 `
   --network-name ya-network

Create an internal network load balancer

Bash

PowerShell

yc load-balancer network-load-balancer create \
   --name ya-loadbalancer \
   --type internal

yc load-balancer network-load-balancer create `
   --name ya-loadbalancer `
   --type internal

Create a listener

Bash

PowerShell

Get the subnet ID:

yc vpc subnet get --name ya-ilb-rc1a

Create a listener and specify the subnet ID:

yc load-balancer network-load-balancer add-listener \
   --name ya-loadbalancer \
   --listener name=ya-listener,port=1433,target-port=14333,protocol=tcp,internal-address=192.168.1.62,internal-subnet-id=<subnet_ID>

$inlbSubnet = yc vpc subnet get `
   --name ya-ilb-rc1a `
   --format json | ConvertFrom-Json

yc load-balancer network-load-balancer add-listener `
   --name ya-loadbalancer `
   --listener name=ya-listener,port=1433,target-port=14333,protocol=tcp,internal-address=192.168.1.62,internal-subnet-id=$($inlbSubnet.id)

Create and connect the target group to the network load balancer

Bash

PowerShell

yc load-balancer target-group create \
   --name ya-tg \
   --target address=192.168.1.3,subnet-name=ya-sqlserver-rc1a \
   --target address=192.168.1.19,subnet-name=ya-sqlserver-rc1b \
   --target address=192.168.1.35,subnet-name=ya-sqlserver-rc1d

Copy the target group ID from the response and run this command:

yc load-balancer network-load-balancer attach-target-group \
   --name ya-loadbalancer \
   --target-group target-group-id=<target_group_ID>,healthcheck-name=listener,healthcheck-tcp-port=59999

yc load-balancer target-group create `
   --name ya-tg `
   --target address=192.168.1.3,subnet-name=ya-sqlserver-rc1a `
   --target address=192.168.1.19,subnet-name=ya-sqlserver-rc1b `
   --target address=192.168.1.35,subnet-name=ya-sqlserver-rc1d

$TargetGroup = yc load-balancer target-group get `
   --name ya-tg `
   --format json | ConvertFrom-Json

yc load-balancer network-load-balancer attach-target-group `
   --name ya-loadbalancer `
   --target-group target-group-id=$($TargetGroup.id),healthcheck-name=listener,healthcheck-tcp-port=59999

Prepare VMs for the availability group

Prepare Windows Server images

Before creating VMs, prepare a Windows Server image to use in Yandex Cloud with your own license.

Create a file with administrator credentials

Create a file named setpass with a script to set the password for the local administrator account. This script will be executed when you create VMs via the CLI.

Alert

The setpass file must be UTF-8 encoded.

Bash

PowerShell

touch ~/setpass

echo '#ps1' >> ~/setpass

echo 'Get-LocalUser | Where-Object SID -like *-500 | Set-LocalUser -Password (ConvertTo-SecureString "YaQWErty123" -AsPlainText -Force)' >> ~/setpass
cd

ni ~/setpass

echo '#ps1' >> ~/setpass

echo 'Get-LocalUser | Where-Object SID -like *-500 | Set-LocalUser -Password (ConvertTo-SecureString "YaQWErty123" -AsPlainText -Force)' >> ~/setpass

Warning

The password provided above is for testing only. Use your own complex password when deploying a cluster to work in a product environment.

The password must meet the complexity requirements.

You can read more about the best practices for securing Active Directory on the MS official website.

Create VMs

Make sure to create your VMs on dedicated hosts. You can get the dedicated host ID in the Yandex Cloud CLI by running the yc compute host-group list-hosts command. To learn more about this command, see this reference.

Create a VM for a bastion host

Create a bastion host with Windows Server 2022 Datacenter and a public IP address to access other VMs:

Bash

PowerShell

yc compute instance create \
   --name ya-jump1 \
   --hostname ya-jump1 \
   --zone ru-central1-a \
   --memory 4 \
   --cores 2 \
   --metadata-from-file user-data=setpass \
   --create-boot-disk \
     type=network-ssd,size=50,image-id=<Windows_image_ID> \
   --network-interface \
     subnet-name=ya-ad-rc1a,nat-ip-version=ipv4 \
   --host-id <dedicated_host_ID> \
   --async

Note

The commands yc compute instance create | create-with-container | update | add-metadata support substitution of environment variable values into VM metadata. When you execute a Yandex Cloud CLI command, these values, specified in the user-data key in $<variable_name> format, will be substituted into the VM metadata from the environment variables of the environment the command is executed in.

To change such behavior, i.e. to provide a variable name to the VM metadata in $<variable_name> format rather than take the variable value from the CLI command runtime environment, use the two-dollar syntax, e.g., $$<variable_name>.

For more information, see Specifics of providing environment variables in metadata via the CLI.

yc compute instance create `
   --name ya-jump1 `
   --hostname ya-jump1 `
   --zone ru-central1-a `
   --memory 4 `
   --cores 2 `
   --metadata-from-file user-data=setpass `
   --create-boot-disk `
     type=network-ssd,size=50,image-id=<Windows_image_ID> `
   --network-interface `
     subnet-name=ya-ad-rc1a,nat-ip-version=ipv4 `
   --host-id <dedicated_host_ID> `
   --async

Note

For more information, see Specifics of providing environment variables in metadata via the CLI.

Create a VM for Active Directory

Bash

PowerShell

yc compute instance create \
   --name ya-ad \
   --hostname ya-ad \
   --zone ru-central1-a \
   --memory 6 \
   --cores 2 \
   --metadata-from-file user-data=setpass \
   --create-boot-disk \
     type=network-ssd,size=50,image-id=<Windows_image_ID> \
   --network-interface \
     subnet-name=ya-ad-rc1a,ipv4-address=10.0.0.3 \
   --host-id <dedicated_host_ID> \
   --async

yc compute instance create `
   --name ya-ad `
   --hostname ya-ad `
   --zone ru-central1-a `
   --memory 6 `
   --cores 2 `
   --metadata-from-file user-data=setpass `
   --create-boot-disk `
     type=network-ssd,size=50,image-id=<Windows_image_ID> `
   --network-interface `
     subnet-name=ya-ad-rc1a,ipv4-address=10.0.0.3 `
   --host-id <dedicated_host_ID> `
   --async

Create VM instances for SQL Server

Create three VMs with Windows Server 2022 Datacenter for SQL Server:

Bash

PowerShell

yc compute instance create \
   --name ya-mssql1 \
   --hostname ya-mssql1 \
   --zone ru-central1-a \
   --memory 16 \
   --cores 4 \
   --metadata-from-file user-data=setpass \
   --create-boot-disk \
     type=network-ssd,size=50,image-id=<Windows_image_ID> \
   --create-disk \
     type=network-ssd,size=200 \
   --network-interface \
     subnet-name=ya-sqlserver-rc1a,ipv4-address=192.168.1.3 \
   --host-id <dedicated_host_ID> \
   --async

yc compute instance create \
   --name ya-mssql2 \
   --hostname ya-mssql2 \
   --zone ru-central1-b \
   --memory 16 \
   --cores 4 \
   --metadata-from-file user-data=setpass \
   --create-boot-disk \
     type=network-ssd,size=50,image-id=<Windows_image_ID> \
   --create-disk \
     type=network-ssd,size=200 \
   --network-interface \
     subnet-name=ya-sqlserver-rc1b,ipv4-address=192.168.1.19 \
   --host-id <dedicated_host_ID> \
   --async

yc compute instance create \
   --name ya-mssql3 \
   --hostname ya-mssql3 \
   --zone ru-central1-d \
   --memory 16 \
   --cores 4 \
   --metadata-from-file user-data=setpass \
   --create-boot-disk \
     type=network-ssd,size=50,image-id=<Windows_image_ID> \
   --create-disk \
     type=network-ssd,size=200 \
   --network-interface \
     subnet-name=ya-sqlserver-rc1d,ipv4-address=192.168.1.35 \
   --host-id <dedicated_host_ID> \
   --async

yc compute instance create `
   --name ya-mssql1 `
   --hostname ya-mssql1 `
   --zone ru-central1-a `
   --memory 16 `
   --cores 4 `
   --metadata-from-file user-data=setpass `
   --create-boot-disk `
     type=network-ssd,size=50,image-id=<Windows_image_ID> `
   --create-disk `
     type=network-ssd,size=200 `
   --network-interface `
     subnet-name=ya-sqlserver-rc1a,ipv4-address=192.168.1.3 `
   --host-id <dedicated_host_ID> `
   --async

yc compute instance create `
   --name ya-mssql2 `
   --hostname ya-mssql2 `
   --zone ru-central1-b `
   --memory 16 `
   --cores 4 `
   --metadata-from-file user-data=setpass `
   --create-boot-disk `
     type=network-ssd,size=50,image-id=<Windows_image_ID> `
   --create-disk `
     type=network-ssd,size=200 `
   --network-interface `
     subnet-name=ya-sqlserver-rc1b,ipv4-address=192.168.1.19 `
   --host-id <dedicated_host_ID> `
   --async

yc compute instance create `
   --name ya-mssql3 `
   --hostname ya-mssql3 `
   --zone ru-central1-d `
   --memory 16 `
   --cores 4 `
   --metadata-from-file user-data=setpass `
   --create-boot-disk `
     type=network-ssd,size=50,image-id=<Windows_image_ID> `
   --create-disk `
     type=network-ssd,size=200 `
   --network-interface `
     subnet-name=ya-sqlserver-rc1d,ipv4-address=192.168.1.35 `
   --host-id <dedicated_host_ID> `
   --async

Bring your own Windows Server licenses

Connect to each VM you created and activate your own Windows Server license on these VMs.

Install and configure Active Directory

Connect to ya-jump1 through RDP. Use Administrator as the username and your password.
From ya-jump1, connect to the ya-ad VM under the same account using RDP.
On ya-ad, run PowerShell and set the required server roles:
PowerShell
```
Install-WindowsFeature AD-Domain-Services -IncludeManagementTools
```

Create an Active Directory forest:

PowerShell

Install-ADDSForest `
  -DomainName 'yantoso.net' `
  -Force:$true `
  -SafeModeAdministratorPassword `
    ('YaP@ssw0rd!11' | ConvertTo-SecureString -AsPlainText -Force)

After that, the VM will restart.

Reconnect to ya-ad.

Rename the website and add to it the subnets you created:

PowerShell

Get-ADReplicationSite 'Default-First-Site-Name' | Rename-ADObject -NewName 'ru-central1'
New-ADReplicationSubnet -Name '10.0.0.0/28'  -Site 'ru-central1'
New-ADReplicationSubnet -Name '192.168.1.0/28'  -Site 'ru-central1'
New-ADReplicationSubnet -Name '192.168.1.16/28' -Site 'ru-central1'
New-ADReplicationSubnet -Name '192.168.1.32/28' -Site 'ru-central1'
New-ADReplicationSubnet -Name '192.168.1.48/28' -Site 'ru-central1'

Specify the forwarder for the DNS server:
PowerShell
```
Set-DnsServerForwarder '10.0.0.2'
```

Specify the DNS server addresses:

PowerShell

Get-NetAdapter | Set-DnsClientServerAddress -ServerAddresses "10.0.0.3,127.0.0.1"

Create users and groups in Active Directory

Connect to ya-jump1 through RDP. Use Administrator as the username and your password.
Connect to the ya-ad VM from ya-jump1 under the same account using RDP.

On ya-ad, run PowerShell and create the mssql-svc service account:

PowerShell

New-ADUser `
  -Name mssql-svc `
  -AccountPassword ('YaQWErty123' | ConvertTo-SecureString -AsPlainText -Force) `
  -CannotChangePassword $true `
  -PasswordNeverExpires $true `
  -Enabled $true

Create groups to access backups and DB servers:

PowerShell

New-AdGroup mssql-admins-grp -GroupScope:Global
New-AdGroup mssql-backups-grp -GroupScope:Global

Add the Administrator account to all groups. Add the mssql-svc service account to the mssql-backups-grp group:

PowerShell

Add-ADGroupMember mssql-admins-grp -Members Administrator
Add-ADGroupMember mssql-backups-grp -Members Administrator
Add-ADGroupMember mssql-backups-grp -Members mssql-svc

Set the service account SPN:

PowerShell

setspn -A MSSQLSvc/ya-mssql1.yantoso.net:1433 yantoso\mssql-svc
setspn -A MSSQLSvc/ya-mssql1.yantoso.net yantoso\mssql-svc

setspn -A MSSQLSvc/ya-mssql2.yantoso.net:1433 yantoso\mssql-svc
setspn -A MSSQLSvc/ya-mssql2.yantoso.net yantoso\mssql-svc

setspn -A MSSQLSvc/ya-mssql3.yantoso.net:1433 yantoso\mssql-svc
setspn -A MSSQLSvc/ya-mssql3.yantoso.net yantoso\mssql-svc

Install and configure SQL Server

Install SQL Server on your database servers:

Configure internet access on the VMs with DB servers:

Bash

PowerShell

yc compute instance add-one-to-one-nat <ya-mssql1_VM_ID> --network-interface-index 0
yc compute instance add-one-to-one-nat <ya-mssql2_VM_ID> --network-interface-index 0
yc compute instance add-one-to-one-nat <ya-mssql3_VM_ID> --network-interface-index 0

yc compute instance add-one-to-one-nat <ya-mssql1_VM_ID> --network-interface-index 0
yc compute instance add-one-to-one-nat <ya-mssql2_VM_ID> --network-interface-index 0
yc compute instance add-one-to-one-nat <ya-mssql3_VM_ID> --network-interface-index 0

Run RDP and connect to the ya-mssql1 VM using the Administrator account and your password. To connect, use the public IP address of the VM.

Start PowerShell and set the role:

PowerShell

Install-WindowsFeature Failover-Clustering -IncludeManagementTools

Restart the VM.
Reconnect to the ya-mssql1 VM and run PowerShell.

Initialize and format the second logical disk:

PowerShell

Get-Disk | `
  Where-Object PartitionStyle -Eq "RAW" | `
    Initialize-Disk -PassThru -PartitionStyle:GPT | `
      New-Partition -DriveLetter 'X' -UseMaximumSize | `
        Format-Volume `
           -FileSystem:NTFS `
           -AllocationUnitSize:64KB `
           -Force `
           -ShortFileNameSupport $false `
           -Confirm:$false

A message will appear asking you to confirm disk formatting. Click Format disk. Click Start. After formatting is complete, click OK.

Create folders for the distribution kit, backups and storage for databases, logs, and temporary files:
PowerShell
```
mkdir C:\dist
mkdir X:\BACKUP
mkdir X:\DB
mkdir X:\DBLOG
mkdir X:\TEMPDB
mkdir X:\TEMPDBLOG
```
Download the SQL Server 2022 (English) image from the web and save it to C:\dist.
Install the SqlServer module:
PowerShell
```
Install-Module -Name SqlServer
```
When prompted to confirm the installation, enter Y.
Import the SqlServer module commands for PowerShell:
PowerShell
```
Import-Module SQLServer
```

Specify the DNS server address:

PowerShell

Get-NetAdapter | Set-DnsClientServerAddress -ServerAddresses "10.0.0.3"

Prepare the data for accessing the domain:

PowerShell

$domain_credential = `
  New-Object System.Management.Automation.PSCredential (
      'yantoso\Administrator', `
      ('YaQWErty123' | ConvertTo-SecureString -AsPlainText -Force))

Add the DB server to the domain:

PowerShell

Add-Computer -DomainCredential $domain_credential -DomainName 'yantoso.net' -Restart -Force

The VM will restart automatically.

Following that, reconnect to the VM with the yantoso\Administrator username and open PowerShell.

Configure the required permissions for the service account:

PowerShell

& secedit /export /cfg sec_conf_export.ini /areas user_rights

$secConfig = Get-Content sec_conf_export.ini | Select-Object -SkipLast 3
$versionSection = Get-Content sec_conf_export.ini | Select-Object -Last 3

$SID = Get-WmiObject `
  -Class Win32_UserAccount `
  -Filter "name='mssql-svc' and domain='yantoso'" | `
    Select-Object -ExpandProperty SID

$isSeManageVolumePrivilegeDefined = $secConfig | `
  Select-String SeManageVolumePrivilege

if ($isSeManageVolumePrivilegeDefined) {
  $secConfig = $secConfig -replace '^SeManageVolumePrivilege .+', "`$0,*$SID"
} else {
  $secConfig = $secConfig + "SeManageVolumePrivilege = *$SID"
}

$isSeLockMemoryPrivilegeDefined = $secConfig | `
  Select-String SeLockMemoryPrivilege

if ($isSeLockMemoryPrivilegeDefined) {
  $secConfig = $secConfig -replace '^SeLockMemoryPrivilege .+', "`$0,*$SID"
} else {
  $secConfig = $secConfig + "SeLockMemoryPrivilege = *$SID"
}

$secConfig = $secConfig + $versionSection
$secConfig | Set-Content sec_conf_import.ini

secedit /configure /db secedit.sdb /cfg sec_conf_import.ini /areas user_rights

Remove-Item sec_conf_import.ini
Remove-Item sec_conf_export.ini

Set up a firewall:

PowerShell

New-NetFirewallRule `
  -Group "MSSQL" `
  -DisplayName "SQL Server Default" `
  -Name "MSSQLServer-In-TCP" `
  -LocalPort 1433 `
  -Action "Allow" `
  -Protocol "TCP"

New-NetFirewallRule `
  -Group "MSSQL" `
  -DisplayName "SQL Server AAG Custom" `
  -Name "MSSQLAAG-In-TCP" `
  -LocalPort 14333 `
  -Action "Allow" `
  -Protocol "TCP"

New-NetFirewallRule `
  -Group "MSSQL" `
  -DisplayName "MSSQL HADR Default" `
  -Name "MSSQLHADR-In-TCP" `
  -LocalPort 5022 `
  -Action "Allow" `
  -Protocol "TCP"

New-NetFirewallRule `
  -Group "MSSQL" `
  -DisplayName "MSSQL NLB Probe" `
  -Name "MSSQLAAG-NLB-In-TCP" `
  -LocalPort 59999 `
  -Action "Allow" `
  -Protocol "TCP"

Install SQL Server. Mount an image, run the installation, and detach the image:

PowerShell

Mount-DiskImage -ImagePath C:\dist\<SQL_Server_image_name>.iso

& D:\setup.exe /QUIET /INDICATEPROGRESS /IACCEPTSQLSERVERLICENSETERMS `
  /ACTION=INSTALL /FEATURES=SQLENGINE /INSTANCENAME=MSSQLSERVER `
  /SQLSVCACCOUNT="yantoso\mssql-svc" /SQLSVCPASSWORD="YaQWErty123" `
  /SQLSYSADMINACCOUNTS="yantoso\mssql-admins-grp" /UpdateEnabled=FALSE `
  /SQLBACKUPDIR="X:\BACKUP" /SQLTEMPDBDIR="X:\TEMPDB" /SQLTEMPDBLOGDIR="X:\TEMPDBLOG" `
  /SQLUSERDBDIR="X:\DB" /SQLUSERDBLOGDIR="X:\DBLOG"

Dismount-DiskImage -ImagePath C:\dist\<MSSQL_Server_image_name>.iso

Repeat steps 2-16 for the ya-mssql2 and ya-mssql3 VMs.

Disable internet access for the VM:

Bash

PowerShell

yc compute instance remove-one-to-one-nat <ya-mssql1_VM_ID> --network-interface-index 0
yc compute instance remove-one-to-one-nat <ya-mssql2_VM_ID> --network-interface-index 0
yc compute instance remove-one-to-one-nat <ya-mssql3_VM_ID> --network-interface-index 0

yc compute instance remove-one-to-one-nat <ya-mssql1_VM_ID> --network-interface-index 0
yc compute instance remove-one-to-one-nat <ya-mssql2_VM_ID> --network-interface-index 0
yc compute instance remove-one-to-one-nat <ya-mssql3_VM_ID> --network-interface-index 0

Connect to ya-jump1 through RDP. Use Administrator as the username and your password.

Connect to the ya-mssql1 VM from ya-jump1 under the same account using RDP. Configure a static IP address with its own subnet mask:

$IPAddress = Get-NetAdapter | Get-NetIPAddress -AddressFamily IPv4 | Select-Object -ExpandProperty IPAddress
$InterfaceName = Get-NetAdapter | Select-Object -ExpandProperty Name
$Gateway = Get-NetIPConfiguration | Select-Object -ExpandProperty IPv4DefaultGateway | Select-Object -ExpandProperty NextHop

netsh interface ip set address $InterfaceName static $IPAddress 255.255.255.192 $Gateway

Repeat steps 19-20 for the ya-mssql2 and ya-mssql3 VMs.
The Always On availability group requires a configured Windows Server failover cluster. To create one, you need to test the DB servers. On any of the cluster VMs, run:
PowerShell
```
Test-Cluster -Node 'ya-mssql1.yantoso.net'
Test-Cluster -Node 'ya-mssql2.yantoso.net'
Test-Cluster -Node 'ya-mssql3.yantoso.net'
```

Create a Windows Server failover cluster

Connect to ya-jump1 through RDP. Use Administrator as the username and your password.
Connect to the ya-mssql1 VM from ya-jump1 using RDP as yantoso\Administrator.

Create a cluster of three DB servers:

PowerShell

New-Cluster `
  -Name 'wsfc' `
  -Node 'ya-mssql1.yantoso.net','ya-mssql2.yantoso.net','ya-mssql3.yantoso.net' `
  -NoStorage `
  -StaticAddress 192.168.1.4

Test-Cluster

Get-ClusterResource -Name 'Cluster IP Address' | `
  Stop-ClusterResource

Get-ClusterResource -Name 'Cluster IP Address' | `
  Set-ClusterParameter -Multiple `
    @{
      "Address"="169.254.1.1";
      "SubnetMask"="255.255.255.255";
      "OverrideAddressMatch"=1;
      "EnableDhcp"=0
    }

Get-ClusterResource -Name 'Cluster Name' | Start-ClusterResource -Wait 60

Enable TCP/IP on all VMs and add port 14333 to receive traffic:

PowerShell

[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")
[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlWmiManagement")

$nodes = @('ya-mssql1.yantoso.net','ya-mssql2.yantoso.net','ya-mssql3.yantoso.net')

foreach ($node in $nodes) {
  $smo = [Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer]::new($node)
  $np = $smo.GetSmoObject("ManagedComputer[@Name=`'$node`']/ServerInstance[@Name='MSSQLSERVER']/ServerProtocol[@Name='NP']")
  $tcp = $smo.GetSmoObject("ManagedComputer[@Name=`'$node`']/ServerInstance[@Name='MSSQLSERVER']/ServerProtocol[@Name='Tcp']")
  $ipall = $smo.GetSmoObject("ManagedComputer[@Name=`'$node`']/ServerInstance[@Name='MSSQLSERVER']/ServerProtocol[@Name='Tcp']/IPAddress[@Name='IPAll']")

  if (-not $ipall.IPAddressProperties['TcpPort'].Value -ne '1433,14333') {
    $ipall.IPAddressProperties['TcpPort'].Value = '1433,14333'
    $tcp.Alter()
    $altered = $true
  }

  if (-not $tcp.IsEnabled) {
    $tcp.IsEnabled = $true
    $tcp.Alter()
    $altered = $true
  }
  if (-not $np.IsEnabled) {
    $np.IsEnabled = $true
    $np.Alter()
    $altered = $true
  }

  if ($altered) {
    Get-Service -Name 'MSSQLSERVER' -ComputerName $node | Restart-Service -Force
    Start-Sleep -Seconds 60
  }
}

Grant server management permissions to the mssql-svc user:

PowerShell

Add-SqlLogin -Path "SQLSERVER:\SQL\ya-mssql1\Default" `
  -LoginName "yantoso\mssql-svc" `
  -LoginType "WindowsUser" `
  -Enable `
  -GrantConnectSql

Add-SqlLogin -Path "SQLSERVER:\SQL\ya-mssql2\Default" `
  -LoginName "yantoso\mssql-svc" `
  -LoginType "WindowsUser" `
  -Enable `
  -GrantConnectSql

Add-SqlLogin -Path "SQLSERVER:\SQL\ya-mssql3\Default" `
  -LoginName "yantoso\mssql-svc" `
  -LoginType "WindowsUser" `
  -Enable `
  -GrantConnectSql

$mssql1 = Get-Item "SQLSERVER:\SQL\ya-mssql1.yantoso.net\Default"
$mssql1.Roles['sysadmin'].AddMember('yantoso\mssql-svc')
$mssql1.Roles['sysadmin'].Alter()

$mssql2 = Get-Item "SQLSERVER:\SQL\ya-mssql2.yantoso.net\Default"
$mssql2.Roles['sysadmin'].AddMember('yantoso\mssql-svc')
$mssql2.Roles['sysadmin'].Alter()

$mssql3 = Get-Item "SQLSERVER:\SQL\ya-mssql3.yantoso.net\Default"
$mssql3.Roles['sysadmin'].AddMember('yantoso\mssql-svc')
$mssql3.Roles['sysadmin'].Alter()

Configure Always On

Connect to each server one by one and enable SqlAlwaysOn:
1. Connect to the Windows Server failover cluster (WSFC) node that hosts the SQL Server instance.
2. Open the Start menu and select All programs → Microsoft SQL Server → Configuration Tools → SQL Server Configuration Manager.
3. In the SQL Server Configuration Manager, right-click the SQL Server instance for which you need to enable Always On Availability Groups, and select Properties.
4. Go to the Always On High Availability tab.
5. Select Enable Always On Availability Groups and restart the SQL Server instance service.

Create and start HADR endpoints:

PowerShell

New-SqlHADREndpoint -Port 5022 -Owner sa `
 -Encryption Supported -EncryptionAlgorithm Aes `
 -Name AlwaysonEndpoint `
 -Path "SQLSERVER:\SQL\ya-mssql1.yantoso.net\Default"

Set-SqlHADREndpoint -Path "SQLSERVER:\SQL\ya-mssql1.yantoso.net\Default\Endpoints\AlwaysonEndpoint" -State Started

New-SqlHADREndpoint -Port 5022 -Owner sa `
  -Encryption Supported -EncryptionAlgorithm Aes `
  -Name AlwaysonEndpoint `
  -Path "SQLSERVER:\SQL\ya-mssql2.yantoso.net\Default"

Set-SqlHADREndpoint -Path "SQLSERVER:\SQL\ya-mssql2.yantoso.net\Default\Endpoints\AlwaysonEndpoint" -State Started

New-SqlHADREndpoint -Port 5022 -Owner sa `
  -Encryption Supported -EncryptionAlgorithm Aes `
  -Name AlwaysonEndpoint `
  -Path "SQLSERVER:\SQL\ya-mssql3.yantoso.net\Default"

Set-SqlHADREndpoint -Path "SQLSERVER:\SQL\ya-mssql3.yantoso.net\Default\Endpoints\AlwaysonEndpoint" -State Started

Create variables with replica parameters. The main replica is ya-mssql1; the second and third replicas are ya-mssql2 and ya-mssql3, respectively.

PowerShell

$PrimaryReplica = New-SqlAvailabilityReplica `
 -Name ya-mssql1 `
 -EndpointUrl "TCP://ya-mssql1.yantoso.net:5022" `
 -FailoverMode "Automatic" `
 -AvailabilityMode "SynchronousCommit" `
 -AsTemplate -Version 13

$SecondaryReplica = New-SqlAvailabilityReplica `
  -Name ya-mssql2 `
  -EndpointUrl "TCP://ya-mssql2.yantoso.net:5022" `
  -FailoverMode "Automatic" `
  -AvailabilityMode "SynchronousCommit" `
  -AsTemplate -Version 13

$ThirdReplica = New-SqlAvailabilityReplica `
  -Name ya-mssql3 `
  -EndpointUrl "TCP://ya-mssql3.yantoso.net:5022" `
  -FailoverMode "Automatic" `
  -AvailabilityMode "SynchronousCommit" `
  -AsTemplate -Version 13

Create a MyAG availability group of replicas and add the first server to it:

PowerShell

New-SqlAvailabilityGroup `
    -Name 'MyAG' `
    -AvailabilityReplica @($PrimaryReplica, $SecondaryReplica, $ThirdReplica) `
    -Path "SQLSERVER:\SQL\ya-mssql1.yantoso.net\Default"

Add the remaining servers to the availability group:

PowerShell

Join-SqlAvailabilityGroup -Path "SQLSERVER:\SQL\ya-mssql2.yantoso.net\Default" -Name 'MyAG'
Join-SqlAvailabilityGroup -Path "SQLSERVER:\SQL\ya-mssql3.yantoso.net\Default" -Name 'MyAG'

Create a listener with the 192.168.1.62 IP address, which belongs to the internal network load balancer:

PowerShell

$NLBIPAddress = '192.168.1.62'

Get-Cluster | `
  Add-ClusterResource -Name 'MyAG Network Name' -Group 'MyAG' -ResourceType 'Network Name'
Get-ClusterResource -Name 'MyAG Network Name' | `
  Set-ClusterParameter -Multiple `
    @{
      Name = 'MyAGlistener'
      DnsName = 'MyAGlistener'
    }

Get-Cluster | `
  Add-ClusterResource -Name 'My AG listener IP Address' -Group 'MyAG' -ResourceType 'IP Address'
Get-ClusterResource -Name 'My AG listener IP Address' | `
  Set-ClusterParameter -Multiple `
    @{
      Address=$NLBIPAddress
      ProbePort=59999
      SubnetMask="255.255.255.255"
      Network="Cluster Network 1"
      EnableDhcp=0
    }

Add-ClusterResourceDependency `
  -Resource 'MyAG Network Name' `
  -Provider 'My AG listener IP Address'

Stop-ClusterResource 'MyAG'

Add-ClusterResourceDependency `
  -Resource 'MyAG' `
  -Provider 'MyAG Network Name'

Start-ClusterResource 'MyAG'

Assign the listener port 14333:

PowerShell

Set-SqlAvailabilityGroupListener `
  -Port 14333 `
  -Path "SQLSERVER:\Sql\ya-mssql1.yantoso.net\Default\AvailabilityGroups\MyAG\AvailabilityGroupListeners\MyAGlistener"

Open port 14333 on all VMs in the cluster:

PowerShell

$nodes = @('ya-mssql1.yantoso.net','ya-mssql2.yantoso.net','ya-mssql3.yantoso.net')

foreach ($node in $nodes) {
  $smo = [Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer]::new($node)
  $tcp = $smo.GetSmoObject("ManagedComputer[@Name=`'$node`']/ServerInstance[@Name='MSSQLSERVER']/ServerProtocol[@Name='Tcp']")
  $np = $smo.GetSmoObject("ManagedComputer[@Name=`'$node`']/ServerInstance[@Name='MSSQLSERVER']/ServerProtocol[@Name='NP']")
  $ipall = $smo.GetSmoObject("ManagedComputer[@Name=`'$node`']/ServerInstance[@Name='MSSQLSERVER']/ServerProtocol[@Name='Tcp']/IPAddress[@Name='IPAll']")

  if (-not $ipall.IPAddressProperties['TcpPort'].Value -ne '1433,14333') {
    $ipall.IPAddressProperties['TcpPort'].Value = '1433,14333'
    $tcp.Alter()
    $altered = $true
  }

  if (-not $tcp.IsEnabled) {
    $tcp.IsEnabled = $true
    $tcp.Alter()
    $altered = $true

  }
  if (-not $np.IsEnabled) {
    $np.IsEnabled = $true
    $np.Alter()
    $altered = $true
  }

  if ($altered) {
    Get-Service -Name 'MSSQLSERVER' -ComputerName $node | Restart-Service
    Start-Sleep -Seconds 60
  }
}

Create a database on the ya-mssql1 server:

PowerShell

Invoke-Sqlcmd -Query "CREATE DATABASE MyDatabase" -ServerInstance 'ya-mssql1.yantoso.net'

Configure access for the backup directory on the server:

PowerShell

New-SMBShare -Name SQLBackup -Path "X:\BACKUP" -FullAccess "yantoso\mssql-backups-grp"

$Acl = Get-Acl "X:\BACKUP"
$AccessRule = New-Object System.Security.AccessControl.FileSystemAccessRule("yantoso\mssql-backups-grp","Read", "ContainerInherit, ObjectInherit", "None", "Allow")
$Acl.AddAccessRule($AccessRule)

$Acl | Set-Acl "X:\BACKUP"

Create a backup of MyDatabase on the ya-mssql1 VM:

PowerShell

Backup-SqlDatabase `
  -Database "MyDatabase" -Initialize `
  -BackupFile "MyDatabase.bak" `
  -ServerInstance "ya-mssql1.yantoso.net"

Backup-SqlDatabase `
  -Database "MyDatabase" -Initialize `
  -BackupFile "MyDatabase.log" `
  -ServerInstance "ya-mssql1.yantoso.net" `
  -BackupAction Log

Restore the database on the ya-mssql2 server from the backup:

PowerShell

Restore-SqlDatabase `
  -Database "MyDatabase" `
  -BackupFile "\\ya-mssql1.yantoso.net\SQLBackup\MyDatabase.bak" `
  -Path "SQLSERVER:\SQL\ya-mssql2.yantoso.net\Default" `
  -NORECOVERY

Restore-SqlDatabase `
  -Database "MyDatabase" `
  -BackupFile "\\ya-mssql1.yantoso.net\SQLBackup\MyDatabase.log" `
  -Path "SQLSERVER:\SQL\ya-mssql2.yantoso.net\Default" `
  -RestoreAction Log `
  -NORECOVERY

Restore the database on the ya-mssql3 server from the backup:

PowerShell

Restore-SqlDatabase `
  -Database "MyDatabase" `
  -BackupFile "\\ya-mssql1.yantoso.net\SQLBackup\MyDatabase.bak" `
  -Path "SQLSERVER:\SQL\ya-mssql3.yantoso.net\Default" `
  -NORECOVERY

Restore-SqlDatabase `
  -Database "MyDatabase" `
  -BackupFile "\\ya-mssql1.yantoso.net\SQLBackup\MyDatabase.log" `
  -Path "SQLSERVER:\SQL\ya-mssql3.yantoso.net\Default" `
  -RestoreAction Log `
  -NORECOVERY

Add all databases to the availability group:

PowerShell

Add-SqlAvailabilityDatabase `
 -Path "SQLSERVER:\SQL\ya-mssql1.yantoso.net\Default\AvailabilityGroups\MyAG" `
 -Database "MyDatabase"

Add-SqlAvailabilityDatabase `
  -Path "SQLSERVER:\SQL\ya-mssql2.yantoso.net\Default\AvailabilityGroups\MyAG" `
  -Database "MyDatabase"

Add-SqlAvailabilityDatabase `
  -Path "SQLSERVER:\SQL\ya-mssql3.yantoso.net\Default\AvailabilityGroups\MyAG" `
  -Database "MyDatabase"

Test the availability group

You can test DB performance on any domain VM. Log in as yantoso\Administrator.

Create a table in the MyDatabase database under replication:

PowerShell

Invoke-Sqlcmd -ServerInstance 'MyAGlistener.yantoso.net' -Query @"
CREATE TABLE MyDatabase.dbo.test (
  test_id INT IDENTITY(1,1) PRIMARY KEY,
  test_name VARCHAR(30) NOT NULL
);
"@

Add a new row to the DB table:

PowerShell

Invoke-Sqlcmd -ServerInstance 'MyAGlistener.yantoso.net' -Query @"
INSERT INTO MyDatabase.dbo.test (test_name) 
VALUES ('one')
"@

Make sure the table now has this row:

PowerShell

Invoke-Sqlcmd -ServerInstance 'MyAGlistener.yantoso.net' -Query @"
SELECT *
FROM MyDatabase.dbo.test;
"@

Result:

test_id test_name
------- ---------
      1 one

Check the name of the main DB replica:

PowerShell

Invoke-Sqlcmd -Query "SELECT @@SERVERNAME" -ServerInstance 'MyAGlistener.yantoso.net'

Result:

Column1
-------
YA-MSSQL1

Run a failover to the second replica:

PowerShell

Invoke-Sqlcmd -Query "ALTER AVAILABILITY GROUP MyAg FAILOVER" -ServerInstance 'ya-mssql2.yantoso.net'

Verify the name of the main replica again:

PowerShell

Invoke-Sqlcmd -Query "SELECT @@SERVERNAME" -ServerInstance 'MyAGlistener.yantoso.net'

Result:

Column1
-------
YA-MSSQL2

Add another row to the table to check the second replica for writes:

PowerShell

Invoke-Sqlcmd -ServerInstance 'MyAGlistener.yantoso.net' -Query @"
INSERT INTO MyDatabase.dbo.test (test_name) 
VALUES ('two')
"@

Make sure the row is there:

PowerShell

Invoke-Sqlcmd -ServerInstance 'MyAGlistener.yantoso.net' -Query "SELECT * FROM MyDatabase.dbo.test"

Result:

test_id test_name
------- ---------
      1 one
      2 two

How to delete the resources you created

To stop paying for the resources you created, delete the VMs and the load balancer:

ya-jump1
ya-ad
ya-mssql1
ya-mssql2
ya-mssql3
ya-loadbalancer

Deploying an Always On availability group with an internal network load balancer

Get your cloud readyGet your cloud ready

Required paid resourcesRequired paid resources

Create a network infrastructureCreate a network infrastructure

Create an internal network load balancerCreate an internal network load balancer

Create a listenerCreate a listener

Create and connect the target group to the network load balancerCreate and connect the target group to the network load balancer

Prepare VMs for the availability groupPrepare VMs for the availability group

Prepare Windows Server imagesPrepare Windows Server images

Create a file with administrator credentialsCreate a file with administrator credentials

Create VMsCreate VMs

Create a VM for a bastion hostCreate a VM for a bastion host

Create a VM for Active DirectoryCreate a VM for Active Directory

Create VM instances for SQL ServerCreate VM instances for SQL Server

Bring your own Windows Server licensesBring your own Windows Server licenses

Install and configure Active DirectoryInstall and configure Active Directory

Create users and groups in Active DirectoryCreate users and groups in Active Directory

Install and configure SQL ServerInstall and configure SQL Server

Create a Windows Server failover clusterCreate a Windows Server failover cluster

Configure Always OnConfigure Always On

Test the availability groupTest the availability group

How to delete the resources you createdHow to delete the resources you created

Was the article helpful?

Get your cloud ready

Required paid resources

Create a network infrastructure

Create an internal network load balancer

Create a listener

Create and connect the target group to the network load balancer

Prepare VMs for the availability group

Prepare Windows Server images

Create a file with administrator credentials

Create VMs

Create a VM for a bastion host

Create a VM for Active Directory

Create VM instances for SQL Server

Bring your own Windows Server licenses

Install and configure Active Directory

Create users and groups in Active Directory

Install and configure SQL Server

Create a Windows Server failover cluster

Configure Always On

Test the availability group

How to delete the resources you created