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

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

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

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

Подготовьте облако к работе.
Необходимые платные ресурсы.
Создайте сетевую инфраструктуру.
Создайте внутренний сетевой балансировщик.
Создайте обработчик.
Создайте и подключите целевую группу.
Подготовьте виртуальные машины для группы доступности.
Создайте файл с учетными данными администратора.
Создайте виртуальные машины.
Создайте ВМ для бастионного хоста.
Создайте ВМ для Active Directory.
Создайте ВМ для серверов MSSQL.
Установите и настройте Active Directory.
Создайте пользователей и группы в Active Directory.
Установите и настройте MSSQL.
Установите MSSQL на серверы баз данных.
Создайте Windows Failover Cluster.
Настройте Always On.
Протестируйте группу доступности.
Протестируйте работу базы данных.

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

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

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

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

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

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

Необходимые платные ресурсы

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

плата за постоянно запущенные виртуальные машины (см. тарифы Yandex Compute Cloud);
плата за использование сетевого балансировщика (см. тарифы Yandex Load Balancer);
плата за использование динамических или статических публичных IP-адресов (см. тарифы Yandex Virtual Private Cloud).

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

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

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

Создайте сеть с именем ya-network:
Консоль управления

Bash

PowerShell
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
```
Создайте подсети, в которых будут размещаться виртуальные машины и сетевой балансировщик:
- Три подсети для размещения ВМ SQLServer: ya-sqlserver-rc1a, ya-sqlserver-rc1b и ya-sqlserver-rc1c. К каждой подсети будет привязана таблица маршрутизации mssql.
- Подсеть ya-ilb-rc1a для сетевого балансировщика.
- Подсеть ya-ad-rc1a для Active Directory.
Консоль управления

Bash

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

Создайте внутренний сетевой балансировщик

Bash

PowerShell

$ yc load-balancer network-load-balancer create \
   --name ya-loadbalancer \
   --type internal

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

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

Bash

PowerShell

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

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

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

Bash

PowerShell


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

Bash

PowerShell

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-адресом. Через этот хост будет осуществляться доступ ко всем остальным ВМ:

Bash

Powershell

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

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

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

Подключитесь к ВМ ya-jump1 с помощью RDP. Используйте логин Administrator и ваш пароль.
Запустите RDP и подключитесь к виртуальной машине ya-ad.
Установите необходимые роли сервера. Запустите PowerShell и выполните следующую команду:
PowerShell
```
Install-WindowsFeature AD-Domain-Services -IncludeManagementTools
```

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

PowerShell

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

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

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

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

PowerShell

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'

Укажите Forwarder для DNS-сервера:
PowerShell
```
Set-DnsServerForwarder '10.0.0.2'
```

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

PowerShell

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

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

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

PowerShell

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

Создайте группы для доступа к резервным копиям и серверам баз данных:
PowerShell
```
New-AdGroup mssql-admins-grp -GroupScope:Global
New-AdGroup mssql-backups-grp -GroupScope:Global
```
Добавьте учетную запись Administrator во все группы. В группу mssql-backups-grp добавьте сервисную учетную запись mssql-svc:
PowerShell
```
Add-ADGroupMember mssql-admins-grp -Members Administrator
Add-ADGroupMember mssql-backups-grp -Members Administrator
Add-ADGroupMember mssql-backups-grp -Members mssql-svc
```

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

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

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

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

Запустите RDP и подключитесь к ВМ ya-mssql1 с учетной записью Administrator и вашим паролем. Для подключения используйте публичный IP-адрес ВМ.
Запустите PowerShell и установите роль:
PowerShell
```
Install-WindowsFeature Failover-Clustering -IncludeManagementTools
```
Перезагрузите ВМ и снова запустите PowerShell.

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

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

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

Подготовьте папки для резервного копирования, хранения баз данных, логов и временных файлов:
PowerShell
```
mkdir C:\dist
mkdir X:\BACKUP
mkdir X:\TEMPDB
mkdir X:\TEMPDBLOG
mkdir X:\DB
mkdir X:\DBLOG
```

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

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

Загрузите в папку C:\dist дистрибутив MSSQL Server из интернета.
Установите модуль SqlServer:
PowerShell
```
Install-Module -Name SqlServer
```

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

PowerShell

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

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

PowerShell

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

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

PowerShell

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

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

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

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

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

Настройте фаерволл:

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

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

PowerShell

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

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

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

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

На каждой ВМ:

$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

Для работы группы доступности Always On требуется настроенный Windows Server Failover Cluster. Для его создания необходимо протестировать серверы БД. На любой из ВМ кластера выполните:
PowerShell
```
Test-Cluster -Node 'ya-mssql1.yantoso.net'
Test-Cluster -Node 'ya-mssql2.yantoso.net'
Test-Cluster -Node 'ya-mssql3.yantoso.net'
```

Создайте Windows Failover Cluster

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

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

PowerShell

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

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

PowerShell

$nodes = @('ya-mssql1.yantoso.net','ya-mssql2.yantoso.net','ya-mssql3.yantoso.net')

foreach ($node in $nodes) {
  $smo = [Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer]::new($node)
  $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
  }
}

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

PowerShell

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

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

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

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

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

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

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

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

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

PowerShell

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

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

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'

Создайте Listener:

PowerShell

$NLBIPAddress = '192.168.1.62'

Get-Cluster | `
  Add-ClusterResource -Name 'MyAG Network Name' -Group 'MyAG' -ResourceType 'Network Name'
Get-ClusterResource -Name 'MyAG Network Name' | `
  Set-ClusterParameter -Multiple `
    @{
      Name = 'MyAGlistener'
      DnsName = 'MyAGlistener'
    }

Get-Cluster | `
  Add-ClusterResource -Name 'My AG listener IP Address' -Group 'MyAG' -ResourceType 'IP Address'
Get-ClusterResource -Name 'My AG listener IP Address' | `
  Set-ClusterParameter -Multiple `
    @{
      Address=$NLBIPAddress
      ProbePort=59999
      SubnetMask="255.255.255.255"
      Network="Cluster Network 1"
      EnableDhcp=0
    }

Add-ClusterResourceDependency `
  -Resource 'MyAG Network Name' `
  -Provider 'My AG listener IP Address'

Stop-ClusterResource 'MyAG'

Add-ClusterResourceDependency `
  -Resource 'MyAG' `
  -Provider 'MyAG Network Name'

Start-ClusterResource 'MyAG'

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

PowerShell

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

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

PowerShell

$nodes = @('ya-mssql1.yantoso.net','ya-mssql2.yantoso.net','ya-mssql3.yantoso.net')

foreach ($node in $nodes) {
  $smo = [Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer]::new($node)
  $tcp = $smo.GetSmoObject("ManagedComputer[@Name=`'$node`']/ServerInstance[@Name='MSSQLSERVER']/ServerProtocol[@Name='Tcp']")
  $np = $smo.GetSmoObject("ManagedComputer[@Name=`'$node`']/ServerInstance[@Name='MSSQLSERVER']/ServerProtocol[@Name='NP']")
  $ipall = $smo.GetSmoObject("ManagedComputer[@Name=`'$node`']/ServerInstance[@Name='MSSQLSERVER']/ServerProtocol[@Name='Tcp']/IPAddress[@Name='IPAll']")

  if (-not $ipall.IPAddressProperties['TcpPort'].Value -ne '1433,14333') {
    $ipall.IPAddressProperties['TcpPort'].Value = '1433,14333'
    $tcp.Alter()
    $altered = $true
  }

  if (-not $tcp.IsEnabled) {
    $tcp.IsEnabled = $true
    $tcp.Alter()
    $altered = $true
  }
  if (-not $np.IsEnabled) {
    $np.IsEnabled = $true
    $np.Alter()
    $altered = $true
  }

  if ($altered) {
    Get-Service -Name 'MSSQLSERVER' -ComputerName $node | Restart-Service
    Start-Sleep -Seconds 60
  }
}

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

PowerShell

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

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

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"

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

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

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

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

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

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

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

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"

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

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

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

Импортируйте PowerShell-модуль SqlServer:
PowerShell
```
Import-Module SQLServer
```

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

PowerShell

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

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

PowerShell

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

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

PowerShell

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

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

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

PowerShell

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

Column1
-------
YA-MSSQL1

Выполните аварийное переключение на вторую реплику:
PowerShell
```
Invoke-Sqlcmd -Query "ALTER AVAILABILITY GROUP MyAg FAILOVER" -ServerInstance 'ya-mssql2.yantoso.net'
```
Через некоторое время снова проверьте имя основной реплики:
PowerShell
```
Invoke-Sqlcmd -Query "SELECT @@SERVERNAME" -ServerInstance 'MyAGlistener.yantoso.net'
```
```
Column1
-------
YA-MSSQL2
```
Добавьте еще одну строку в таблицу, чтобы проверить работу второй реплики на запись:
PowerShell
```
Invoke-Sqlcmd -ServerInstance 'MyAGlistener.yantoso.net' -Query @"
INSERT INTO MyDatabase.dbo.test (test_name) 
VALUES ('two')
"@
```

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

PowerShell

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.