Yandex.Cloud
  • Services
  • Why Yandex.Cloud
  • Pricing
  • Documentation
  • Contact us
Get started
Use cases
  • Web service
    • All use cases
    • Static website in Object Storage
    • Website on LAMP or LEMP stack
    • Fault-tolerant website with load balancing from Yandex Network Load Balancer
    • Fault-tolerant website using DNS load balancing
    • Joomla-based website with PostgreSQL
    • WordPress website
    • WordPress website on a MySQL database
    • 1C-Bitrix website
  • Online stores
    • All use cases
    • 1C-Bitrix online store
    • Opencart online store
  • Data archive
    • All use cases
    • Single-node file server
    • Configuring an SFTP server on Centos 7
    • Backup to Object Storage via Acronis Backup
    • Backup to Object Storage via CloudBerry Desktop Backup
    • Backup to Object Storage via Duplicati
    • Backup to Object Storage via Bacula
    • Digitizing archives in Yandex Vision
  • Test environment
    • All use cases
    • Testing applications with GitLab
    • Creating test VMs using GitLab CI
    • High-performance computing on preemptible VMs
  • Infrastructure management
    • All use cases
    • Getting started with Terraform
    • Uploading Terraform states to Object Storage
    • Getting started with Packer
    • VM images building automation using Jenkins
    • Continuous deployment of containerized applications using GitLab
    • Creating a cluster of 1C:Enterprise Linux servers with a Managed Service for PostgreSQL cluster
    • Creating a cluster of 1C:Enterprise Windows servers with MS SQL Server
    • Migrating to Yandex.Cloud using Hystax Acura
    • Emergency recovery in Yandex.Cloud using Hystax Acura
    • Configuring a fault-tolerant architecture in Yandex.Cloud
  • Windows in Yandex.Cloud
    • All use cases
    • Deploying Active Directory
    • Deploying Microsoft Exchange
    • Deploying Remote Desktop Services
    • Deploying an Always On availability group
    • Deploying an Always On availability group with an internal network load balancer
  • Network routing
    • All use cases
    • Routing through a NAT instance
    • Creating a VPN tunnel
    • Installing a Cisco CSR1000v virtual router
    • Installing a Mikrotik CHR virtual router
    • Creating a VPN connection using OpenVPN
  • Data visualization and analytics
    • All use cases
    • Visualizing data from a CSV file
    • Visualizing data from a ClickHouse database
    • Visualizing data from Yandex.Metrica
    • Visualizing data from Yandex.Metrica Logs API
    • Publishing a chart with a map from a CSV file to DataLens Public
    • Visualizing data from AppMetrica
    • Visualizing geodata from a CSV file
  • Internet of things
    • Use cases for the internet of things
    • Status monitoring of geographically distributed devices
    • Monitoring sensor readings and event notifications
  1. Windows in Yandex.Cloud
  2. Deploying an Always On availability group

Deploying 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 the virtual machines
    • Install and configure Active Directory
    • Create users and groups in Active Directory
    • Install and configure MSSQL
    • Configure the Always On availability group
  • Test the availability group
    • Add the bastion host to the domain
    • Test the database
  • Delete the created resources

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.

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.

  1. Create a network named ya-network:

    Management console
    Bash
    PowerShell
    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:

    Management console
    Bash
    PowerShell
    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.

    Management console
    Bash
    PowerShell
    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.

PowerShell
#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:

Bash
PowerShell
$ 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:

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

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

    PowerShell
    Install-WindowsFeature AD-Domain-Services -IncludeManagementTools
    
  4. Create an Active Directory forest:

    PowerShell
    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:

    PowerShell
    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:

    PowerShell
    Set-DnsServerForwarder '10.0.0.2'
    
  8. Specify DNS server addresses:

    PowerShell
    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:

    PowerShell
    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:

    PowerShell
    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:

    PowerShell
    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:

    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 MSSQL

Install MSSQL on database servers

  1. Grant internet access to the DB server VM:

    Bash
    PowerShell
    $ 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:

    PowerShell
    Install-WindowsFeature Failover-Clustering -IncludeManagementTools
    
  4. Restart the VM and PowerShell.

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

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

    PowerShell
    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:

    PowerShell
    Install-Module -Name SqlServer
    
  9. 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', `
        ('QWErty123' | 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.

  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.

    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
    
  12. Configure the firewall:

    PowerShell
    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:

    PowerShell
    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:

    Bash
    PowerShell
    $ 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:

    PowerShell
    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:

    PowerShell
    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:

    PowerShell
    Import-Module SqlServer
    
  5. Give server management permissions to the mssql-svc service user:

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

    PowerShell
    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:

    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
    
  8. 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
    
  9. 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"
    
  10. 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'
    
  11. Create a Listener:

    PowerShell
    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:

    PowerShell
    Invoke-Sqlcmd -Query "CREATE DATABASE MyDatabase" -ServerInstance 'ya-mssql1.yantoso.net'
    
  13. 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"
    
  14. 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
    
  15. 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
    
  16. 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
    
  17. 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

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:

    PowerShell
    Get-NetAdapter | Set-DnsClientServerAddress -ServerAddresses "10.0.0.3"
    
  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.

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

Test the database

  1. Install the SqlServer PowerShell module:

    PowerShell
    install-Module -Name SqlServer
    
  2. Create a table in the replicated MyDatabase DB:

    PowerShell
    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:

    PowerShell
    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:

    PowerShell
    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:

    PowerShell
    Invoke-Sqlcmd -Query "SELECT @@SERVERNAME" -ServerInstance 'mylistener.yantoso.net'
    
    Column1
    -------
    YA-MSSQL1
    
  6. Run a failover to the second replica:

    PowerShell
    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:

    PowerShell
    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:

    PowerShell
    Invoke-Sqlcmd -ServerInstance 'mylistner.yantoso.net' -Query @"
    INSERT INTO MyDatabase.dbo.test (test_name) 
    VALUES ('two')
    "@
    
  9. Make sure the row is added:

    PowerShell
    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:
  • Before you start
  • Required paid resources
  • Create a network infrastructure
  • Create VMs for the availability group
  • Create a file with administrator credentials
  • Create the virtual machines
  • Install and configure Active Directory
  • Create users and groups in Active Directory
  • Install and configure MSSQL
  • Configure the Always On availability group
  • Test the availability group
  • Add the bastion host to the domain
  • Test the database
  • Delete the created resources
Language
Careers
Privacy policy
Terms of use
© 2021 Yandex.Cloud LLC