Deploying an Always On availability group with an internal network load balancer
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
To create and configure an Always On availability group with an internal network load balancer:
- Prepare your cloud.
- Create a network infrastructure.
- Create an internal network load balancer.
- Prepare VMs for the availability group.
- Test the availability group.
If you no longer need the resources you created, delete them.
Prepare your cloud
Sign up for Yandex Cloud and create a billing account:
- 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 theACTIVE
orTRIAL_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
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 consoleBashPowerShell- Open the Virtual Private Cloud section of the folder where you want to create a cloud network.
- Click Create network.
- Enter the network name:
ya-network
. - 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
, andya-sqlserver-rc1d
. Themssql
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 consoleBashPowerShell- 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 theru-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 theru-central1-b
availability zone:192.168.1.16/28
.ya-sqlserver-rc1d
in theru-central1-d
availability zone:192.168.1.32/28
.ya-ilb-rc1a
in theru-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
- Three subnets to host SQLSERVER VMs:
Create an internal network load balancer
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
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
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.
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:
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
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:
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. EnterAdministrator
as the username and then your password. -
From
ya-jump1
, connect to theya-ad
VM using RDP under the same account. -
On
ya-ad
, launch PowerShell and set the required server roles:PowerShellInstall-WindowsFeature AD-Domain-Services -IncludeManagementTools
-
Create an Active Directory forest:
PowerShellInstall-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:
PowerShellGet-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:
PowerShellSet-DnsServerForwarder '10.0.0.2'
-
Specify the DNS server addresses:
PowerShellGet-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. EnterAdministrator
as the username and then your password. -
From
ya-jump1
, connect to theya-ad
VM using RDP under the same account. -
On the
ya-ad
VM, launch PowerShell and create themssql-svc
service account:PowerShellNew-ADUser ` -Name mssql-svc ` -AccountPassword ('YaQWErty123' | ConvertTo-SecureString -AsPlainText -Force) ` -CannotChangePassword $true ` -PasswordNeverExpires $true ` -Enabled $true
-
Create groups to access backups and DB servers:
PowerShellNew-AdGroup mssql-admins-grp -GroupScope:Global New-AdGroup mssql-backups-grp -GroupScope:Global
-
Add the
Administrator
account to all groups. Add themssql-svc
service account to themssql-backups-grp
group:PowerShellAdd-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:PowerShellsetspn -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:
BashPowerShellyc 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 theAdministrator
account and your password. Use the public IP address of the VM to connect. -
Start PowerShell and set the role:
PowerShellInstall-WindowsFeature Failover-Clustering -IncludeManagementTools
-
Reconnect to
ya-mssql1
and launch PowerShell. -
Initialize and format the second logical disk:
PowerShellGet-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:
PowerShellmkdir 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:
PowerShellInstall-Module -Name SqlServer
-
When asked for confirmation of installation, enter
Y
. -
Import SqlServer module commands for PowerShell:
PowerShellImport-Module SQLServer
-
Specify the DNS server address:
PowerShellGet-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:
PowerShellAdd-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:
PowerShellNew-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:
PowerShellMount-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
andya-mssql3
. -
Disable internet access for the VM:
BashPowerShellyc 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. EnterAdministrator
as the username and then your password. -
From
ya-jump1
, connect to theya-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
andya-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:
PowerShellTest-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. EnterAdministrator
as the username and then your password. -
From
ya-jump1
, connect to theya-mssql1
VM using RDP under theyantoso\Administrator
account. -
Create a cluster of three DB servers:
PowerShellNew-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:PowerShellAdd-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:
- Connect to the Windows Server Failover Cluster (WSFC) node that hosts the SQL Server instance.
- Open the Start menu and select All programs → Microsoft SQL Server → Configuration Tools → SQL Server Configuration Manager.
- In SQL Server Configuration Manager, right-click the SQL Server instance to enable Always On Availability Groups for, and select Properties.
- Go to the Always On High Availability tab.
- Select Enable Always On Availability Groups and restart the SQL Server instance service.
-
Create and start HADR endpoints
:PowerShellNew-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 areya-mssql2
andya-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:PowerShellNew-SqlAvailabilityGroup ` -Name 'MyAG' ` -AvailabilityReplica @($PrimaryReplica, $SecondaryReplica, $ThirdReplica) ` -Path "SQLSERVER:\SQL\ya-mssql1.yantoso.net\Default"
-
Add the remaining servers to the availability group:
PowerShellJoin-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 the192.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:PowerShellSet-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:PowerShellInvoke-Sqlcmd -Query "CREATE DATABASE MyDatabase" -ServerInstance 'ya-mssql1.yantoso.net'
-
Configure access settings for the backup folder on the server:
PowerShellNew-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 theya-mssql1
VM:PowerShellBackup-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:PowerShellRestore-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:PowerShellRestore-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:
PowerShellAdd-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:PowerShellInvoke-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:
PowerShellInvoke-Sqlcmd -ServerInstance 'MyAGlistener.yantoso.net' -Query @" INSERT INTO MyDatabase.dbo.test (test_name) VALUES ('one') "@
-
Make sure the row appears in the table:
PowerShellInvoke-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:
PowerShellInvoke-Sqlcmd -Query "SELECT @@SERVERNAME" -ServerInstance 'MyAGlistener.yantoso.net'
Result:
Column1 ------- YA-MSSQL1
-
Run a failover to the second replica:
PowerShellInvoke-Sqlcmd -Query "ALTER AVAILABILITY GROUP MyAg FAILOVER" -ServerInstance 'ya-mssql2.yantoso.net'
-
Verify the name of the main replica again:
PowerShellInvoke-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:
PowerShellInvoke-Sqlcmd -ServerInstance 'MyAGlistener.yantoso.net' -Query @" INSERT INTO MyDatabase.dbo.test (test_name) VALUES ('two') "@
-
Make sure the row was added:
PowerShellInvoke-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