Подключиться
Сценарии использования

Развертывание группы доступности Always On с внутренним сетевым балансировщиком

Статья создана

Сценарий описывает развертывание в Yandex.Cloud группы доступности Always On с балансировкой нагрузки между узлами с помощью внутреннего сетевого балансировщика. Несколько подсетей будут объединены в одну общую подсеть с помощью настройки сетевых интерфейсов. Благодаря этому не потребуется использование Multisubnet Failover. Основной IP-адрес будет назначаться реплике, в которую ведется запись. У этой же реплики будет открыт порт, на который балансировщик будет направлять трафик. Поскольку порт, указанный для подключения к балансировщику, становится недоступным, для приема трафика будет использоваться дополнительный нестандартный порт.

Чтобы создать и настроить группу доступности Always On с внутренним сетевым балансировщиком:

  1. Подготовьте облако к работе.
  2. Необходимые платные ресурсы.
  3. Создайте сетевую инфраструктуру.
  4. Создайте внутренний сетевой балансировщик.
  5. Создайте обработчик.
  6. Создайте и подключите целевую группу.
  7. Подготовьте виртуальные машины для группы доступности.
  8. Создайте файл с учетными данными администратора.
  9. Создайте виртуальные машины.
  10. Создайте ВМ для бастионного хоста.
  11. Создайте ВМ для Active Directory.
  12. Создайте ВМ для серверов MSSQL.
  13. Установите и настройте Active Directory.
  14. Создайте пользователей и группы в Active Directory.
  15. Установите и настройте MSSQL.
  16. Установите MSSQL на серверы баз данных.
  17. Создайте Windows Failover Cluster.
  18. Настройте Always On.
  19. Протестируйте группу доступности.
  20. Протестируйте работу базы данных.

Если созданные ресурсы вам больше не нужны, удалите их.

Подготовьте облако к работе

Перед тем, как разворачивать группу доступности, нужно зарегистрироваться в Yandex.Cloud и создать платежный аккаунт:

  1. Перейдите в консоль управления, затем войдите в Yandex.Cloud или зарегистрируйтесь, если вы еще не зарегистрированы.
  2. На странице биллинга убедитесь, что у вас подключен платежный аккаунт, и он находится в статусе ACTIVE или TRIAL_ACTIVE. Если платежного аккаунта нет, создайте его.

Если у вас есть активный платежный аккаунт, вы можете создать или выбрать каталог, в котором будет работать ваша виртуальная машина, на странице облака.

Подробнее об облаках и каталогах.

В стоимость поддержки группы доступности входят:

Вы можете воспользоваться мобильностью лицензий и использовать собственную лицензию MSSQL Server в Yandex.Cloud.

Создайте сетевую инфраструктуру

У всех реплик группы будет несколько IP-адресов, трафик на которые будет направляться с помощью статических маршрутов. Подготовьте сетевую инфраструктуру для размещения группы доступности.

  1. Создайте сеть с именем ya-network:

    1. Откройте раздел Virtual Private Cloud в каталоге, где требуется создать облачную сеть.
    2. Нажмите кнопку Создать сеть.
    3. Задайте имя сети: ya-network.
    4. Нажмите кнопку Создать сеть.

    Установите интерфейс командной строки Yandex.Cloud, чтобы использовать команды CLI в Bash.

    $ yc vpc network create --name ya-network

    Установите интерфейс командной строки Yandex.Cloud, чтобы использовать команды CLI в PowerShell.

    yc vpc network create --name ya-network

  2. Создайте подсети, в которых будут размещаться виртуальные машины и сетевой балансировщик:

    • Три подсети для размещения ВМ SQLServer: ya-sqlserver-rc1a, ya-sqlserver-rc1b и ya-sqlserver-rc1c. К каждой подсети будет привязана таблица маршрутизации mssql.
    • Подсеть ya-ilb-rc1a для сетевого балансировщика.
    • Подсеть ya-ad-rc1a для Active Directory.
    1. Откройте раздел Virtual Private Cloud в каталоге, где требуется создать подсети.
    2. Выберите сеть ya-network.
    3. Нажмите кнопку Добавить подсеть.
    4. Заполните форму: введите имя подсети ya-sqlserver-rc1a, выберите зону доступности ru-central1-a из выпадающего списка.
    5. Введите CIDR подсети: IP-адрес и маску подсети: 192.168.1.0/28.
    6. Нажмите кнопку Создать подсеть.

    Повторите шаги для подсетей со следующими именами и CIDR:

    • ya-sqlserver-rc1b в зоне доступности ru-central1-b — 192.168.1.16/28;
    • ya-sqlserver-rc1c в зоне доступности ru-central1-b — 192.168.1.32/28;
    • ya-ilb-rc1a в зоне доступности ru-central1-a — 192.168.1.48/28;
    • ya-ad-rc1a в зоне доступности ru-central1-a — 10.0.0.0/28;

    Чтобы использовать статические маршруты, необходимо привязать таблицу маршрутизации к подсети:

    1. В строке нужной подсети нажмите кнопку .
    2. В открывшемся меню выберите пункт Привязать таблицу маршрутизации.
    3. В открывшемся окне выберите созданную таблицу в списке.
    4. Нажмите кнопку Добавить.
    $ 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-rc1c \
   --zone ru-central1-c \
   --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-rc1c `
  --zone ru-central1-c `
  --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 load-balancer network-load-balancer create \
   --name ya-loadbalancer \
   --type internal

yc load-balancer network-load-balancer create `
   --name ya-loadbalancer `
   --type internal

Создайте обработчик

Получите идентификатор подсети следующей командой:

$ yc vpc subnet get --name ya-ilb-rc1a

Выполните команду, указав идентификатор подсети:

$ yc load-balancer network-load-balancer add-listener \
   --name ya-loadbalancer \
   --listener name=ya-listener,port=1433,target-port=14333,protocol=tcp,internal-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-subnet-id=$($inlbSubnet.id)

Создайте и подключите целевую группу к балансировщику


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

Скопируйте из ответа идентификатор целевой группы и выполните команду:

$ yc load-balancer network-load-balancer attach-target-group \
   --name ya-loadbalancer \
   --target-group 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-rc1c


$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

Подготовьте виртуальные машины для группы доступности

Создайте файл с учетными данными администратора

Создайте файл setpass со скриптом для установки пароля локальной учетной записи администратора. Этот скрипт будет выполняться при создании виртуальных машин через CLI.

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

Важно

Указанный пароль используется только для тестирования. Используйте собственный сложный пароль при развертывании кластера для работы в продуктовом окружении.

Пароль должен соответствовать требованиям к сложности.

Подробные рекомендации по защите Active Directory читайте на сайте разработчика.

Создайте виртуальные машины

Создайте ВМ для бастионного хоста

Создайте бастионный хост с публичным IP-адресом. Через этот хост будет осуществляться доступ ко всем остальным ВМ:

$ 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-nvme,size=50,image-family=windows-2019-gvlk,image-folder-id=standard-images \
   --network-interface \
     subnet-name=ya-ad-rc1a,nat-ip-version=ipv4 \
   --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-nvme,size=50,image-family=windows-2019-gvlk,image-folder-id=standard-images `
  --network-interface `
    subnet-name=ya-ad-rc1a,nat-ip-version=ipv4 `
  --async

Создайте ВМ для Active Directory

Создайте виртуальную машину для установки 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-ad-rc1a,ipv4-address=10.0.0.3 \
   --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-nvme,size=50,image-family=windows-2019-gvlk,image-folder-id=standard-images `
  --network-interface `
    subnet-name=ya-ad-rc1a,ipv4-address=10.0.0.3 `
  --async

Создайте ВМ для серверов MSSQL

Создайте три виртуальных машины для серверов MSSQL:

$ 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-sqlserver-rc1a,ipv4-address=192.168.1.3 \
   --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-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-sqlserver-rc1b,ipv4-address=192.168.1.19 \
   --async

$ yc compute instance create \
   --name ya-mssql3 \
   --hostname ya-mssql3 \
   --zone ru-central1-c \
   --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-sqlserver-rc1c,ipv4-address=192.168.1.35 \
   --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-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-sqlserver-rc1a,ipv4-address=192.168.1.3 `
  --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-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-sqlserver-rc1b,ipv4-address=192.168.1.19 `
  --async

yc compute instance create `
  --name ya-mssql3 `
  --hostname ya-mssql3 `
  --zone ru-central1-c `
  --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-sqlserver-rc1c,ipv4-address=192.168.1.35 `
  --async

Установите и настройте Active Directory

  1. Подключитесь к ВМ ya-jump1 с помощью RDP. Используйте логин Administrator и ваш пароль.

  2. Запустите RDP и подключитесь к виртуальной машине ya-ad.

  3. Установите необходимые роли сервера. Запустите PowerShell и выполните следующую команду:

    Install-WindowsFeature AD-Domain-Services -IncludeManagementTools

  4. Создайте лес Active Directory:

    Install-ADDSForest `
  -DomainName 'yantoso.net' `
  -Force:$true `
  -SafeModeAdministratorPassword `
    ('YaP@ssw0rd!11' | ConvertTo-SecureString -AsPlainText -Force)

    После этого ВМ перезапустится.

  5. Снова подключитесь к ВМ ya-ad.

  6. Переименуйте сайт и добавьте в него созданные подсети:

    Get-ADReplicationSite 'Default-First-Site-Name' | Rename-ADObject -NewName 'ru-central1'
New-ADReplicationSubnet -Name '10.0.0.0/28'  -Site 'ru-central1'
New-ADReplicationSubnet -Name '192.168.1.0/28'  -Site 'ru-central1'
New-ADReplicationSubnet -Name '192.168.1.16/28' -Site 'ru-central1'
New-ADReplicationSubnet -Name '192.168.1.32/28' -Site 'ru-central1'
New-ADReplicationSubnet -Name '192.168.1.48/28' -Site 'ru-central1'

  7. Укажите Forwarder для DNS-сервера:

    Set-DnsServerForwarder '10.0.0.2'

  8. Укажите адреса DNS-сервера:

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

Создайте пользователей и группы в Active Directory

  1. Создайте сервисную учетную запись mssql-svc:

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

  2. Создайте группы для доступа к резервным копиям и серверам баз данных:

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

  3. Добавьте учетную запись Administrator во все группы. В группу mssql-backups-grp добавьте сервисную учетную запись mssql-svc:

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

  4. Задайте SPN сервисной учетной записи:

    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

Установите и настройте MSSQL

Установите MSSQL на серверы баз данных

  1. Запустите RDP и подключитесь к ВМ ya-mssql1 с учетной записью Administrator и вашим паролем. Для подключения используйте публичный IP-адрес ВМ.

  2. Запустите PowerShell и установите роль:

    Install-WindowsFeature Failover-Clustering -IncludeManagementTools

  3. Перезагрузите ВМ и снова запустите PowerShell.

  4. Инициализируйте и отформатируйте второй логический диск:

    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

    Появится запрос подтверждения форматирования диска. Нажмите кнопку Format disk. Нажмите кнопку Start. Нажмите кнопку OK.

  5. Подготовьте папки для резервного копирования, хранения баз данных, логов и временных файлов:

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

  6. Дайте виртуальным машинам с серверами БД доступ в интернет:

    $ 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

  7. Загрузите в папку C:\dist дистрибутив MSSQL Server из интернета.

  8. Установите модуль SqlServer:

    Install-Module -Name SqlServer

  9. Укажите адрес DNS-сервера:

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

    Подготовьте данные для доступа к домену:

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

    Добавьте сервер БД в домен:

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

    ВМ автоматически перезапустится.

  10. После перезагрузки снова подключитесь к ВМ с логином yantoso\Administrator, откройте PowerShell.

  11. Дайте необходимые права служебной учетной записи.

    & 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. Настройте фаерволл:

    New-NetFirewallRule `
  -Group "MSSQL" `
  -DisplayName "MSSQL Server Default" `
  -Name "MSSQLServer-In-TCP" `
  -LocalPort 1433 `
  -Action "Allow" `
  -Protocol "TCP"

New-NetFirewallRule `
  -Group "MSSQL" `
  -DisplayName "MSSQL 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"

  13. Установите MSSQL. Смонтируйте образ, выполните установку и отсоедините образ:

    Mount-DiskImage -ImagePath C:\dist\<имя образа MSSQL Server>.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\<имя образа MSSQL Server>.iso

  14. Повторите шаги 2-13 для ВМ ya-mssql2 и ya-mssql3.

  15. Отключите у ВМ доступ в интернет:

    $ 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

  16. На каждой ВМ:

    $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

  17. Для работы группы доступности Always On требуется настроенный Windows Server Failover Cluster. Для его создания необходимо протестировать серверы БД. На любой из ВМ кластера выполните:

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

Создайте Windows Failover Cluster

  1. Подключитесь к ВМ jump-server-vm с помощью RDP. Используйте логин Administrator и ваш пароль. Откройте RDP и подключитесь к ВМ ya-mssql1.

  2. Создайте кластер из трех серверов БД:

    New-Cluster `
  -Name 'wsfc' `
  -Node 'ya-mssql1.yantoso.net','ya-mssql2.yantoso.net','ya-mssql3.yantoso.net' `
  -NoStorage `
  -StaticAddress 192.168.1.4

Test-Cluster

Get-ClusterResource -Name 'Cluster IP Address' | `
  Stop-ClusterResource

Get-ClusterResource -Name 'Cluster IP Address' | `
  Set-ClusterParameter -Multiple `
    @{
      "Address"="169.254.1.1";
      "SubnetMask"="255.255.255.255";
      "OverrideAddressMatch"=1;
      "EnableDhcp"=0
    }

Get-ClusterResource -Name 'Cluster Name' | Start-ClusterResource -Wait 60

  3. Включите на всех ВМ TCP/IP и добавьте порт 14333 для получения трафика:

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

  4. Импортируйте команды модуля SqlServer для PowerShell и назначьте служебному пользователю mssql-svc разрешения на управление серверами:

    Import-Module SqlServer

