SQL Server 管理系列（第四期）：高可用性——Always On 可用性组与故障转移集群

绩隐金
29 阅读9分钟

SQL Server 管理系列（第四期）：高可用性——Always On 可用性组与故障转移集群

上一期我们深入了备份与恢复，这是数据安全的“最后一道防线”。但备份恢复需要时间——从发现故障到恢复完成，可能需要数小时。对于 7x24 小时业务来说，这个窗口太长了。这一期，我们进入**高可用性（High Availability, HA）**领域。你是否遇到过：半夜接到报警说服务器宕机？主库维护时整个系统停摆？跨机房的灾备切换需要手工操作数十分钟？高可用架构就是解决这些问题的答案。

一、高可用性概述

1.1 高可用 vs 灾难恢复

维度高可用性（HA）灾难恢复（DR）
目标应对组件级故障应对站点级灾难
范围同一数据中心内跨数据中心/地域
RTO秒级到分钟级分钟级到小时级
典型方案Always On AG、FCI分布式 AG、日志传送
成本中等高

核心指标：

  • RTO（恢复时间目标）：从故障发生到恢复服务的时间
  • RPO（恢复点目标）：可容忍的最大数据丢失量

1.2 SQL Server 高可用方案全景

方案保护级别自动故障转移读写分离跨平台适用场景
Always On AG数据库级✅✅Win/Linux现代推荐方案
故障转移集群（FCI）实例级✅❌Win传统企业环境
日志传送数据库级❌ 手动✅（只读）Win/Linux低成本/异地
数据库镜像数据库级✅❌仅 Win⚠️ 已弃用

📌 版本说明：数据库镜像在 SQL Server 2012 中已被标记为弃用，建议新项目使用 Always On AG。

二、Always On 可用性组（AG）

2.1 架构概览

Always On 可用性组是微软主推的现代化高可用方案，在 Windows 和 Linux 上都可用。

┌─────────────────────────────────────────────────────────────┐
│                      应用程序                                │
│                         │                                    │
│                    ┌────┴────┐                              │
│                    │ 侦听器   │  ← 虚拟 IP + 端口            │
│                    └────┬────┘                              │
│         ┌───────────────┼───────────────┐                   │
│         ▼               ▼               ▼                   │
│   ┌──────────┐    ┌──────────┐    ┌──────────┐             │
│   │ 主副本    │◄──►│ 辅助副本1 │◄──►│ 辅助副本2 │             │
│   │ 读/写     │同步 │ 只读     │同步 │ 只读     │             │
│   └──────────┘    └──────────┘    └──────────┘             │
│         ▲               ▲               ▲                   │
│         └───────────────┴───────────────┘                   │
│                    基础集群（WSFC/Pacemaker）                │
└─────────────────────────────────────────────────────────────┘

核心组件：

  • 可用性组：一组一起进行故障转移的数据库
  • 副本：SQL Server 实例上的数据库副本（1主 + 最多8辅）
  • 侦听器：提供统一连接点的虚拟网络名称
  • 同步模式：同步提交 vs 异步提交

2.2 同步 vs 异步提交

模式数据丢失性能影响适用场景
同步提交RPO=0事务需等待副本确认同城 HA
异步提交可能丢失数据主库不受影响跨地域 DR
-- 查看可用性组配置
SELECT 
    ag.name AS AvailabilityGroup,
    ar.replica_server_name,
    rs.role_desc,
    rs.synchronization_health_desc,
    ar.availability_mode_desc,  -- SYNCHRONOUS_COMMIT / ASYNCHRONOUS_COMMIT
    ar.failover_mode_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 rs ON ar.replica_id = rs.replica_id

2.3 创建可用性组

前置条件：

  • 所有节点安装相同版本的 SQL Server（Enterprise 推荐）
  • 配置 Windows 故障转移集群（WSFC）或 Linux Pacemaker
  • 数据库需为完整恢复模式
  • 已完成完整备份

T-SQL 创建：

-- 1. 在主副本上创建端点
CREATE ENDPOINT [Hadr_endpoint]
    STATE=STARTED
    AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
    FOR DATA_MIRRORING (ROLE = ALL, AUTHENTICATION = WINDOWS NEGOTIATE
, ENCRYPTION = REQUIRED ALGORITHM AES)

-- 2. 创建可用性组（Windows + WSFC）
CREATE AVAILABILITY GROUP [AG_YourDB]
    WITH (AUTOMATED_BACKUP_PREFERENCE = PRIMARY)
    FOR DATABASE [YourDB]
    REPLICA ON 
        N'SQLNode1' WITH (
            ENDPOINT_URL = N'TCP://SQLNode1.contoso.com:5022',
            AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
            FAILOVER_MODE = AUTOMATIC,
            SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY)
        ),
        N'SQLNode2' WITH (
            ENDPOINT_URL = N'TCP://SQLNode2.contoso.com:5022',
            AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
            FAILOVER_MODE = AUTOMATIC,
            SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY)
        )
    LISTENER 'AGListener' (WITH IP (('10.0.0.10', '255.255.255.0')), PORT = 1433);

-- 3. 加入辅助副本
-- 在辅助副本上执行：
ALTER AVAILABILITY GROUP [AG_YourDB] JOIN;
ALTER DATABASE [YourDB] SET HADR AVAILABILITY GROUP = [AG_YourDB];

-- 4. 创建侦听器（如未在创建时指定）
ALTER AVAILABILITY GROUP [AG_YourDB]
    ADD LISTENER 'AGListener' (
        WITH IP (('10.0.0.10', '255.255.255.0')), 
        PORT = 1433
    );

2.4 Linux 上的 AG（Pacemaker）

Linux 上的 SQL Server AG 使用 Pacemaker 作为集群管理器，而非 WSFC。

关键差异：

  • 集群类型必须设置为 EXTERNAL
  • 需要至少 3 个副本（仲裁要求）
  • 侦听器通过 DNS 实现，而非集群资源
# 1. 安装 Pacemaker
yum install pacemaker pcs fence-agents-all

# 2. 创建可用性组（集群类型 = EXTERNAL）

-- 在 Linux 主节点执行
CREATE AVAILABILITY GROUP [AG_YourDB]
    WITH (CLUSTER_TYPE = EXTERNAL)  -- 关键差异！
    FOR DATABASE [YourDB]
    REPLICA ON 
        N'SQLNode1' WITH (ENDPOINT_URL = N'tcp://SQLNode1:5022', 
                          AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
                          FAILOVER_MODE = EXTERNAL),
        N'SQLNode2' WITH (ENDPOINT_URL = N'tcp://SQLNode2:5022',
                          AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
                          FAILOVER_MODE = EXTERNAL)

2.5 可用性组监控

-- 查看同步状态
SELECT 
    DB_NAME(database_id) AS DBName,
    synchronization_state_desc,
    synchronization_health_desc,
    last_commit_time,
    log_send_queue_size,
    log_send_rate,
    redo_queue_size
FROM sys.dm_hadr_database_replica_states

-- 查看可用性组健康状态
SELECT 
    ag.name,
    rs.role_desc,
    rs.connected_state_desc,
    rs.operational_state_desc,
    rs.synchronization_health_desc
FROM sys.availability_groups ag
JOIN sys.dm_hadr_availability_replica_states rs 
    ON ag.group_id = rs.group_id

三、故障转移集群实例（FCI）

3.1 FCI 架构

FCI 是传统的实例级高可用方案，依赖共享存储。

┌─────────────────────────────────────────┐
│           共享存储（SAN/iSCSI/NFS）      │
│    ┌─────────────────────────────────┐  │
│    │     SQL Server 数据文件（共享）   │  │
│    └─────────────────────────────────┘  │
└─────────────────────────────────────────┘
         ▲                    ▲
         │                    │
    ┌────┴────┐          ┌────┴────┐
    │ 节点1    │          │ 节点2    │
    │ 主动     │◄────────►│ 被动     │
    │ SQL实例  │  心跳     │ 待命     │
    └─────────┘          └─────────┘

特点：

  • 实例级保护（整个 SQL Server 实例）
  • 共享存储是单点故障（需存储自身高可用）
  • 不支持读写分离
  • 故障转移对应用透明（相同实例名）

3.2 FCI vs AG 对比

维度FCIAG
保护粒度整个实例单个数据库
存储要求共享存储本地存储即可
只读副本❌✅
故障转移时间秒级秒级
配置复杂度中等较高
跨数据中心困难（需存储复制）支持

3.3 创建 FCI（Windows）

# 1. 安装 WSFC
# 使用故障转移集群管理器创建集群

# 2. 通过 SQL Server 安装程序选择“新建 SQL Server 故障转移集群安装”

# 3. 安装过程中配置：
#    - 集群网络名称（虚拟名称）
#    - 共享存储路径
#    - IP 地址资源

四、日志传送（Log Shipping）

4.1 架构与原理

日志传送是一种轻量级的异步数据复制方案，通过定时传输事务日志实现。

┌─────────────┐     ┌─────────────┐     ┌─────────────┐
│  主服务器    │     │  辅助服务器  │     │  监控服务器  │
│             │     │             │     │  （可选）    │
│  ┌───────┐  │     │  ┌───────┐  │     │             │
│  │ 备份   │──┼────►│  │ 复制   │──┼────►│  监控与告警  │
│  │ 作业   │  │     │  │ 作业   │  │     │             │
│  └───────┘  │     │  └───────┘  │     │             │
│     │       │     │     │       │     │             │
│     ▼       │     │     ▼       │     │             │
│  ┌───────┐  │     │  ┌───────┐  │     │             │
│  │ 共享   │  │     │  │ 还原   │  │     │             │
│  │ 文件夹 │  │     │  │ 作业   │  │     │             │
│  └───────┘  │     │  └───────┘  │     │             │
└─────────────┘     └─────────────┘     └─────────────┘

三个作业：

  1. 备份作业：在主服务器上备份事务日志
  2. 复制作业：将日志文件复制到辅助服务器
  3. 还原作业：在辅助服务器上还原日志

4.2 配置日志传送

-- 在主服务器上执行
USE master;

-- 1. 设置主数据库
EXEC sp_add_log_shipping_primary_database 
    @database = N'YourDB',
    @backup_directory = N'D:\LogShipping\Backup',  -- 本地备份目录
    @backup_share = N'\\PrimaryServer\LogShipping', -- 网络共享路径
    @backup_job_name = N'LSBackup_YourDB',
    @backup_retention_period = 4320,  -- 3天保留
    @backup_threshold = 60,  -- 60分钟无备份则告警
    @threshold_alert_enabled = 1;

-- 2. 添加辅助数据库（在辅助服务器上）
EXEC sp_add_log_shipping_secondary_database 
    @secondary_database = N'YourDB_Secondary',
    @primary_server = N'PrimaryServer',
    @primary_database = N'YourDB',
    @restore_delay = 0,  -- 立即还原
    @restore_mode = 2,   -- STANDBY 模式（可读）
    @copy_directory = N'D:\LogShipping\Copy',
    @restore_directory = N'D:\LogShipping\Restore';

4.3 故障转移与恢复

-- 手动故障转移（辅助→主）
-- 1. 在辅助服务器上应用所有日志
-- 2. 恢复辅助数据库
RESTORE DATABASE YourDB_Secondary WITH RECOVERY;

-- 3. 重定向应用连接
-- 4. 重建日志传送（反向）

-- 查看日志传送状态
SELECT 
    primary_database,
    secondary_server,
    secondary_database,
    last_backup_date,
    last_copied_date,
    last_restored_date,
    threshold_alert_enabled
FROM msdb.dbo.log_shipping_monitor_primary;

五、方案选型指南

5.1 决策树

开始
 │
 ├─ 是否需要自动故障转移？
 │   ├─ 是 → 继续
 │   └─ 否 → 考虑日志传送
 │
 ├─ 是否需要读写分离？
 │   ├─ 是 → Always On AG
 │   └─ 否 → 继续
 │
 ├─ 是否有共享存储？
 │   ├─ 是 → FCI 或 AG 均可
 │   └─ 否 → Always On AG（本地存储）
 │
 ├─ 是否跨操作系统平台？
 │   ├─ 是 → AG（Win/Linux 均支持）
 │   └─ 否 → 均可
 │
 └─ 推荐：Always On AG（现代首选）

5.2 各场景推荐方案

业务场景推荐方案理由
关键 OLTPAlways On AG（同步）RPO=0，自动故障转移
读写分离Always On AG辅助副本可读
传统企业（已有 SAN）FCI利用现有存储投资
跨地域 DR分布式 AG + 异步兼顾性能与容灾
低成本/报表服务器日志传送配置简单，成本低
开发/测试环境无高可用 + 定期备份降低成本

5.3 版本限制

功能Standard EditionEnterprise Edition
可用性组基本可用性组（仅 2 副本，1 DB）完整 AG（最多 9 副本）
故障转移集群✅✅
可读辅助副本❌✅
自动故障转移✅✅
分布式 AG❌✅

六、监控与维护

6.1 高可用健康检查脚本

-- 综合健康检查
SELECT 
    ag.name AS AGName,
    ar.replica_server_name,
    rs.role_desc,
    rs.synchronization_health_desc,
    DB_NAME(drs.database_id) AS DBName,
    drs.synchronization_state_desc,
    drs.log_send_queue_size,
    drs.redo_queue_size,
    DATEDIFF(SECOND, drs.last_commit_time, GETDATE()) AS SecondsBehind
FROM sys.availability_groups ag
JOIN sys.dm_hadr_availability_replica_states rs 
    ON ag.group_id = rs.group_id
JOIN sys.availability_replicas ar 
    ON rs.replica_id = ar.replica_id
LEFT JOIN sys.dm_hadr_database_replica_states drs 
    ON rs.replica_id = drs.replica_id
WHERE rs.role_desc = 'SECONDARY'

6.2 警报配置

-- 创建 Agent 作业监控 AG 状态
-- 当同步健康状态不是 HEALTHY 时告警
IF EXISTS (
    SELECT 1 FROM sys.dm_hadr_availability_replica_states
    WHERE synchronization_health_desc != 'HEALTHY'
)
BEGIN
    EXEC msdb.dbo.sp_send_dbmail
        @recipients = 'dba@company.com',
        @subject = 'Always On 同步异常',
        @body = '请检查可用性组同步状态';
END

七、核心总结

知识点核心要点
Always On AG现代化推荐方案，支持读写分离，Win/Linux 通用
FCI传统方案，依赖共享存储，实例级保护
日志传送轻量级异步复制，适合低成本/异地场景
同步 vs 异步同步保数据（RPO=0），异步保性能
RTO/RPO高可用方案 ≠ 备份，两者需要配合使用
监控定期检查同步延迟和健康状态
选型原则需求驱动——RPO、RTO、预算、技能综合考量

一句话记住本期内容：

高可用是业务连续性的保障——RPO=0 用同步 AG，读负载大用辅助副本，预算低用日志传送，但任何方案都不能替代备份。

动手练习

-- 场景：电商系统需要 7x24 小时运行，RPO=0，RTO<60秒
-- 问题1：选择什么高可用方案？

-- 问题2：如何验证辅助副本的只读查询功能？

-- 问题3：写出查看 AG 同步延迟的查询
点击查看参考答案

问题1：Always On 可用性组（同步提交模式 + 自动故障转移）。配置 2-3 个副本，同步提交确保 RPO=0，自动故障转移满足 RTO<60秒。

问题2：

-- 在辅助副本上执行
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT * FROM YourDB.dbo.Orders;  -- 需要配置辅助副本允许只读连接

问题3：

SELECT 
    DB_NAME(database_id) AS DB,
    DATEDIFF(SECOND, last_commit_time, GETDATE()) AS SecondsBehind
FROM sys.dm_hadr_database_replica_states
WHERE is_primary_replica = 0

下一期预告

自动化运维——SQL Agent 作业与多服务器管理

  • SQL Agent 作业的创建、调度与监控
  • 多服务器管理（主服务器/目标服务器）
  • 警报与操作员配置
  • 使用 PowerShell 批量管理
  • 运维自动化最佳实践

📌 本文配置适用于 SQL Server 2016-2022。Windows 和 Linux 上的高可用配置有差异，请根据实际环境选择。

管理系列持续更新中，点击关注不错过第五期。

avatar
文章
阅读
粉丝