Get started
Solutions

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:

  1. Before you start.
  2. Required paid resources.
  3. Create a network infrastructure.
  4. Create VMs for the availability group.
  5. Create a file with administrator credentials.
  6. Create VMs.
  7. Create a VM for a bastion host.
  8. Create a VM for Active Directory.
  9. Create a VM for MSSQL servers.
  10. Install and configure Active Directory.
  11. Create users and groups in Active Directory.
  12. Install and configure MSSQL.
  13. Install MSSQL on database servers.
  14. Configure Always On.
  15. Test the availability group.
  16. Add a bastion host to the domain.
  17. 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:

  1. Go to the management console. Then log in to Yandex.Cloud or sign up if don't already have an account.
  2. On the billing page, make sure you linked a billing account, and it has the ACTIVE or TRIAL_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.

The cost of supporting the availability group includes:

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.

  1. Create a network named ya-network:

    1. Open the Virtual Private Cloud section in the folder where you want to create the cloud network.
    2. Click Create network.
    3. Enter the network name: ya-network.
    4. 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

  2. Create an mssql route table in the new network and add static routes to it:

    1. Open the Virtual Private Cloud section in the folder where you want to create a static route.
    2. Select the ya-network network.
    3. Click Create route table.
    4. Enter the route table name: mssql.
    5. Click Add route.
    6. In the window that opens enter the destination subnet prefix in CIDR notation: 10.0.0.20/32.
    7. Set the next hop: 10.0.0.19.
    8. Click Add.
    9. Add five more routes:
      • 10.0.0.21/32, next hop 10.0.0.19.
      • 10.0.0.36/32, next hop 10.0.0.35.
      • 10.0.0.37/32, next hop 10.0.0.35.
      • 10.0.0.52/32, next hop 10.0.0.51.
      • 10.0.0.53/32, next hop 10.0.0.51.
    10. 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

  3. 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, and ya-subnet-alwayson3. The mssql route table will be linked to each of the subnets.

    1. Open the Virtual Private Cloud section in the folder to create the subnets in.
    2. Select the ya-network network.
    3. Click Add subnet.
    4. Fill out the form: enter the ya-subnet-general subnet name and select the ru-central1-a availability zone from the drop-down list.
    5. Enter the subnet CIDR: IP address and subnet mask: 10.0.0.0/28.
    6. 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:

    1. In the line with the desired subnet, click .
    2. In the menu that opens, select Link route table.
    3. In the window that opens, select the created table from the list.
    4. 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

  1. Connect to the VM jump-server-vm using RDP. Enter Administrator as the username and then your password.

  2. Start the RDP client and connect to the ya-ad VM.

  3. Set the required server roles. Start PowerShell and run the following command:

    Install-WindowsFeature AD-Domain-Services -IncludeManagementTools

  4. Create an Active Directory forest:

    Install-ADDSForest -DomainName 'yantoso.net' -Force:$true -SafeModeAdministratorPassword ('QWErty123' | ConvertTo-SecureString -AsPlainText -Force)

    After that, the VM restarts.

  5. Reconnect to ya-ad.

  6. Rename the website and add the created subnets to it:

    Get-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'

  7. Specify the Forwarder for the DNS server:

    Set-DnsServerForwarder '10.0.0.2'

  8. Specify DNS server addresses:

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

Create users and groups in Active Directory

  1. Create a service account named mssql-svc:

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

  2. Create groups to access the backups and DB servers:

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

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

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

  4. Set the SPN of the service account:

    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 MSSQL

Install MSSQL on database servers

  1. Grant internet access to the DB server VM:

    $ 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

  2. Run the RDP client and connect to the ya-mssql1 VM with the Administrator account and your password. Use the VM's public IP address to connect.

  3. Start PowerShell and set the role:

    Install-WindowsFeature Failover-Clustering -IncludeManagementTools

  4. Restart the VM and PowerShell.

  5. Initialize and format the second logical disk:

    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 the disk formatting. Click Format disk. Click Start. Click OK.

  6. Create folders for backups, storage of databases, logs, and temporary files:

    mkdir C:\dist
mkdir X:\BACKUP
mkdir X:\TEMPDB
mkdir X:\TEMPDBLOG
mkdir X:\DB
mkdir X:\DBLOG

  7. Download the Microsoft SQL Server distribution from the web to C:\dist.

  8. Install the SqlServer module:

    Install-Module -Name SqlServer

  9. Specify the DNS server address:

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

    Prepare data to access the domain:

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

    Add the DB server to the domain:

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

    The VM restarts automatically.

  10. After the restart, reconnect to the VM with the yantoso\Administrator username and open PowerShell.

  11. Give the necessary rights to the service account.

    & 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

  12. Configure the firewall:

    New-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"

  13. Install MSSQL. Mount an image, perform installation, and dismount the image:

    Mount-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

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

  15. Disable the VM's internet access:

    $ 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

  1. Connect to jump-server-vm using RDP. Enter Administrator as the username and then your password. Open the RDP client and connect to ya-mssql1.

  2. The Always On availability group requires a configured Windows Server Failover Cluster. To create it, you need to test all the DB servers:

    Test-Cluster -Node 'ya-mssql1.yantoso.net'
Test-Cluster -Node 'ya-mssql2.yantoso.net'
Test-Cluster -Node 'ya-mssql3.yantoso.net'

  3. Create a cluster of three DB servers:

    New-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

  4. Import SQLServer module commands for PowerShell:

    Import-Module SqlServer

  5. Give server management permissions to the mssql-svc service user:

    Add-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()

  6. Connect to each server in turn and enable SqlAlwaysOn. When Always On is enabled, the DBMS service restarts.

    Enable-SqlAlwaysOn -ServerInstance 'ya-mssql1.yantoso.net' -Force
Enable-SqlAlwaysOn -ServerInstance 'ya-mssql2.yantoso.net' -Force
Enable-SqlAlwaysOn -ServerInstance 'ya-mssql3.yantoso.net' -Force

  7. Create and run HADR endpoints:

    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

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

    $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

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

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

  10. Add the remaining servers to the availability group:

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

  11. Create a Listener:

    New-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

  12. Create a database on the ya-mssql1 server:

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

  13. Configure access settings for the backup folder on the server:

    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"

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

    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

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

    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

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

    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

  17. Add all the databases to the availability group:

    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

Add the bastion host to the domain

  1. Connect jump-server-vm using RDP and start PowerShell.

  2. Specify the VM with the domain controller as the DNS server:

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

  3. Add the VM to the domain:

    $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.

  4. After restarting, reconnect to the VM and log in to it as yantoso\Administrator.

Test the database

  1. Install the SqlServer PowerShell module:

    install-Module -Name SqlServer

  2. Create a table in the replicated MyDatabase DB:

    Invoke-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
);
"@

  3. Add a new row to the DB table:

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

  4. Make sure the row appears in the table:

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

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

  5. Check the name of the main DB replica:

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

    Column1
-------
YA-MSSQL1

  6. Run a failover to the second replica:

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

  7. After a while, check the name of the main replica again:

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

    Column1
-------
YA-MSSQL2

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

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

  9. Make sure the row is added:

    Invoke-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
In this article: