SQL Server Always On Availability Group 配置

步骤:

  1. 配置三台 Linux 集群节点
  2. 创建 Availability Group
  3. 配置 Cluster Resource Manager, 比如 Pacemaker
  4. 在集群中添加 Availability Groups

详细解说

1. 配置三台 Linux 集群节点

  • 在集群中的三台服务器之间可以互相通信
  • 安装 SQL Server

在 Linux 上,往集群管理器中添加集群资源时,一定是先建立集群资源,接着将新建的资源加入到集群中去。

所以建立 SQL Server Always On Availability Groups 也一样,先建立 AG, 完了之后添加到集群管理器( Cluster Manager )中去。

三台节点之间可以相互通信,该如何了解?

使用 ssh 可以相互无障碍地登录

  1. 设置每台计算机的机器名
  2. 加入同一个域
  3. 配置 RSA 使得无密码 ssh 登录

1 设置每台计算机的机器名

vi /etc/hostname

分别设置为 centos00, centos01, centos02.

centos00 为主库所在服务器节点

2 加入同一个域

作用就是了互相识别,假如 node1 , node2, node3 是我们新建的三台集群服务器,互为 Availability Groups 成员,且 node1 是主库,node2, node3 为从库,三台机器之间互相识别的方法有两种,一是加入同一个域; 二是在各自的 /etc/hosts 文件中,添加机器名和 IP 地址的映射:

127.0.0.1 localhost
::1 localhost

192.168.1.10 centos00
192.168.1.11 centos01
192.168.1.12 centos02

把以上的配置脚本放入各自服务器的 /etc/hosts 中去,不需要配置域即可互相识别。

安装 SQL Server 已有介绍,不展开了

2. 启动 AlwaysOn Availability Group 特性

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

可以检查服务器是否启用了 hadr :

select serverproperty('HadrManagerStatus')

开启 AlwaysOn_health 扩展事件(Extended Events),用来检测 Always On Availability Group 的故障所在:

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

在每一台节点实例上都开启 AlwaysOn_health 扩展事件。

3. 新建证书 (certificate)

证书(Certificate)的作用,是为了让 SQL Server Mirroring Endpoints 之间可以相互通信。

理念和两台 Linux 服务器之间无密通信一样,一个公钥,一个私钥。公钥用来核实私钥的有效性,分发到各个服务器上,作为访问远程服务的凭证。

新建 master key :

create master key encryption by password='MasterKey123.'

create master key 是 T-SQL 命令,不能直接在 shell 里面运行!

新建证书:

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='PrivateKey123.')

现在有了公钥,私钥和证书,则需要保证所有的集群节点都有私钥和证书,如果没有则复制私钥和证书到相同目录下,以便之后的安装证书。

所以配置 AG 时,复制 dbm_certificate.cer, dbm_certificate.pvk 到其他两台节点相同目录下。

cd /var/opt/mssql/data
scp dbm_certificate.* root@centos01: /var/opt/mssql/data/
scp dbm_certificate.* root@centos02: /var/opt/mssql/data/

把这两个文件的权限赋给运行 ms sql server 服务的账户 mssql:

chown mssql:mssql dbm_certificate.*

4. 还原从库的证书

从库已经有了主库私钥的副本,这些副本可以还原出证书

create master key encryption by password='MasterKey123.'

create certificate dbm_certificate
    from file='/var/opt/mssql/data/dbm_certificate.cer'
    with private key(
        file='/var/opt/mssql/data/dbm_certificate.pvk',
        decryption by password='PrivateKey123.')

在所有的从库都执行上述的脚本,来创建证书。

其中会遇到一点小麻烦:

Msg 15208, Level 16, State 6, Server centos02, Line 1
The certificate, asymmetric keyor private key file is not valid or does not exist; or you do not have permissions for it.

mssql 是用来运行 sql server on linux 的账户,因此他需要被赋予可以访问证书和私钥文件的权限。

chown mssql:mssql dbm_certificate.*

证书是从主库上“移栽”过去的,那么从库是否也需要搭建自己的证书,“移栽”给主库使用呢?

5. 新建 mirroring endpoints

SQL Server AG 节点之间使用 TCP 通信,通过指定的端口传送消息。

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

