Deploying an Always On availability group
This scenario describes how to deploy an Always On availability group in Yandex.Cloud. If one or more nodes in the group fail, the system will continue to run through replication and failover of the write database, thus ensuring high availability of the DBMS.
To create and configure an Always On availability group:
- Before you start.
- Required paid resources.
- Create a network infrastructure.
- Create VMs for the availability group.
- Create a file with administrator credentials.
- Create VMs.
- Create a VM for a bastion host.
- Create a VM for Active Directory.
- Create a VM for MSSQL servers.
- Install and configure Active Directory.
- Create users and groups in Active Directory.
- Install and configure MSSQL.
- Install MSSQL on database servers.
- Configure Always On.
- Test the availability group.
- Add a bastion host to the domain.
- Test the database.
If you no longer need the created resources, delete them.
Before you start
Before deploying an availability group, sign up for Yandex.Cloud and create a billing account:
- Go to the management console. Then log in to Yandex.Cloud or sign up if don't already have an account.
- On the billing page, make sure you linked a billing account, and it has the
ACTIVE
orTRIAL_ACTIVE
status. If you don't have a billing account, create one.
If you have an active billing account, you can create or select a folder to run your VM in from the Yandex.Cloud page.
Learn more about clouds and folders.
Required paid resources
The cost of supporting the availability group includes:
- A fee for continuously running VMs (see pricing Yandex Compute Cloud).
- A fee for using dynamic or static public IP addresses (see pricing Yandex Virtual Private Cloud).
You can use license mobility and use your own MSSQL Server license in Yandex.Cloud.
Create a network infrastructure
All replicas in the group will have multiple IP addresses that will be routed to using static routes. Prepare the network infrastructure to host the availability group.
-
Create a network named
ya-network
:Management consoleBashPowerShell- Open the Virtual Private Cloud section in the folder where you want to create the cloud network.
- Click Create network.
- Enter the network name:
ya-network
. - Click Create network.
Install the Yandex.Cloud command line interface to use CLI commands in Bash.
$ 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 an
mssql
route table in the new network and add static routes to it:Management consoleBashPowerShell- Open the Virtual Private Cloud section in the folder where you want to create a static route.
- Select the
ya-network
network. - Click Create route table.
- Enter the route table name:
mssql
. - Click Add route.
- In the window that opens enter the destination subnet prefix in CIDR notation:
10.0.0.20/32
. - Set the next hop:
10.0.0.19
. - Click Add.
- Add five more routes:
10.0.0.21/32
, next hop10.0.0.19
.10.0.0.36/32
, next hop10.0.0.35
.10.0.0.37/32
, next hop10.0.0.35
.10.0.0.52/32
, next hop10.0.0.51
.10.0.0.53/32
, next hop10.0.0.51
.
- Click Create route table.
$ yc vpc route-table create \ --name mssql \ --route destination=10.0.0.20/32,next-hop=10.0.0.19 \ --route destination=10.0.0.21/32,next-hop=10.0.0.19 \ --route destination=10.0.0.36/32,next-hop=10.0.0.35 \ --route destination=10.0.0.37/32,next-hop=10.0.0.35 \ --route destination=10.0.0.52/32,next-hop=10.0.0.51 \ --route destination=10.0.0.53/32,next-hop=10.0.0.51 \ --network-name ya-network
yc vpc route-table create ` --name mssql ` --route destination=10.0.0.20/32,next-hop=10.0.0.19 ` --route destination=10.0.0.21/32,next-hop=10.0.0.19 ` --route destination=10.0.0.36/32,next-hop=10.0.0.35 ` --route destination=10.0.0.37/32,next-hop=10.0.0.35 ` --route destination=10.0.0.52/32,next-hop=10.0.0.51 ` --route destination=10.0.0.53/32,next-hop=10.0.0.51 ` --network-name ya-network
-
Create subnets that will host your VMs:
-
A subnet named
ya-subnet-general
for the bastion host and VM with Active Directory. -
Three subnets for hosting the Always On availability group's VMs:
ya-subnet-alwayson1
,ya-subnet-alwayson2
, andya-subnet-alwayson3
. Themssql
route table will be linked to each of the subnets.Management consoleBashPowerShell- Open the Virtual Private Cloud section in the folder to create the subnets in.
- Select the
ya-network
network. - Click Add subnet.
- Fill out the form: enter the
ya-subnet-general
subnet name and select theru-central1-a
availability zone from the drop-down list. - Enter the subnet CIDR: IP address and subnet mask:
10.0.0.0/28
. - Click Create subnet.
Repeat the steps for subnets with the following names and CIDR:
ya-subnet-alwayson1
—10.0.0.16/28
.ya-subnet-alwayson2
—10.0.0.32/28
.ya-subnet-alwayson3
—10.0.0.48/28
.
To use static routes, link the route table to a subnet:
- In the line with the desired subnet, click .
- In the menu that opens, select Link route table.
- In the window that opens, select the created table from the list.
- Click Add.
$ yc vpc subnet create \ --name ya-subnet-general \ --network-name ya-network \ --zone ru-central1-a \ --route-table-name mssql \ --range 10.0.0.0/28
$ yc vpc subnet create \ --name ya-subnet-alwayson1 \ --network-name ya-network \ --zone ru-central1-a \ --route-table-name mssql \ --range 10.0.0.16/28
$ yc vpc subnet create \ --name ya-subnet-alwayson2 \ --network-name ya-network \ --zone ru-central1-a \ --route-table-name mssql \ --range 10.0.0.32/28
$ yc vpc subnet create \ --name ya-subnet-alwayson3 \ --network-name ya-network \ --zone ru-central1-a \ --route-table-name mssql \ --range 10.0.0.48/28
yc vpc subnet create ` --name ya-subnet-general ` --network-name ya-network ` --zone ru-central1-a ` --route-table-name mssql ` --range 10.0.0.0/28
yc vpc subnet create ` --name ya-subnet-alwayson1 ` --network-name ya-network ` --zone ru-central1-a ` --route-table-name mssql ` --range 10.0.0.16/28
yc vpc subnet create ` --name ya-subnet-alwayson2 ` --network-name ya-network ` --zone ru-central1-a ` --route-table-name mssql ` --range 10.0.0.32/28
yc vpc subnet create ` --name ya-subnet-alwayson3 ` --network-name ya-network ` --zone ru-central1-a ` --route-table-name mssql ` --range 10.0.0.48/28
Create VMs for the availability group
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.
#ps1
Get-LocalUser | Where-Object SID -like *-500 | Set-LocalUser -Password (ConvertTo-SecureString "QWErty123" -AsPlainText -Force)
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.
Read more about the best practices for securing Active Directory on the official website.
Create the virtual machines
Create a VM for a bastion host
Create a bastion host with a public IP address. This host will provide access to all other VMs:
$ yc compute instance create \
--name jump-server-vm \
--hostname jump-server-vm \
--memory 4 \
--cores 2 \
--zone ru-central1-a \
--network-interface subnet-name=ya-subnet-general,nat-ip-version=ipv4 \
--create-boot-disk image-folder-id=standard-images,image-family=windows-2019-gvlk \
--metadata-from-file user-data=setpass
yc compute instance create `
--name jump-server-vm `
--hostname jump-server-vm `
--memory 4 `
--cores 2 `
--zone ru-central1-a `
--network-interface subnet-name=ya-subnet-general,nat-ip-version=ipv4 `
--create-boot-disk image-folder-id=standard-images,image-family=windows-2019-gvlk `
--metadata-from-file user-data=setpass `
Create a VM for Active Directory
Create a VM to install 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-nvme,size=50,image-family=windows-2019-gvlk,image-folder-id=standard-images \
--network-interface \
subnet-name=ya-subnet-general,ipv4-address=10.0.0.3
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-nvme,size=50,image-family=windows-2019-gvlk,image-folder-id=standard-images `
--network-interface `
subnet-name=ya-subnet-general,ipv4-address=10.0.0.3
Create a VM for MSSQL servers
Create three VMs for MSSQL servers:
$ 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-nvme,size=50,image-family=windows-2019-gvlk,image-folder-id=standard-images \
--create-disk \
type=network-nvme,size=200 \
--network-interface \
subnet-name=ya-subnet-alwayson1,ipv4-address=10.0.0.19
$ yc compute instance create \
--name ya-mssql2 \
--hostname ya-mssql2 \
--zone ru-central1-a \
--memory 16 \
--cores 4 \
--metadata-from-file user-data=setpass \
--create-boot-disk \
type=network-nvme,size=50,image-family=windows-2019-gvlk,image-folder-id=standard-images \
--create-disk \
type=network-nvme,size=200 \
--network-interface \
subnet-name=ya-subnet-alwayson2,ipv4-address=10.0.0.35
$ yc compute instance create \
--name ya-mssql3 \
--hostname ya-mssql3 \
--zone ru-central1-a \
--memory 16 \
--cores 4 \
--metadata-from-file user-data=setpass \
--create-boot-disk \
type=network-nvme,size=50,image-family=windows-2019-gvlk,image-folder-id=standard-images \
--create-disk \
type=network-nvme,size=200 \
--network-interface \
subnet-name=ya-subnet-alwayson3,ipv4-address=10.0.0.51
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-nvme,size=50,image-family=windows-2019-gvlk,image-folder-id=standard-images `
--create-disk `
type=network-nvme,size=200 `
--network-interface `
subnet-name=ya-subnet-alwayson1,ipv4-address=10.0.0.19
yc compute instance create `
--name ya-mssql2 `
--hostname ya-mssql2 `
--zone ru-central1-a `
--memory 16 `
--cores 4 `
--metadata-from-file user-data=setpass `
--create-boot-disk `
type=network-nvme,size=50,image-family=windows-2019-gvlk,image-folder-id=standard-images `
--create-disk `
type=network-nvme,size=200 `
--network-interface `
subnet-name=ya-subnet-alwayson2,ipv4-address=10.0.0.35
yc compute instance create `
--name ya-mssql3 `
--hostname ya-mssql3 `
--zone ru-central1-a `
--memory 16 `
--cores 4 `
--metadata-from-file user-data=setpass `
--create-boot-disk `
type=network-nvme,size=50,image-family=windows-2019-gvlk,image-folder-id=standard-images `
--create-disk `
type=network-nvme,size=200 `
--network-interface `
subnet-name=ya-subnet-alwayson3,ipv4-address=10.0.0.51
Install and configure Active Directory
-
Connect to the VM
jump-server-vm
using RDP. EnterAdministrator
as the username and then your password. -
Start the RDP client and connect to the
ya-ad
VM. -
Set the required server roles. Start PowerShell and run the following command:
PowerShellInstall-WindowsFeature AD-Domain-Services -IncludeManagementTools
-
Create an Active Directory forest:
PowerShellInstall-ADDSForest -DomainName 'yantoso.net' -Force:$true -SafeModeAdministratorPassword ('QWErty123' | 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-a' New-ADReplicationSubnet -Name '10.0.0.0/28' -Site 'ru-central1-a' New-ADReplicationSubnet -Name '10.0.0.16/28' -Site 'ru-central1-a' New-ADReplicationSubnet -Name '10.0.0.32/28' -Site 'ru-central1-a' New-ADReplicationSubnet -Name '10.0.0.48/28' -Site 'ru-central1-a'
-
Specify the Forwarder for the DNS server:
PowerShellSet-DnsServerForwarder '10.0.0.2'
-
Specify DNS server addresses:
PowerShellGet-NetAdapter | Set-DnsClientServerAddress -ServerAddresses "10.0.0.3,127.0.0.1"
Create users and groups in Active Directory
-
Create a service account named
mssql-svc
:PowerShellNew-ADUser ` -Name mssql-svc ` -AccountPassword ('QWErty123' | ConvertTo-SecureString -AsPlainText -Force) ` -CannotChangePassword $true ` -PasswordNeverExpires $true ` -Enabled $true
-
Create groups to access the 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 MSSQL
Install MSSQL on database servers
-
Grant internet access to the DB server VM:
BashPowerShell$ yc compute instance add-one-to-one-nat ya-mssql1 --network-interface-index 0 $ yc compute instance add-one-to-one-nat ya-mssql2 --network-interface-index 0 $ yc compute instance add-one-to-one-nat ya-mssql3 --network-interface-index 0
yc compute instance add-one-to-one-nat ya-mssql1 --network-interface-index 0 yc compute instance add-one-to-one-nat ya-mssql2 --network-interface-index 0 yc compute instance add-one-to-one-nat ya-mssql3 --network-interface-index 0
-
Run the RDP client and connect to the
ya-mssql1
VM with theAdministrator
account and your password. Use the VM's public IP address to connect. -
Start PowerShell and set the role:
PowerShellInstall-WindowsFeature Failover-Clustering -IncludeManagementTools
-
Restart the VM and 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 the disk formatting. Click Format disk. Click Start. Click OK.
-
Create folders for backups, storage of databases, logs, and temporary files:
PowerShellmkdir C:\dist mkdir X:\BACKUP mkdir X:\TEMPDB mkdir X:\TEMPDBLOG mkdir X:\DB mkdir X:\DBLOG
-
Download the Microsoft SQL Server distribution from the web to
C:\dist
. -
Install the SqlServer module:
PowerShellInstall-Module -Name 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', ` ('QWErty123' | 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 the restart, reconnect to the VM with the
yantoso\Administrator
username and open PowerShell. -
Give 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
-
Configure the firewall:
PowerShellNew-NetFirewallRule ` -Group "MSSQL" ` -DisplayName "MSSQL Server Default" ` -Name "MSSQLServer-In-TCP" ` -LocalPort 1433 ` -Action "Allow" ` -Protocol "TCP" New-NetFirewallRule ` -Group "MSSQL" ` -DisplayName "MSSQL AAG Default" ` -Name "MSSQLAAG-In-TCP" ` -LocalPort 5022 ` -Action "Allow" ` -Protocol "TCP"
-
Install MSSQL. Mount an image, perform installation, and dismount the image:
PowerShellMount-DiskImage -ImagePath C:\dist\<MSSQL Server image name>.iso & D:\setup.exe /QUIET /INDICATEPROGRESS /IACCEPTSQLSERVERLICENSETERMS ` /ACTION=INSTALL /FEATURES=SQLENGINE /INSTANCENAME=MSSQLSERVER ` /SQLSVCACCOUNT="yantoso\mssql-svc" /SQLSVCPASSWORD="QWErty123" ` /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-14 for VMs
ya-mssql2
andya-mssql3
. -
Disable the VM's internet access:
BashPowerShell$ yc compute instance remove-one-to-one-nat ya-mssql1 --network-interface-index 0 $ yc compute instance remove-one-to-one-nat ya-mssql2 --network-interface-index 0 $ yc compute instance remove-one-to-one-nat ya-mssql3 --network-interface-index 0
yc compute instance remove-one-to-one-nat ya-mssql1 --network-interface-index 0 yc compute instance remove-one-to-one-nat ya-mssql2 --network-interface-index 0 yc compute instance remove-one-to-one-nat ya-mssql3 --network-interface-index 0
Configure the Always On availability group
-
Connect to
jump-server-vm
using RDP. Enter Administrator as the username and then your password. Open the RDP client and connect toya-mssql1
. -
The Always On availability group requires a configured Windows Server Failover Cluster. To create it, you need to test all the DB servers:
PowerShellTest-Cluster -Node 'ya-mssql1.yantoso.net' Test-Cluster -Node 'ya-mssql2.yantoso.net' Test-Cluster -Node 'ya-mssql3.yantoso.net'
-
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 10.0.0.20, 10.0.0.36, 10.0.0.52
-
Import SQLServer module commands for PowerShell:
PowerShellImport-Module SqlServer
-
Give server management permissions to the
mssql-svc
service user:PowerShellAdd-SqlLogin -Path "SQLSERVER:\SQL\ya-mssql1.yantoso.net\Default" ` -LoginName "yantoso\mssql-svc" ` -LoginType "WindowsUser" ` -Enable ` -GrantConnectSql Add-SqlLogin -Path "SQLSERVER:\SQL\ya-mssql2.yantoso.net\Default" ` -LoginName "yantoso\mssql-svc" ` -LoginType "WindowsUser" ` -Enable ` -GrantConnectSql Add-SqlLogin -Path "SQLSERVER:\SQL\ya-mssql3.yantoso.net\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()
-
Connect to each server in turn and enable SqlAlwaysOn. When Always On is enabled, the DBMS service restarts.
PowerShellEnable-SqlAlwaysOn -ServerInstance 'ya-mssql1.yantoso.net' -Force Enable-SqlAlwaysOn -ServerInstance 'ya-mssql2.yantoso.net' -Force Enable-SqlAlwaysOn -ServerInstance 'ya-mssql3.yantoso.net' -Force
-
Create and run 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:
PowerShellNew-SqlAvailabilityGroupListener ` -Name 'MyListener' ` -Port 1433 ` -StaticIp @("10.0.0.21/255.255.255.240","10.0.0.37/255.255.255.240","10.0.0.53/255.255.255.240") ` -Path SQLSERVER:\Sql\ya-mssql1.yantoso.net\Default\AvailabilityGroups\MyAG
-
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
Add the bastion host to the domain
-
Connect
jump-server-vm
using RDP and start PowerShell. -
Specify the VM with the domain controller as the DNS server:
PowerShellGet-NetAdapter | Set-DnsClientServerAddress -ServerAddresses "10.0.0.3"
-
Add the VM to the domain:
PowerShell$domain_credential = ` New-Object System.Management.Automation.PSCredential ( 'yantoso\Administrator', ` ('QWErty123' | ConvertTo-SecureString -AsPlainText -Force)) Add-Computer -DomainCredential $domain_credential -DomainName 'yantoso.net' -Restart -Force
The VM restarts automatically.
-
After restarting, reconnect to the VM and log in to it as
yantoso\Administrator
.
Test the database
-
Install the
SqlServer
PowerShell module:PowerShellinstall-Module -Name SqlServer
-
Create a table in the replicated
MyDatabase
DB:PowerShellInvoke-Sqlcmd -ServerInstance 'mylistner.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 'mylistner.yantoso.net' -Query @" INSERT INTO MyDatabase.dbo.test (test_name) VALUES ('one') "@
-
Make sure the row appears in the table:
PowerShellInvoke-Sqlcmd -ServerInstance 'mylistner.yantoso.net' -Query "SELECT * FROM MyDatabase.dbo.test"
test_id test_name ------- --------- 1 one
-
Check the name of the main DB replica:
PowerShellInvoke-Sqlcmd -Query "SELECT @@SERVERNAME" -ServerInstance 'mylistener.yantoso.net'
Column1 ------- YA-MSSQL1
-
Run a failover to the second replica:
PowerShellInvoke-Sqlcmd -Query "ALTER AVAILABILITY GROUP MyAg FAILOVER" -ServerInstance 'ya-mssql2.yantoso.net'
-
After a while, check the name of the main replica again:
PowerShellInvoke-Sqlcmd -Query "SELECT @@SERVERNAME" -ServerInstance 'mylistener.yantoso.net'
Column1 ------- YA-MSSQL2
-
Add another row to the table to check the second replica for writes:
PowerShellInvoke-Sqlcmd -ServerInstance 'mylistner.yantoso.net' -Query @" INSERT INTO MyDatabase.dbo.test (test_name) VALUES ('two') "@
-
Make sure the row is added:
PowerShellInvoke-Sqlcmd -ServerInstance 'mylistner.yantoso.net' -Query "SELECT * FROM MyDatabase.dbo.test"
test_id test_name ------- --------- 1 one 2 two
Delete the created resources
To stop paying for the deployed servers, delete the VMs you created:
vm-jump-server
ya-ad
ya-mssql1
ya-mssql2
ya-mssql3