Настройка SQL Server Always On Availability Group

Симптом

Цель: настроить автоматическую отработку отказа и читаемую secondary-реплику для SQL Server с Always On Availability Group (AG).

1. Настройте Windows Server Failover Cluster (WSFC)

Always On опирается на WSFC. Установите компонент Failover Clustering на всех узлах и настройте сеть и witness. Создайте кластер с помощью PowerShell.

SQL Server

-- PowerShell (the Failover-Clustering feature must be installed on every node):
New-Cluster -Name sqlclu -Node node1,node2 -StaticAddress 10.0.0.50
-- Witness:
Set-ClusterQuorum -NodeAndFileShareMajority \\fs01\quorum

2. Включите Always On на каждом экземпляре

Включите компонент Always On Availability Groups через SQL Server Configuration Manager или PowerShell. Затем перезапустите службу SQL Server. Это связывает членство в кластере с экземпляром.

SQL Server

-- PowerShell:
Enable-SqlAlwaysOn -ServerInstance node1\SQL01 -Force
Enable-SqlAlwaysOn -ServerInstance node2\SQL01 -Force

3. Сделайте бэкап базы данных и восстановите его на secondary WITH NORECOVERY

База данных, добавляемая в AG, должна быть в модели восстановления FULL. Сделайте полный бэкап и бэкап журнала на primary; восстановите их на secondary WITH NORECOVERY (чтобы она могла продолжать получать redo). Это ручной seeding; альтернатива — automatic seeding.

SQL Server

-- On the primary:
BACKUP DATABASE app TO DISK='\\fs01\bak\app.bak';
BACKUP LOG app TO DISK='\\fs01\bak\app.trn';
-- On the secondary:
RESTORE DATABASE app FROM DISK='\\fs01\bak\app.bak' WITH NORECOVERY;
RESTORE LOG app FROM DISK='\\fs01\bak\app.trn' WITH NORECOVERY;

4. Создайте endpoints и определите Availability Group

Создайте HADR endpoint на порту 5022 для трафика redo между узлами. Синхронный режим (SYNCHRONOUS_COMMIT) обеспечивает автоматическую отработку отказа без потери данных; асинхронный (ASYNCHRONOUS_COMMIT) работает с отставанием, но производителен для удалённого DR.

SQL Server

CREATE ENDPOINT hadr_endpoint STATE=STARTED
  AS TCP (LISTENER_PORT=5022)
  FOR DATA_MIRRORING (ROLE=ALL, ENCRYPTION=REQUIRED ALGORITHM AES);

CREATE AVAILABILITY GROUP appag
  FOR DATABASE app
  REPLICA ON
    'NODE1\SQL01' WITH (ENDPOINT_URL='TCP://node1:5022', AVAILABILITY_MODE=SYNCHRONOUS_COMMIT, FAILOVER_MODE=AUTOMATIC, SECONDARY_ROLE(ALLOW_CONNECTIONS=ALL)),
    'NODE2\SQL01' WITH (ENDPOINT_URL='TCP://node2:5022', AVAILABILITY_MODE=SYNCHRONOUS_COMMIT, FAILOVER_MODE=AUTOMATIC, SECONDARY_ROLE(ALLOW_CONNECTIONS=ALL));

5. Присоедините secondary к AG и добавьте базу данных

Выполните JOIN к AG на экземпляре secondary, затем добавьте восстановленную базу данных в AG. Этот шаг нужен при ручном seeding; при automatic seeding это выполняется на primary через SET(SEEDING_MODE).

SQL Server

-- On the secondary:
ALTER AVAILABILITY GROUP appag JOIN;
ALTER DATABASE app SET HADR AVAILABILITY GROUP = appag;

6. Создайте Availability Group Listener

Listener — виртуальное имя и IP, к которым подключаются приложения; после отработки отказа подключения автоматически перенаправляются на новый primary. Используйте это имя и MultiSubnetFailover=True в строке подключения приложения.

SQL Server

ALTER AVAILABILITY GROUP appag
  ADD LISTENER 'appag-listener' (WITH IP ((N'10.0.0.60', N'255.255.255.0')), PORT=1433);

7. Протестируйте отработку отказа и проверьте состояние синхронизации

Когда синхронизация в состоянии 'HEALTHY', протестируйте смену ролей плановой отработкой отказа. Для автоматической отработки отказа режим должен быть синхронным и FAILOVER_MODE=AUTOMATIC. Отслеживайте состояние через sys.dm_hadr_availability_replica_states.

SQL Server

SELECT ag.name, ars.role_desc, ars.synchronization_health_desc
FROM sys.dm_hadr_availability_replica_states ars
JOIN sys.availability_groups ag ON ag.group_id=ars.group_id;
-- Planned failover (on the node that will become the new primary):
ALTER AVAILABILITY GROUP appag FAILOVER;