打开 防火墙对 endpoint 对应的端口的支持。

    firewall-cmd --zone=public --add-port=5022/tcp --permanent
    firewall-cmd --reload

6. 新建 AG (Availability Groups)

两种新建 AG 的方法:

  • 3 份同步副本 (Three synchronous replicas)
  • 2 份同步副本 + 1份配置副本(Two synchronous replicas plus a configuration replica)

这两种方法都可以保护数据和实现高可用性,但 3份同步副本的方法更能在主库失效的情况下,做出自动切换的动作,等待老主库回线之后,继续事务操作。

如果仅仅是实现保护数据的目的,那么直接采用 2 副本即可,还省去了集群管理器的配置。

搭建三副本同步的脚本

create availability group [crmag]
    with (db_failover=on,cluster_type=external)
    for replica on 
        N'centos00'
        with (
            endpoint_url = N'tcp://centos00:5022',
            availability_mode=synchronous_commit,
            failover_mode=external,
            seeding_mode=automatic
            ),
         N'centos01'
        with (
            endpoint_url = N'tcp://centos01:5022',
            availability_mode=synchronous_commit,
            failover_mode=external,
            seeding_mode=automatic
            ),
         N'centos02'
        with (
            endpoint_url = N'tcp://centos02:5022',
            availability_mode=synchronous_commit,
            failover_mode=external,
            seeding_mode=automatic
            );

    alter availability group [crmag]
        grant create any database ;

我们在这里指定了 external 的集群管理方式,那么就需要在 Linux 上安装独立的集群管理软件,通常用 Pacemaker. 但理论上也应该有其他的集群管理软件,比如 Mesos, Linux Cluster Manager(LCM) 等。

这是配置 Availability Group 中比较重要的一大步,有必要在这一步做一些测试来提早预知,是不是配置得有问题。

endpoint_url 被配置成了 namepipeline, 我觉得有必要修改一下:

create availability group [crmag]
    with (db_failover=on,cluster_type=external)
    for replica on 
        N'192.168.1.10'
        with (
            endpoint_url = N'tcp://192.168.1.10:5022',
            availability_mode=synchronous_commit,
            failover_mode=external,
            seeding_mode=automatic
            ),
         N'192.168.1.11'
        with (
            endpoint_url = N'tcp://192.168.1.11:5022',
            availability_mode=synchronous_commit,
            failover_mode=external,
            seeding_mode=automatic
            ),
         N'192.168.1.12'
        with (
            endpoint_url = N'tcp://192.168.1.12:5022',
            availability_mode=synchronous_commit,
            failover_mode=external,
            seeding_mode=automatic
            );

    alter availability group [crmag]
        grant create any database ;

理论上所有 availability_mode 标记是 synchronous_commit 的节点,都需要同步完成之后,主库的事务才能被正确提交。但有一个参数可以控制从库副本同步的最小数量 - Required_Synchronized_Secondaries_To_Commit. 上面的配置看上去是要 2 个从库都同步之后,事务才会在主库提交,但如果配置了 required_synchronized_secondaries_to_commit 为1, 则只需要一台从库同步即可。

7. 连接从库与添加 AG 数据库

连接从库

需要将集群中的从库,添加到 AG 中来,在每个从库上执行下面的命令:

ALTER AVAILABILITY GROUP [crmag]
    Join WITH(cluster_type=external);

ALTER AVAILABILITY GROUP [crmag]
    Grant Create Any Database ;

添加 AG 数据库:

选择性的添加我们要同步的数据库,使其实现三副本同步:

create database [crm];
alter database [crm] set recovery full ;


backup database [crm]
    to disk = N'/var/opt/mssql/data/crm.bak';



alter availability group [crmag]
    add database [crm]

验证同步情况:

select db_name(database_id)  as 'database'
    ,   synchronization_state_desc
from sys.dm_hadr_database_replica_states ;

目前为止实现的功能是数据保护,即主库失效的情况下,数据都备份在两台从库上。但并没有实现自动切换功能,即主库失效了,其他两台库也就不能访问了。所以下一篇讲解如何使用 pacemaker 实现自动切换失效主库到从库,达到7*24 小时无故障在线。

Linux 下搭建 SQL Server 高可用服务之一-LMLPHP

08-30 01:43