SQL Server Always On 可用性组搭建

症状

目标：使用 Always On 可用性组（AG）为 SQL Server 搭建自动故障转移和可读辅助副本。

1. 搭建 Windows Server 故障转移群集（WSFC）

Always On 依赖于底层的 WSFC。在所有节点上安装 Failover Clustering 功能，并配置网络和见证。用 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

使用 SQL Server Configuration Manager 或 PowerShell 启用 Always On 可用性组功能。然后重启 SQL Server 服务。这会将群集成员身份绑定到实例。

SQL Server

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

3. 备份数据库并在辅助副本上 WITH NORECOVERY 还原

要加入 AG 的数据库必须处于 FULL 恢复模式。在主副本上执行完整备份和日志备份；在辅助副本上 WITH NORECOVERY 还原（以便继续接收 redo）。这是手动初始化（manual 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. 创建端点并定义可用性组

在端口 5022 上创建 HADR 端点，用于节点之间的 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. 将辅助副本加入 AG 并加入数据库

在辅助实例上 JOIN AG，然后将已还原的数据库加入 AG。若使用手动初始化则需要此步骤；使用自动种子设定时，则在主副本上通过 SET(SEEDING_MODE) 处理。

SQL Server

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

6. 创建可用性组侦听器

侦听器是应用连接的虚拟名称和 IP；故障转移后，连接会自动重定向到新的主副本。在应用连接字符串中使用该名称和 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;

在 DBAtlas 中打开 →