如何在Ubuntu上配置SQL Server的高可用性

在 Ubuntu 上配置 SQL Server 高可用性

一 方案选择与前提

• 推荐方案：使用 Always On 可用性组（AG），在 Ubuntu 20.04/22.04 上配合 Pacemaker/Corosync 实现自动故障转移与集群管理。Linux 上的 AG 需将 CLUSTER_TYPE 设置为 EXTERNAL，由外部集群接管。
• 前提条件：
  • 计算机名长度必须少于 15 个字符，网络唯一；主机名解析正确（建议通过 /etc/hosts 保证短名可解析为真实 IP）。
  • 在所有节点启用 hadr.hadrenabled=1 并重启 SQL Server。
  • 为数据库与 AG 准备一致的 排序规则/兼容级别，并确保数据库处于 FULL 恢复模式。
  • 安装 mssql-server-ha 资源代理，用于与 Pacemaker 集成。
  • 开放网络与防火墙端口：1433（客户端/侦听器）、5022（数据库镜像端点）。

二 安装与基础配置

• 安装 SQL Server（示例为 Ubuntu 20.04/22.04 的 APT 源方式）：
  • 导入 GPG 密钥并添加 Microsoft 仓库，执行：
    • wget -qO- https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add -
    • sudo add-apt-repository “$(wget -qO- https://packages.microsoft.com/config/ubuntu/$(lsb_release -rs)/prod.list)”
    • sudo apt-get update && sudo apt-get install -y mssql-server
  • 运行 sudo /opt/mssql/bin/mssql-conf setup 完成 SA 密码与实例初始化。
• 启用 Always On：
  • sudo /opt/mssql/bin/mssql-conf set hadr.hadrenabled 1
  • sudo systemctl restart mssql-server
• 安装高可用代理：
  • sudo apt-get install -y mssql-server-ha
• 主机名与解析：
  • 确保 /etc/hostname 短名 ≤ 15 字符；在 /etc/hosts 中为各节点配置短名到真实 IP 的解析，避免 127.0.1.1 覆盖主机名解析。
• 防火墙放行：
  • sudo ufw allow 1433,5022/tcp 或按环境放行相应端口。

三 配置 Always On 可用性组

• 在所有节点创建数据库镜像端点（证书认证）：
  • 创建登录与用户：
    • CREATE LOGIN dbm_login WITH PASSWORD = ‘’;
    • CREATE USER dbm_user FOR LOGIN dbm_login;
  • 主节点创建主密钥与证书并备份：
    • CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘’;
    • CREATE CERTIFICATE dbm_certificate WITH SUBJECT = ‘dbm’;
    • BACKUP CERTIFICATE dbm_certificate TO FILE = ‘/var/opt/mssql/data/dbm_certificate.cer’
      WITH PRIVATE KEY (FILE = ‘/var/opt/mssql/data/dbm_certificate.pvk’, ENCRYPTION BY PASSWORD = ‘’);
  • 将证书拷贝到各副本节点（/var/opt/mssql/data），在副本节点上：
    • CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘’;
    • CREATE CERTIFICATE dbm_certificate AUTHORIZATION dbm_user
      FROM FILE = ‘/var/opt/mssql/data/dbm_certificate.cer’
      WITH PRIVATE KEY (FILE = ‘/var/opt/mssql/data/dbm_certificate.pvk’, DECRYPTION BY PASSWORD = ‘’);
  • 在各节点创建端点（示例端口 5022）：
    • CREATE ENDPOINT [Hadr_endpoint]
      AS TCP (LISTENER_PORT = 5022)
      FOR DATABASE_MIRRORING (ROLE = ALL, AUTHENTICATION = CERTIFICATE dbm_certificate, ENCRYPTION = REQUIRED ALGORITHM AES);
    • ALTER ENDPOINT [Hadr_endpoint] STATE = STARTED;
    • GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO [dbm_login];
• 在主副本创建可用性组（示例名称 ag1）：
  • CREATE AVAILABILITY GROUP [ag1]
    WITH (CLUSTER_TYPE = EXTERNAL)
    FOR DATABASE [YourDB]
    REPLICA ON
    N’node1’ WITH (ENDPOINT_URL = N’TCP://node1:5022’, AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, FAILOVER_MODE = EXTERNAL, SEEDING_MODE = AUTOMATIC),
    N’node2’ WITH (ENDPOINT_URL = N’node2:5022’, AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, FAILOVER_MODE = EXTERNAL, SEEDING_MODE = AUTOMATIC);
• 在辅助副本加入 AG：
  • ALTER AVAILABILITY GROUP [ag1] JOIN WITH (CLUSTER_TYPE = EXTERNAL);
  • ALTER AVAILABILITY GROUP [ag1] GRANT CREATE ANY DATABASE;
• 将数据库加入 AG（在主副本）：
  • ALTER DATABASE [YourDB] SET HADR AVAILABILITY GROUP = [ag1];
• 建议开启诊断事件会话：
  • ALTER EVENT SESSION AlwaysOn_health ON SERVER STATE = START;
• 验证：
  • SELECT ag.name, ags.primary_replica, ags.synchronization_health_desc
    FROM sys.availability_groups AS ag
    JOIN sys.dm_hadr_availability_group_states AS ags ON ag.group_id = ags.group_id;
  • 在各副本检查端点与连接：
    • SELECT * FROM sys.dm_tcp_listener_states WHERE listener_id = (SELECT endpoint_id FROM sys.endpoints WHERE name = N’Hadr_endpoint’);

四 配置 Pacemaker 与自动故障转移

• 创建 Pacemaker 管理用的 SQL 登录并授予权限：
  • USE [master]
  • CREATE LOGIN [pacemakerLogin] WITH PASSWORD = N’';
  • ALTER SERVER ROLE [sysadmin] ADD MEMBER [pacemakerLogin];
  • 或最小权限集：
    • GRANT ALTER, CONTROL, VIEW DEFINITION ON AVAILABILITY GROUP::ag1 TO pacemakerLogin;
• 安全存储凭据：
  • echo ‘pacemakerLogin ’ | sudo tee /var/opt/mssql/secrets/passwd
  • sudo chown root:root /var/opt/mssql/secrets/passwd
  • sudo chmod 400 /var/opt/mssql/secrets/passwd
• 创建集群资源（Pacemaker，示例）：
  • 创建 AG 资源（ocf:mssql:ag）：
    • sudo pcs resource create ag_cluster ocf:mssql:ag ag_name=ag1 --master meta notify=true
  • 创建虚拟 IP（示例 10.2.38.204）：
    • sudo pcs resource create virtualip ocf:heartbeat:IPaddr2 ip=10.2.38.204
  • 设置启动顺序与依赖（示例）：
    • sudo pcs constraint colocation add virtualip with master ag_cluster INFINITY
    • sudo pcs constraint order promote ag_cluster then start virtualip
• 常用运维命令：
  • 查看资源：pcs status
  • 切换主副本：pcs resource move ag_cluster node2 --master
  • 清理约束：pcs resource clear ag_cluster
• 说明：Linux 上的 AG 由 Pacemaker 管理，故障转移由集群策略触发；AG 的 CLUSTER_TYPE 必须为 EXTERNAL。

五 验证与常见问题

• 验证要点：
  • 各节点可互相解析短名（ping 主机名应返回真实 IP，避免 127.0.1.1）。
  • 端口 1433/5022 已放行，端点处于 STARTED 状态。
  • 数据库处于 FULL 恢复模式并已成功加入 AG（sys.dm_hadr_database_replica_states 健康）。
  • Pacemaker 资源运行正常，虚拟 IP 漂移到主副本。
• 常见问题与处理：
  • 主机名 > 15 字符会导致 AG 配置失败；请缩短主机名。
  • 证书/私钥权限或路径错误会导致端点无法认证；确保 /var/opt/mssql/data 下文件属主为 mssql:mssql 且权限正确。
  • 未设置 CLUSTER_TYPE = EXTERNAL 将无法由 Pacemaker 接管；请在 CREATE/ALTER AG 时指定。
  • 副本加入失败可检查端点连通性、证书是否一致、数据库恢复模式与备份链是否完整。
  • 防火墙/安全组未放行 5022 会导致副本无法同步；请放行相应端口。