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

Развертывание группы доступности Always On

Сценарий описывает развертывание в Yandex.Cloud группы доступности Always On. При выходе из строя одного или нескольких узлов группы система продолжит функционировать благодаря репликации и аварийному переключению базы данных, работающей на запись — таким образом обеспечивается высокая доступность СУБД.

Чтобы создать и настроить группу доступности Always On:

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

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

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

Перед тем, как разворачивать группу доступности, нужно зарегистрироваться в 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. Создайте в новой сети таблицу маршрутизации mssql и добавьте в нее статические маршруты:

    1. Откройте раздел Virtual Private Cloud в каталоге, где требуется создать статический маршрут.
    2. Выберите сеть ya-network.
    3. Нажмите кнопку Создать таблицу маршрутизации.
    4. Задайте имя таблицы маршрутизации: mssql.
    5. Нажмите кнопку Добавить маршрут.
    6. В открывшемся окне введите префикс подсети назначения в нотации CIDR: 10.0.0.20/32.
    7. Укажите next hop: 10.0.0.19.
    8. Нажмите кнопку Добавить.
    9. Добавьте еще пять маршрутов:
      • 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. Нажмите кнопку Создать таблицу маршрутизации.
    $ 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. Создайте подсети, в которых будут размещаться виртуальные машины:

  • подсеть ya-subnet-general для бастионного хоста и ВМ с инсталляцией Active Directory;

  • три подсети для размещения ВМ группы доступности Always On: ya-subnet-alwayson1, ya-subnet-alwayson2 и ya-subnet-alwayson3. К каждой из подсетей будет привязана таблица маршрутизации mssql.

    1. Откройте раздел Virtual Private Cloud в каталоге, где требуется создать подсети.
    2. Выберите сеть ya-network.
    3. Нажмите кнопку Добавить подсеть.
    4. Заполните форму: введите имя подсети ya-subnet-general, выберите зону доступности ru-central1-a из выпадающего списка.
    5. Введите CIDR подсети: IP-адрес и маску подсети: 10.0.0.0/28.
    6. Нажмите кнопку Создать подсеть.

    Повторите шаги для подсетей со следующими именами и 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.

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

    1. В строке нужной подсети нажмите кнопку .
    2. В открывшемся меню выберите пункт Привязать таблицу маршрутизации.
    3. В открывшемся окне выберите созданную таблицу в списке.
    4. Нажмите кнопку Добавить.
    $ 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

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

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

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

#ps1
Get-LocalUser | Where-Object SID -like *-500 | Set-LocalUser -Password (ConvertTo-SecureString "QWErty123" -AsPlainText -Force)

Важно

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

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

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

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

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

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

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

Создайте ВМ для 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-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

Создайте ВМ для серверов 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-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

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

  1. Подключитесь к ВМ jump-server-vm с помощью 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 ('QWErty123' | ConvertTo-SecureString -AsPlainText -Force)

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

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

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

    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. Укажите 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 ('QWErty123' | 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. Дайте ВМ сервера БД доступ в интернет:

    $ 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. Запустите RDP и подключитесь к ВМ ya-mssql1 с учетной записью Administrator и вашим паролем. Используйте публичный IP-адрес ВМ для подключения.

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

    Install-WindowsFeature Failover-Clustering -IncludeManagementTools

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

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

    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.

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

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

  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', `
    ('QWErty123' | 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 AAG Default" `
 -Name "MSSQLAAG-In-TCP" `
 -LocalPort 5022 `
 -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="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>.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

Настройте группу доступности Always On

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

  2. Для работы группы доступности 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'

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

    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. Импортируйте команды модуля SqlServer для PowerShell:

    Import-Module SqlServer

  5. Назначьте служебному пользователю mssql-svc разрешения на управление серверами:

    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. По очереди подключитесь к каждому серверу и включите 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

  7. Создайте и запустите эндпоинты 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

  8. Создайте переменные с параметрами реплик. Основной репликой будет выступать 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

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

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

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

    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. Создайте Listener:

    New-SqlAvailabilityGroupListener `
  -Name 'MyListener' `
  -Port 1433 `
  -StaticIp @("10.0.0.21/255.255.255.240","10.0.0.37/255.255.255.240","10.0.0.53/255.255.255.240") `
  -Path SQLSERVER:\Sql\ya-mssql1.yantoso.net\Default\AvailabilityGroups\MyAG

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

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

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

    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. Создайте резервную копию базы 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

  15. Восстановите базу данных на сервере 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

  16. Восстановите базу данных на сервере 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

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

    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"

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

Добавьте бастионный хост в домен

  1. Подключитесь к ВМ jump-server-vm с помощью RDP и запустите PowerShell.

  2. Укажите ВМ с контроллером домена в качестве DNS-сервера:

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

  3. Добавьте ВМ в домен:

    $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

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

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

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

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

    install-Module -Name SqlServer

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

    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. Добавьте в таблицу БД новую строку:

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

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

    Invoke-Sqlcmd -ServerInstance 'mylistner.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 'mylistener.yantoso.net'

    Column1
-------
YA-MSSQL2

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

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

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

    Invoke-Sqlcmd -ServerInstance 'mylistner.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.
В этой статье: