MS SQL Server дело тонкое...: AlwaysOn Availability Groups – пошаговая настройка гибридной конфигурации

Автор: SQLYARD. SQL Always On Availability Groups – Hybrid Setup Steps

Это руководство описывает настройку отказоустойчивого кластера AlwaysOn Availability Group (AG) с двумя обычными узлами, одним узлом в Azure и использованием Azure Cloud Witness для кворума. В итоге вы получите продакшн‑кластер с автоматическим переключением на локальном уровне и DR‑репликой в облаке.

Часть 1 — Создание Windows Server Failover Cluster (WSFC) для SQL Server AlwaysOn

Топология: 2 on‑prem узла + 1 Azure‑узел, Azure Cloud Witness, multi-subnet Listener.

Пример таблицы‑заготовки

Элемент	Пример
AD Domain	`corp.example.com`
Cluster Name (CNO)	`SQLCLUSTER`
Узлы	`SQLNODE1` (on‑prem), `SQLNODE2`, `SQLNODE-AZ` (Azure VM)
IP‑адреса узлов	`170.xx.xx.11`, `170.xx.xx.12`, `170.xx.xx.21`
Cluster Mgmt IP	`170.xx.xx.30`
Listener Name	`SQLLISTENER`
Listener IPs	`170.xx.xx.50` (on‑prem), `170.xx.xx.60` (Azure)
SQL Service Account	`CORP\sqlsvc`
Azure Storage (Cloud Witness)	`https://sqlwitnessacct.blob.core.windows.net/`
HADR Endpoint Port	5022
Listener Port	1433
Azure LB Health Probe Ports	59999 (listener), 58888 (cluster IP, опц.)

Предварительный чеклист (каждый узел)

Не является контроллером домена; все узлы в одном домене.
Синхронизация времени и DNS.
Идентичные версии ОС и патчи (например, Windows Server 2019+).
Отключено энергосбережение NIC.
Одинаковая структура дисков: Data, Logs, TempDB.
Доступ к *.core.windows.net.
Существует аккаунт CORP\sqlsvc

AD‑объекты и права

Предварительно создайте CNO (имя: SQLCLUSTER) в OU, которым вы управляете. Дайте ему права на создание VCO внутри OU.

Установка WSFC на каждом узле

Install‑WindowsFeature Failover‑Clustering -IncludeManagementTools
Restart‑Computer
Test‑Cluster -Node SQLNODE1,SQLNODE2,SQLNODE‑AZ
New‑Cluster -Name SQLCLUSTER `
  -Node SQLNODE1,SQLNODE2,SQLNODE‑AZ `
  -StaticAddress 170.xx.xx.30

Конфигурация кворума – Azure Cloud Witness

Set‑ClusterQuorum -CloudWitness `
  -AccountName "sqlwitnessacct" `
  -AccessKey "REDACTED_ACCESS_KEY"

Настройки WSFC для AG в нескольких подсетях

Get‑ClusterResource "SQL Network Name (SQLLISTENER)" | Set‑ClusterParameter HostRecordTTL 300
Get‑ClusterResource "SQL Network Name (SQLLISTENER)" | Set‑ClusterParameter RegisterAllProvidersIP 0
(Get‑ClusterGroup "Cluster Group").PreferredOwners = @("SQLNODE1","SQLNODE2","SQLNODE‑AZ")

Установка и подготовка SQL Server на узлах

Установите SQL Server Standalone на каждом узле, включите Always On.

CREATE ENDPOINT [Hadr_endpoint]
    STATE = STARTED
    AS TCP (LISTENER_PORT = 5022)
    FOR DATABASE_MIRRORING (ROLE = ALL);
ALTER AUTHORIZATION ON ENDPOINT::[Hadr_endpoint] TO [CORP\sqlsvc];

Создание Availability Group + Listener

CREATE AVAILABILITY GROUP [AG1]
  WITH (AUTOMATED_BACKUP_PREFERENCE = SECONDARY)
  FOR DATABASE [AlwaysOnTest]
  REPLICA ON
    N'SQLNODE1'   WITH (ENDPOINT_URL = N'TCP://SQLNODE1.corp.example.com:5022',
                        AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
                        FAILOVER_MODE = AUTOMATIC,
                        SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY)),
    N'SQLNODE2'   WITH (ENDPOINT_URL = N'TCP://SQLNODE2.corp.example.com:5022',
                        AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
                        FAILOVER_MODE = AUTOMATIC,
                        SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY)),
    N'SQLNODE‑AZ' WITH (ENDPOINT_URL = N'TCP://SQLNODE‑AZ.corp.example.com:5022',
                        AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
                        FAILOVER_MODE = MANUAL,
                        SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));

Теперь у вас есть полностью рабочий гибридный Always On AG: два синхронных узла с автоматическим failover и DR‑узел в Azure. Использование Cloud Witness обеспечивает стабильный кворум без зависимости от файловых шар.

Часть 2 — Шаги установки и подготовки SQL Server

Подготовка серверов и учётных записей

CORP\sqlsvc = сервисный аккаунт SQL Server.
Должен быть локальным администратором на каждом узле (или вручную выдать нужные права).
Типовые права (назначаются через GPO): Log on as a service, Lock pages in memory, Impersonate a client, Debug programs, Bypass traverse checking, Backup files и др.

Установка SQL Server (каждый узел)

Запустите New SQL Server stand-alone installation (не Failover Cluster Install).
Выберите роли: Database Engine Services (при необходимости добавьте Full-Text/Replication).
Инстанс: default или именованный (одинаковый на всех узлах).
Collation: одинаковый.
Каталоги:
- D:\Data — пользовательские базы;
- E:\Logs — журналы транзакций;
- F:\TempDB — TempDB.
Завершите установку; поставьте последний CU (одинаковая сборка на всех узлах).

Включение Always On

Через SQL Server Configuration Manager → SQL Server (MSSQLSERVER) → Enable Always On Availability Groups → перезапуск службы.

Создание HADR Endpoints (если отсутствуют)

CREATE ENDPOINT [Hadr_endpoint]
    STATE = STARTED
    AS TCP (LISTENER_PORT = 5022)
    FOR DATABASE_MIRRORING (ROLE = ALL);
GO

ALTER AUTHORIZATION ON ENDPOINT::[Hadr_endpoint] TO [CORP\sqlsvc];
GO

Smoke-тест соединений

Откройте firewall на TCP 5022.

Проверьте соединения:

telnet 170.xx.xx.11 5022
telnet 170.xx.xx.12 5022
telnet 170.xx.xx.21 5022

Тестовая база

CREATE DATABASE AlwaysOnTest
ON (NAME = AlwaysOnTest_Data, FILENAME = 'D:\Data\AlwaysOnTest.mdf', SIZE = 128MB),
   (NAME = AlwaysOnTest_Log , FILENAME = 'E:\Logs\AlwaysOnTest.ldf', SIZE = 64MB);
GO

BACKUP DATABASE AlwaysOnTest TO DISK = 'D:\Backups\AlwaysOnTest.bak' WITH INIT;
BACKUP LOG AlwaysOnTest TO DISK = 'D:\Backups\AlwaysOnTest.trn' WITH INIT;

На вторичных узлах восстановите с NORECOVERY (если не используете auto seeding).

Часть 3 — Создание Availability Group + Listener (multi-subnet)

Создание AG (AG1)

В SSMS на SQLNODE1: Always On High Availability → New Availability Group Wizard.

Реплики:
- SQLNODE1 — синхронная, авто‑фейловер = Да
- SQLNODE2 — синхронная, авто‑фейловер = Да
- SQLNODE-AZ — асинхронная, авто‑фейловер = Нет (DR)
Readable Secondary: Да
Seeding: Auto seeding или Full/Log restore
DB: AlwaysOnTest (FULL recovery + бэкап)

