亚马逊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 架构、私有云及混合云部署,在数据库、数据仓库、高可用容灾及企业应用架构等方面有多年实践经验。