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

Written by

Updated at July 10, 2024

Prepare your cloud
- 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.

The use case describes how to deploy an Always On availability group in Yandex Cloud and enable load balancing between the nodes using an internal network load balancer. Network interfaces will be set up to combine multiple subnets into a single common subnet. This means you do not need Multisubnet Failover. The primary IP address will be assigned to the replica written to. 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, an additional non-standard port will be used to receive traffic.

To create and configure an Always On availability group with an internal network load balancer:

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

Prepare your cloud

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 command line interface yet, install and initialize it.

The folder specified in the CLI profile is used by default. You can specify a different folder using 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. The mssql route table will be linked to each subnet.
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 to create a subnet in.
Select the ya-network network.
Click Create subnet.
Fill out the form: enter the ya-sqlserver-rc1a 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 the steps for 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 theru-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, indicating 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 the 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 you are going 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 administrator's local account password. This script will be executed when creating VMs via the CLI.

Alert

The setpass file must be in UTF-8 encoding.

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 set password is only used for testing. Use your own complex password when deploying a cluster to work in a product environment.

The password must meet the complexity requirements.

Learn more about security best practices for Active Directory on the official website.

Create VMs

Make sure to create your VM instances 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 the reference).

Create a VM for a bastion host

Create a bastion host with Windows Server 2022 Datacenter with 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

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

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 VM instances 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 licenses for Windows Server

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

Install and configure Active Directory

Connect to ya-jump1 using RDP. Enter Administrator as the username and then your password.
From ya-jump1, connect to the ya-ad VM using RDP under the same account.
On ya-ad, launch 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 restarts.

Reconnect to ya-ad.

Rename the website and add the created subnets to it:

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 using RDP. Enter Administrator as the username and then your password.
From ya-jump1, connect to the ya-ad VM using RDP under the same account.

On the ya-ad VM, launch 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 SPN of the service account:

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 <ID_of_ya-mssql1> --network-interface-index 0
yc compute instance add-one-to-one-nat <ID_of_ya-mssql2> --network-interface-index 0
yc compute instance add-one-to-one-nat <ID_of_ya-mssql3> --network-interface-index 0

yc compute instance add-one-to-one-nat <ID_of_ya-mssql1> --network-interface-index 0
yc compute instance add-one-to-one-nat <ID_of_ya-mssql2> --network-interface-index 0
yc compute instance add-one-to-one-nat <ID_of_ya-mssql3> --network-interface-index 0

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

Start PowerShell and set the role:

PowerShell

Install-WindowsFeature Failover-Clustering -IncludeManagementTools

Restart the VM.
Reconnect to ya-mssql1 and launch 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

You'll be asked to confirm that you want to format the disk. Click Format disk. Click Start. After formatting finishes, click OK.

Create folders for the distribution, 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 English SQL Server 2022 image from the web to C:\dist.
Install the SqlServer module:
PowerShell
```
Install-Module -Name SqlServer
```
When asked for confirmation of installation, enter Y.
Import 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 data to access 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 restarts automatically.

After it restarts, reconnect to the VM with the yantoso\Administrator username and launch PowerShell.

Configure the necessary rights to 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, perform 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\<name_of_MSSQL_Server_image>.iso

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

Disable internet access for the VM:

Bash

PowerShell

yc compute instance remove-one-to-one-nat <ID_of_ya-mssql1> --network-interface-index 0
yc compute instance remove-one-to-one-nat <ID_of_ya-mssql2> --network-interface-index 0
yc compute instance remove-one-to-one-nat <ID_of_ya-mssql3> --network-interface-index 0

yc compute instance remove-one-to-one-nat <ID_of_ya-mssql1> --network-interface-index 0
yc compute instance remove-one-to-one-nat <ID_of_ya-mssql2> --network-interface-index 0
yc compute instance remove-one-to-one-nat <ID_of_ya-mssql3> --network-interface-index 0

Connect to ya-jump1 using RDP. Enter Administrator as the username and then your password.

From ya-jump1, connect to the ya-mssql1 VM using RDP under the same account. 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 VMs ya-mssql2 and ya-mssql3.
The Always On availability group requires a configured Windows Server Failover Cluster. To create it, 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 using RDP. Enter Administrator as the username and then your password.
From ya-jump1, connect to the ya-mssql1 VM using RDP under the yantoso\Administrator account.

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 the 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
  }
}

Give server management permissions to the mssql-svc service 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 in turn 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 SQL Server Configuration Manager, right-click the SQL Server instance to enable Always On Availability Groups for, 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 ones 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 port 14333 to the listener:

PowerShell

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

Open port 14333 on each VM 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 settings for the backup folder 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 the 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 your DB performance on any domain VM. Log in under the yantoso\Administrator account.

Create a table in the replicated MyDatabase DB:

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 row appears in the table:

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 was added:

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 created resources, delete the VMs and the load balancer you created:

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

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

Prepare your cloudPrepare your cloud

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 licenses for Windows ServerBring your own licenses for Windows Server

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?

Prepare your cloud

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 licenses for Windows Server

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