T-SQL (скелет)

CREATE AVAILABILITY GROUP [AG1]
  WITH (AUTOMATED_BACKUP_PREFERENCE = SECONDARY)
  FOR DATABASE [AlwaysOnTest]
  REPLICA ON
    N'SQLNODE1'   WITH (...),
    N'SQLNODE2'   WITH (...),
    N'SQLNODE-AZ' WITH (...);
GO

Создание Listener

DNS: SQLLISTENER
Порт: 1433
IP: 170.xx.xx.50 (on-prem), 170.xx.xx.60 (Azure)

Клиенты должны использовать MultiSubnetFailover=True.

Azure Internal Load Balancer (ILB)

Создайте ILB, статический IP = 170.xx.xx.60.
Backend pool: добавьте SQLNODE-AZ.
Health probe: TCP 59999, interval 5 сек, threshold 2.
Load balancing rules:
- Rule A (Listener): Frontend = 170.xx.xx.60, TCP 1433, Probe 59999, Floating IP=On.
- (Опция) Rule B (Cluster Core): TCP 3343 + отдельный probe.

Проверка AG и Listener

SELECT ag.name, ar.replica_server_name, ars.role_desc
FROM sys.availability_groups ag
JOIN sys.availability_replicas ar ON ag.group_id = ar.group_id
JOIN sys.dm_hadr_availability_replica_states ars ON ar.replica_id = ars.replica_id;

Часть 4 — Read-Only Routing, оптимизация и проверка

Включение Readable Secondaries

ALTER AVAILABILITY GROUP [AG1]
  MODIFY REPLICA ON N'SQLNODE1' WITH (SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));
ALTER AVAILABILITY GROUP [AG1]
  MODIFY REPLICA ON N'SQLNODE2' WITH (SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));
ALTER AVAILABILITY GROUP [AG1]
  MODIFY REPLICA ON N'SQLNODE-AZ' WITH (SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));

Определение Routing URLs

ALTER AVAILABILITY GROUP [AG1]
  MODIFY REPLICA ON N'SQLNODE1'
  WITH (SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://SQLNODE1.corp.example.com:1433'));
-- Аналогично для SQLNODE2 и SQLNODE-AZ

Настройка Routing Lists

ALTER AVAILABILITY GROUP [AG1]
  MODIFY REPLICA ON N'SQLNODE1'
  WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST = ('SQLNODE2','SQLNODE-AZ','SQLNODE1')));

Connection string для R/O нагрузки

Server=SQLLISTENER;Database=YourDB;
ApplicationIntent=ReadOnly;MultiSubnetFailover=True;Encrypt=True;TrustServerCertificate=True;

Прочее (Kerberos, SPN, Firewall)

SPN для SQLLISTENER и всех узлов.
Firewall: TCP 1433 (клиенты), TCP 5022 (HADR), TCP 3343 (кластер), TCP 59999 (ILB probe).
Кластерные логи: Get-ClusterLog -UseLocalTime -Destination C:\ClusterLogs.

Финальный чеклист

WSFC на 3 узлах, Cloud Witness работает.
SQL с одинаковым CU.
HADR endpoints на 5022.
AG1 создан (2 sync + 1 async).
Listener с 2 IP.
Azure ILB настроен.
Read-only routing работает.
MultiSubnetFailover включен у клиентов.
Failover протестирован (on-prem и DR).

Заключение

Теперь у вас гибридный Always On AG: 2 синхронных on-prem узла для HA, 1 асинхронный Azure‑узел для DR, Cloud Witness для кворума, Read-Only Routing для разгрузки. Клиенты используют Listener (SQLLISTENER) для подключения.

MS SQL Server дело тонкое...

22.8.25

AlwaysOn Availability Groups – пошаговая настройка гибридной конфигурации