Add-SqlLogin -Path "SQLSERVER:\SQL\ya-mssql1\Default" `
  -LoginName "yantoso\mssql-svc" `
  -LoginType "WindowsUser" `
  -Enable `
  -GrantConnectSql

Add-SqlLogin -Path "SQLSERVER:\SQL\ya-mssql2\Default" `
  -LoginName "yantoso\mssql-svc" `
  -LoginType "WindowsUser" `
  -Enable `
  -GrantConnectSql

Add-SqlLogin -Path "SQLSERVER:\SQL\ya-mssql3\Default" `
  -LoginName "yantoso\mssql-svc" `
  -LoginType "WindowsUser" `
  -Enable `
  -GrantConnectSql

$mssql1 = Get-Item "SQLSERVER:\SQL\ya-mssql1.yantoso.net\Default"
$mssql1.Roles['sysadmin'].AddMember('yantoso\mssql-svc')
$mssql1.Roles['sysadmin'].Alter()

$mssql2 = Get-Item "SQLSERVER:\SQL\ya-mssql2.yantoso.net\Default"
$mssql2.Roles['sysadmin'].AddMember('yantoso\mssql-svc')
$mssql2.Roles['sysadmin'].Alter()

$mssql3 = Get-Item "SQLSERVER:\SQL\ya-mssql3.yantoso.net\Default"
$mssql3.Roles['sysadmin'].AddMember('yantoso\mssql-svc')
$mssql3.Roles['sysadmin'].Alter()

Настройте Always On

  1. По очереди подключитесь к каждому серверу и включите SqlAlwaysOn. При включении Always On сервис СУБД будет перезапускаться.

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

Get-Service -Name 'MSSQLSERVER' -ComputerName 'ya-mssql1.yantoso.net' | Restart-Service
Get-Service -Name 'MSSQLSERVER' -ComputerName 'ya-mssql2.yantoso.net' | Restart-Service
Get-Service -Name 'MSSQLSERVER' -ComputerName 'ya-mssql3.yantoso.net' | Restart-Service
Start-Sleep -Seconds 30

  2. Создайте и запустите эндпоинты HADR:

    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

  3. Создайте переменные с параметрами реплик. Основной репликой будет выступать ya-mssql1, второй и третьей — ya-mssql2 и ya-mssql3.

    $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

  4. Создайте из реплик группу доступности MyAG и добавьте туда первый сервер:

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

  5. Добавьте оставшиеся серверы в группу доступности:

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

  6. Создайте Listener:

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

  7. Назначьте порт 14333 обработчику:

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

  8. Откройте порт 14333 на всех ВМ кластера:

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

  9. Создайте базу данных на сервере ya-mssql1:

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

  10. Задайте настройки доступа к папке с резервными копиями на сервере:

    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"

  11. Создайте резервную копию базы MyDatabase на ВМ ya-mssql1:

    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

  12. Восстановите базу данных на сервере ya-mssql2 из резервной копии:

    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

  13. Восстановите базу данных на сервере ya-mssql3 из резервной копии:

    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

  14. Добавьте все базы данных в группу доступности:

    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"

Протестируйте группу доступности

Протестируйте работу базы данных

Тестирование можно провести на любой из доменных ВМ, войдя под учетной записью yantoso\Administrator.

  1. Импортируйте PowerShell-модуль SqlServer:

    Import-Module SQLServer

  2. Создайте таблицу в реплицируемой БД MyDatabase:

    Invoke-Sqlcmd -ServerInstance 'MyAGlistener.yantoso.net' -Query @"
CREATE TABLE MyDatabase.dbo.test (
  test_id INT IDENTITY(1,1) PRIMARY KEY,
  test_name VARCHAR(30) NOT NULL
);
"@

  3. Добавьте в таблицу БД новую строку:

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

  4. Проверьте, появилась ли строка в таблице:

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

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

  5. Проверьте имя основной реплики БД:

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

    Column1
-------
YA-MSSQL1

  6. Выполните аварийное переключение на вторую реплику:

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

  7. Через некоторое время снова проверьте имя основной реплики:

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

    Column1
-------
YA-MSSQL2

  8. Добавьте еще одну строку в таблицу, чтобы проверить работу второй реплики на запись:

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

  9. Убедитесь, что строка добавлена:

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

    test_id test_name
------- ---------
      1 one
      2 two

Удалите созданные ресурсы

Чтобы перестать платить за развернутые ресурсы, удалите созданные виртуальные машины и балансировщик:

  • vm-jump-server;
  • ya-ad;
  • ya-mssql1;
  • ya-mssql2;
  • ya-mssql3;
  • ya-loadbalancer.
В этой статье: