亚马逊AWS官方博客

基于 AWS 中国（宁夏）区域的三个可用区结合 SQL Server Always On Linux 可用性组提升业务连续性

在由西云数据运营的 AWS 中国（宁夏）区域正式推出第3个可用区后，AWS 中国区域的用户可以更加灵活地来部署跨越3个可用区的应用程序及数据库架构，进一步加强系统高可用性和容错能力，并提升业务的连续性。

本文将重点介绍 SQL Server Always On Linux 可用性组在 AWS 中国（宁夏）区域的安装、配置、只读副本以及故障转移等。

(一) SQL Server Always On Linux 功能介绍

SQL Server 2017现在支持在 Linux 上运行，并使用相同的 SQL Server 数据库引擎，具有许多相似的功能和服务，且不受操作系统的影响。

从 SQL Server 2012 开始引入的 Always On 可用性组，它将数据库的每个事务发送到另一个实例，从而提供数据库级别的保护，该实例称为副本，其中包含处于特定状态的数据库副本。可用性组可部署在 Standard 版本或 Enterprise 版本上。参与可用性组的实例可以是独立实例，也可以是 Always On 故障转移群集实例。由于在事务发生时将它发送到副本，建议在需要较低 RPO 和 RTO 的情况下使用 Always On 可用性组。副本之间的数据移动可以是同步的或异步的，Enterprise 版允许同步多达三个副本（包括主副本）。

可用性组具有一个数据库的完全读/写副本且位于主副本上，而其他所有次要副本仅提供只读功能。

(二)  SQL Server Always On Linux 架构说明

Always On 可用性组的优点之一是可使用单个功能配置高可用性和灾难恢复。由于不需要确保共享存储也具有高可用性，可以更轻松地实现在一个数据中心内具有用于高可用性的本地副本，在其他数据中心内具有用于灾难恢复的远程备份，且每个备份都有单独的存储。确保冗余的代价是具有额外的数据库副本。

下面的示例为跨越多个数据中心的可用性组。一个主要副本负责确保所有次要副本保持同步。

(三)  Always On 部署拓扑

基于 AWS 中国（宁夏）区域3个可用区的 SQL Server Always On Linux 可用性组部署架构，具体参考如下：

在 AWS 中国（宁夏）区域通过以上的部署方式，SQL Server Always On Linux 可用性组将能实现如下目标：

  • 更低的 RTO 与 RPO
  • 支持读/写分离、扩展多个只读副本
  • 更高的高可用性
  • 更简化的部署流程

(四)  Always On 环境要求

基于AWS中国（宁夏）区域 EC2 计算资源配置清单如下所示：

下面将主要围绕 CentOS 7.4 来介绍 SQL Server Always On Linux 可用性组在 AWS 中国（宁夏）区域的安装及配置。请参考文档中心修改 EC2 主机名、调整操作系统时区、关闭操作系统防火墙、关闭 selinux、修改 VPC 安全组。

1)  SQL Server On Linux 安装脚本

请下载安装脚本，可以根据需要修改 MSSQL_SA_PASSWORD，SQL_INSTALL_USER 及  SQL_INSTALL_USER_PASSWORD 变量的值，SA 系统管理员的默认密码是 !Passw0rd，以 sudo 方式去运行 install-mssql.sh，采用的是国外 yum 源，速度可能不稳定。

2)  SQL Server 客户端工具（可选）

SQL Server 管理工具主要包括 Windows 平台的 SQL Server Management Studio (SSMS)、Visual Studio Code、服务器端的 sqlcmd & bcp 等，以下的客户端工具主要用于其他平台的远程管理。

a) 基于Python的 MSSQL 客户端工具安装

pip install mssql-cli

b) 基于 macOS 的 MSSQL 客户端工具 sqlcmd & bcp

brew tap microsoft/mssql-release https://github.com/Microsoft/homebrew-mssql-release
brew update
brew install --no-sandbox mssql-tools

语法如下：

mssql-cli -? 或sqlcmd -? 查看帮助
sqlcmd -S <实例的IP地址> -U SA -P '!Passw0rd'

(五)  Always On 可用性组配置

1)  在所有节点上开启 Always On Availability Group 功能并重启服务：

sudo /opt/mssql/bin/mssql-conf set hadr.hadrenabled 1
sudo systemctl restart mssql-server

2)  在所有节点上执行 SQL 语句开启 AlwaysOn_health 事件会话：

ALTER EVENT SESSION AlwaysOn_health ON SERVER WITH (STARTUP_STATE=ON);
GO

3)  在所有节点上创建数据库镜像终结点的用户:

CREATE LOGIN dbm_login WITH PASSWORD = '********';
CREATE USER dbm_user FOR LOGIN dbm_login;

4)  在主节点上创建证书:

Linux 上的 SQL Server 服务使用证书验证镜像终结点之间的通信:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '**<Master_Key_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 = '**<Private_Key_Password>**'
       );

5)  复制证书文件到所有备用节点，并导入证书:

将主节点上生成的 dbm_certificate.cer 和 dbm_certificate.pvk 文件复制到所有备用节点的相同位置，并修改属主及权限，然后执行导入证书:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '**<Master_Key_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 = '**<Private_Key_Password>**'
            );

6)  在所有节点上创建数据库镜像终结点:

CREATE ENDPOINT [Hadr_endpoint]
    AS TCP (LISTENER_PORT = 5022)
    FOR DATA_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];

7)  在主节点上创建可用性组:

CREATE AVAILABILITY GROUP [ag1]
    WITH (CLUSTER_TYPE = NONE)
    FOR REPLICA ON
        N'mynode01' WITH (
            ENDPOINT_URL = N'tcp://mynode01:5022',
            AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
            FAILOVER_MODE = MANUAL,
            SEEDING_MODE = AUTOMATIC,
                    SECONDARY_ROLE (ALLOW_CONNECTIONS = ALL)
            ),
        N'mynode02' WITH (
            ENDPOINT_URL = N'tcp://mynode02:5022',
            AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
            FAILOVER_MODE = MANUAL,
            SEEDING_MODE = AUTOMATIC,
                    SECONDARY_ROLE (ALLOW_CONNECTIONS = ALL)
            ),
        N'mynode03' WITH ( 
            ENDPOINT_URL = N'tcp://mynode03:5022', 
            AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
            FAILOVER_MODE = MANUAL,
            SEEDING_MODE = AUTOMATIC,
            SECONDARY_ROLE (ALLOW_CONNECTIONS = ALL)
            );
ALTER AVAILABILITY GROUP [ag1] GRANT CREATE ANY DATABASE;

8)  在所有备用节点上执行加入可用性组:

ALTER AVAILABILITY GROUP [ag1] JOIN WITH (CLUSTER_TYPE = NONE);
ALTER AVAILABILITY GROUP [ag1] GRANT CREATE ANY DATABASE;

9)  在主节点上创建数据库并添加到可用性组：

由于设置 SEEDING_MODE 参数为 AUTOMATIC，因此 db1 数据库将会在备库实例中自动创建，后续对于该库进行的任何操作也会自动复制到备库中。

CREATE DATABASE [db1];
ALTER DATABASE [db1] SET RECOVERY FULL;
BACKUP DATABASE [db1] 
   TO DISK = N'/var/opt/mssql/data/db1.bak';
ALTER AVAILABILITY GROUP [ag1] ADD DATABASE [db1];

10)  在所有备用节点验证 db1 是否已经成功同步:

SELECT * FROM sys.databases WHERE name = 'db1';
GO
SELECT DB_NAME(database_id) AS 'database', synchronization_state_desc FROM sys.dm_hadr_database_replica_states;

11)  读写与只读验证测试:

在主节点建表并插入数据；

在所有备用节点查询并删除数据，查看删除操作的出错信息；

(六) Always On 故障转移配置

1)  在所有节点上安装 Pacemaker 软件包：

sudo yum install pacemaker pcs fence-agents-all resource-agents

2)  在所有节点上为安装 Pacemaker 包时创建的 hacluster 用户设置相同密码:

sudo passwd hacluster

###将在第4步使用此密码

3)  在所有节点上启用并开启 pcsd 和 Pacemaker服务：

sudo systemctl enable pcsd

sudo systemctl start pcsd

sudo systemctl enable pacemaker

sudo systemctl enable corosync

4)  在主节点上创建群集：

sudo pcs cluster auth mynode01 mynode02 mynode3 -u hacluster -p ********

sudo pcs cluster setup --name mycluster01 mynode01 mynode02 mynode03

sudo pcs cluster start --all

备注：如果以前配置过群集，为了防止残余文件影响后期安装，可以先在所有节点执行如下命令删除已存在的群集：

sudo pcs cluster destroy

sudo systemctl enable pacemaker

5)  在所有节点上安装 SQL Server 资源代理，运行以下命令：

sudo yum install mssql-server-ha

6)  配置隔离并设置 start-failure-is-fatal:

sudo pcs property set stonith-enabled=false
sudo pcs property set start-failure-is-fatal=false

7)  在所有节点上创建 Pacemaker 所用的 SQL Server 登录用户：

USE [master]

GO

CREATE LOGIN [pacemakerLogin] with PASSWORD= N'ComplexP@$$w0rd!'

ALTER SERVER ROLE [sysadmin] ADD MEMBER [pacemakerLogin]

8)  在所有节点上，保存 SQL Server Login 的信息：

echo 'pacemakerLogin' >> ~/pacemaker-passwd

echo '<Your Password>' >> ~/pacemaker-passwd

sudo mv ~/pacemaker-passwd /var/opt/mssql/secrets/passwd

sudo chown root:root /var/opt/mssql/secrets/passwd

sudo chmod 400 /var/opt/mssql/secrets/passwd ### Only readable by root

9)  在主节点上创建 AG 的资源：

sudo pcs resource create ag1_cluster ocf:mssql:ag ag_name=ag1 master notify=true

10) 在主节点上创建虚拟 IP 资源：

sudo pcs resource create virtualip ocf:heartbeat:IPaddr2 ip=192.168.10.168 cidr_netmask=32 nic=eth0:1 op monitor interval=30s

11) 在主节点上配置群集资源的依赖关系和启动顺序：

sudo pcs constraint colocation add virtualip ag1_cluster-master INFINITY with-rsc-role=Master

sudo pcs constraint order promote ag1_cluster-master then start virtualip

12) 在任何节点上查看群集状态：

sudo pcs status

请留意下图标识高亮的部分：

  • mynode01 是主节点，可以提供读写服务;
  • mynode02､mynode03 是备用节点，可以提供只读服务;
  • 虚拟 IP 地址是 192.168.10.168/32 已经可用；

13)  在任何节点上手动故障转移主节点到 mynode02 并查看群集状态：

sudo pcs resource move ag1_cluster-master mynode02 --master
sudo pcs status

(七) 总结及参考资源

关于数据库级别监视和故障转移触发器，对于 CLUSTER_TYPE = EXTERNAL，故障转移触发器语义与 Windows 故障转移（WSFC）不同。当 AG 在 WSFC 中的 SQL Server 实例上，转换为数据库的 ONLINE 状态导致的 AG 运行状况报告错误。作为响应，群集管理器会触发故障转移操作。

在 Linux 上，SQL Server 实例无法与群集通信，对数据库运行状况进行外部监控，如果用户选择数据库级别故障转移监控和故障转移（通过在创建 AG 时设置 DB_FAILOVER = ON 选项），群集将在每次运行监控操作时检查数据库状态是否为 ONLINE，群集查询 sys.databases 中的状态，对于与 ONLINE 不同的任何状态，它将自动触发故障切换（如果满足自动故障切换条件）。 故障转移的实际时间取决于监控操作的频率以及在 sys.databaseses 中更新的数据库状态，自动故障转移至少需要一个同步副本。

虚拟 IP 地址 192.168.10.168/32 会随主节点的故障转移进行漂移，可以在所有节点上禁用源/目标检查，结合脚本将 192.168.10.168/32 作为 Destination，主节点的实例 ID 作为 Target 来动态更新路由表，并实现 VPC 内对虚拟 IP 的访问。

[参考资源]

  1. Amazon EC2 » Linux 实例用户指南 » Amazon EC2 实例 » 配置您的 Amazon Linux 实例» 更改 Linux 实例的主机名
  2. 快速入门参考部署指南» 架构
  3. Microsoft: Configure SQL Server Always On Availability Group for high availability on Linux

 

蒋华

AWS 合作伙伴解决方案架构师，已获得AWS解决方案架构师专业级与 DevOps Engineer 专业级认证，主要负责 AWS (中国)合作伙伴的技术支持工作，同时致力于 AWS 云服务在国内的应用及推广，并在关系型数据库服务、存储服务、分析服务、HA/DR 及云端应用迁移方面有着丰富的设计和实战经验。加入 AWS 之前，曾在 IBM (中国)工作12年，历任数据库售前工程师、UNIX 服务器资深售前工程师及解决方案架构师，熟悉传统企业 IT 架构、私有云及混合云部署，在数据库、数据仓库、高可用容灾及企业应用架构等方面有多年实践经验。