ubuntu20.04部署sqlserver2019高可用群集

作者: 这货不是王马勺 | 来源:发表于2024-01-25 16:52 被阅读0次

LVS-NAT+Keepalived高可用群集
② OpenStack高可用集群部署方案(train版)—Key
① OpenStack高可用集群部署方案(train版)—基础配
服务部署如何做到高可用？这份“三级跳”秘籍送给你
MongoDB：3.分片集群
LVS+KeepAlived高可用部署实战应用
EMQ高可用性+负载均衡架构
端口冲突引起的cannot bind socket [0.0.0
kubernetes搭建Harbor无坑及多中心Harbor仓库
LVS-DR+keepalived高可用群集

1.环境准备

准备三台ubuntu20.04，规划如下：
10.0.8.86 test1
10.0.8.87 test2
10.0.8.88 test3
并重命名主机名，以test1为例：

sudo hostnamectl set-hostname test1

或修改如下文件：

sudo vi /etc/hostname

重启服务器后生效。
注：每个 SQL Server 主机名称必须满足不超过 15 个字符且在网络中唯一。

三台均配置主机名ip映射：

vim /etc/hosts

添加：

10.0.8.86 test1
10.0.8.87 test2
10.0.8.88 test3

配置ssh互信（可选），以test1为例：

ssh-keygen
ssh-copy-id 10.0.8.87
ssh-copy-id 10.0.8.88

创建目录

mkdir -p /app/database/

然后将安装需要的包上传至此目录，所需软件包清单如下：

-rw-r--r-- 1 root root    485628 Jan 23 10:06 libodbc1_2.3.11-1_amd64.deb
-rw-r--r-- 1 root root    755938 Jan 23 10:06 msodbcsql18_18.3.2.1-1_amd64.deb
-rw-r--r-- 1 root root   1491238 Jan 23 10:06 mssql-server-ha_15.0.4345.5-2_amd64.deb
-rw-r--r-- 1 root root 245162374 Jan 23 10:06 mssql-server_15.0.4345.5-2_amd64.deb
-rw-r--r-- 1 root root    211366 Jan 23 10:06 mssql-tools18_18.2.1.1-1_amd64.deb
-rw-r--r-- 1 root root     99750 Jan 23 10:06 odbcinst1debian2_2.3.11-1_amd64.deb
-rw-r--r-- 1 root root     21274 Jan 23 10:06 odbcinst_2.3.11-1_amd64.deb
-rw-r--r-- 1 root root     42366 Jan 23 10:06 unixodbc-dev_2.3.11-1_amd64.deb
-rw-r--r-- 1 root root     51534 Jan 23 10:06 unixodbc_2.3.11-1_amd64.deb

下载地址见官网发行说明

https://learn.microsoft.com/zh-cn/sql/linux/sql-server-linux-release-notes-2019?view=sql-server-ver15#latest-versions-for-all-packages

2.安装SQLServer引擎和工具

三台均进行如下操作，
查看安装所需依赖：

dpkg -I mssql-server_versionnumber_amd64.deb

安装依赖：

sudo apt-get install libatomic1 libunwind8 libnuma1  libc6 adduser libc++1 gdb debconf hostname  openssl   python3  libgssapi-krb5-2  libsss-nss-idmap0  gawk  sed  libpam0g  libldap-2.4-2  libsasl2-2  libsasl2-modules-gssapi-mit  tzdata  lsof  procps

安装sqlserver引擎：

sudo dpkg -i mssql-server_15.0.4345.5-2_amd64.deb
sudo /opt/mssql/bin/mssql-conf setup

从 SQL Server 2017 CU4 开始，SQL Server 代理包含在 mssql-server 包中，默认情况下处于禁用状态，若要启用 SQL Server 代理，请执行以下步骤：

sudo /opt/mssql/bin/mssql-conf set sqlagent.enabled true
sudo systemctl restart mssql-server

安装工具：

sudo dpkg -i libodbc1_2.3.11-1_amd64.deb
sudo dpkg -i odbcinst1debian2_2.3.11-1_amd64.deb odbcinst_2.3.11-1_amd64.deb
sudo dpkg -i unixodbc_2.3.11-1_amd64.deb
sudo apt-get install libltdl3-dev
sudo dpkg -i unixodbc-dev_2.3.11-1_amd64.deb
sudo dpkg -i msodbcsql18_18.3.2.1-1_amd64.deb
sudo dpkg -i mssql-tools18_18.2.1.1-1_amd64.deb

下载地址：

https://packages.microsoft.com/ubuntu/20.04/prod/pool/main/m/

3.启用AlwaysOn功能

安装SQL Server HA

sudo dpkg -i mssql-server-ha_15.0.4345.5-2_amd64.deb
sudo systemctl restart mssql-server

修改位于 /var/opt/mssql 文件夹下的 mssql.conf 文件，

vim /var/opt/mssql/mssql.conf

添加以下行：

[hadr]
hadr.hadrenabled = 1

执行如下语句：

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

可选择性地启用扩展事件 (XE)，以便在对可用性组进行故障排除时帮助诊断根本原因。在每个 SQL Server 实例上运行以下命令：

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

有关此 XE 会话的详细信息，请参阅配置 Always On 可用性组扩展事件。

4.创建可用性组终结点和证书

Linux 上的 SQL Server 服务使用证书验证镜像终结点之间的通信。
(可用性组使用 TCP 终结点进行通信。在 Linux 下，仅当证书用于身份验证时，才支持 AG 的终结点。)
必须在所有将在同一 AG 中作为副本参与的其他实例上还原来自一个实例的证书。即使对于仅配置副本，也需要证书过程。

只能通过 Transact-SQL 完成创建终结点和还原证书。也可以使用非 SQL Server 生成的证书。还需要一个进程来管理和替换任何过期的证书。

注：如果计划使用 SQL Server Management Studio 向导创建 AG，则仍需要使用 Linux 上的 Transact-SQL 创建和还原证书。

创建证书Transact-SQL语法：

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>**'
        );

数据库镜像端点使用传输控制协议 (TCP) 在参与数据库镜像会话或承载可用性副本的服务器实例之间发送和接收消息。数据库镜像端点在唯一的 TCP 端口号上进行侦听。

为可用性组创建名为 Hadr_endpoint 的侦听终结点。它启动终结点，并向创建的证书授予连接权限。语法如下：

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;

具体请参考如下操作：

在test1上执行以下脚本以创建主密钥、证书和端点，并备份证书。终结点使用 TCP 端口 5022:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'asdfQWER!@34';
GO

CREATE CERTIFICATE test1_Cert
    WITH SUBJECT = 'test1 AG Certificate';
GO

BACKUP CERTIFICATE test1_Cert TO FILE = '/var/opt/mssql/data/test1_Cert.cer';
GO

CREATE ENDPOINT AGEP STATE = STARTED AS TCP (
    LISTENER_PORT = 5022,
    LISTENER_IP = ALL
)
FOR DATABASE_MIRRORING(AUTHENTICATION = CERTIFICATE test1_Cert, ROLE = ALL);
GO

对 test2 执行相同的操作：

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'asdfQWER!@34';
GO

CREATE CERTIFICATE test2_Cert
WITH SUBJECT = 'test2 AG Certificate';
GO

BACKUP CERTIFICATE test2_Cert
TO FILE = '/var/opt/mssql/data/test2_Cert.cer';
GO

CREATE ENDPOINT AGEP
STATE = STARTED
AS TCP (
    LISTENER_PORT = 5022,
    LISTENER_IP = ALL)
FOR DATABASE_MIRRORING (
    AUTHENTICATION = CERTIFICATE test2_Cert,
    ROLE = ALL);
GO

最后，对 test3 执行相同的操作：

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'asdfQWER!@34';
GO

CREATE CERTIFICATE test3_Cert
    WITH SUBJECT = 'test3 AG Certificate';
GO

BACKUP CERTIFICATE test3_Cert TO FILE = '/var/opt/mssql/data/test3_Cert.cer';
GO

CREATE ENDPOINT AGEP STATE = STARTED AS TCP (
    LISTENER_PORT = 5022,
    LISTENER_IP = ALL
)
FOR DATABASE_MIRRORING(AUTHENTICATION = CERTIFICATE test3_Cert, ROLE = ALL);
GO

使用 scp 或其他实用程序，将证书的备份复制到将成为 AG 一部分的每个节点。
对于本示例：
将 /var/opt/mssql/data/test1_Cert.cer 复制到 test2 和 test3。
将 /var/opt/mssql/data/test2_Cert.cer 复制到 test1 和 test3。
将 /var/opt/mssql/data/test3_Cert.cer 复制到 test1 和 test2。

将所有权和与复制的证书文件相关联的组更改为 mssql。

sudo chown mssql:mssql /var/opt/mssql/data/*.cer

之后将证书在其他节点进行还原：
节点1操作
在 test1 上创建与 test2 和 test3 关联的实例级登录名和用户。

CREATE LOGIN test2_Login WITH PASSWORD = 'asdfQWER!@34';
CREATE USER test2_User FOR LOGIN test2_Login;
GO

CREATE LOGIN test3_Login WITH PASSWORD = 'asdfQWER!@34';
CREATE USER test3_User FOR LOGIN test3_Login;
GO

在 test1 上还原 test2_Cert 和 test3_Cert。具有其他副本的证书是 AG 通信和安全的一个重要方面。

CREATE CERTIFICATE test2_Cert AUTHORIZATION test2_User
FROM FILE = '/var/opt/mssql/data/test2_Cert.cer';
GO

CREATE CERTIFICATE test3_Cert AUTHORIZATION test3_User
FROM FILE = '/var/opt/mssql/data/test3_Cert.cer';
GO

向与 test2 和 test3 关联的登录名授予连接到 test1 上的端点的权限。

GRANT CONNECT ON ENDPOINT::AGEP TO test2_Login;
GO

GRANT CONNECT ON ENDPOINT::AGEP TO test3_Login;
GO

然后再另外两节点重复上述动作，如下。

节点2操作
在 test2 上创建与 test1 和 test3 关联的实例级登录名和用户。

CREATE LOGIN test1_Login WITH PASSWORD = 'asdfQWER!@34';
CREATE USER test1_User FOR LOGIN test1_Login;
GO

CREATE LOGIN test3_Login WITH PASSWORD = 'asdfQWER!@34';
CREATE USER test3_User FOR LOGIN test3_Login;
GO

在 test2 上还原 test1_Cert 和 test3_Cert。

CREATE CERTIFICATE test1_Cert
AUTHORIZATION test1_User
FROM FILE = '/var/opt/mssql/data/test1_Cert.cer';
GO

CREATE CERTIFICATE test3_Cert
AUTHORIZATION test3_User
FROM FILE = '/var/opt/mssql/data/test3_Cert.cer';
GO

向与 test1 和 test3 关联的登录名授予连接到 test2 上的端点的权限。

GRANT CONNECT ON ENDPOINT::AGEP TO  test1_Login;
GO

GRANT CONNECT ON ENDPOINT::AGEP TO  test3_Login;
GO

节点3操作
在 test3 上创建与 test1 和 test2 关联的实例级登录名和用户。

CREATE LOGIN test1_Login WITH PASSWORD = 'asdfQWER!@34';
CREATE USER test1_User FOR LOGIN test1_Login;
GO

CREATE LOGIN test2_Login WITH PASSWORD = 'asdfQWER!@34';
CREATE USER test2_User FOR LOGIN test2_Login;
GO

在 test3 上还原 test1_Cert 和 test2_Cert。

CREATE CERTIFICATE test1_Cert
AUTHORIZATION test1_User
FROM FILE = '/var/opt/mssql/data/test1_Cert.cer';
GO

CREATE CERTIFICATE test2_Cert
AUTHORIZATION test2_User
FROM FILE = '/var/opt/mssql/data/test2_Cert.cer';
GO

向与 test1 和 test2 关联的登录名授予连接到 test3 上的端点的权限。

GRANT CONNECT ON ENDPOINT::AGEP TO test1_Login;
GO

GRANT CONNECT ON ENDPOINT::AGEP TO test2_Login;
GO

5.部署Pacemaker群集

安装高可用性加载项

sudo apt-get install -y  pacemaker pcs fence-agents resource-agents
sudo apt-get install -y  pacemaker-cli-utils crmsh  corosync python3-azure

Pacemaker使用hacluster用户。在 RHEL 和 Ubuntu 上安装 HA 加载项时，将自动创建用户。
在将用作Pacemaker群集节点的每台服务器上，为群集使用的用户创建密码。
参与 Pacemaker 群集的所有节点上使用的名称和密码必须相同。

sudo passwd hacluster

在将成为 Pacemaker 群集一部分的每个节点上，使用以下命令（RHEL 和 Ubuntu）启用并启动 pcsd 服务：

sudo systemctl enable pcsd
sudo systemctl start pcsd
sudo systemctl status pcsd

在 Pacemaker 群集的每个可能的节点上启用 Pacemaker 服务：

sudo systemctl start pacemaker
sudo systemctl enable pacemaker
systemctl status pacemaker

如果防火墙是开启的，则需要在所有节点上，打开防火墙端口：

sudo ufw allow 2224/tcp
sudo ufw allow 3121/tcp
sudo ufw allow 21064/tcp
sudo ufw allow 5405/udp

sudo ufw allow 1433/tcp # Replace with TDS endpoint
sudo ufw allow 5022/tcp # Replace with DATA_MIRRORING endpoint

sudo ufw reload

如果防火墙未开启则无需设置。
禁用防火墙命令如下：

sudo ufw disable

为Pacemaker创建SQLServer登录名pacemakerLogin：

USE [master]
GO
CREATE LOGIN [pacemakerLogin] with PASSWORD= N'asdfQWER!@34';

ALTER SERVER ROLE [sysadmin] ADD MEMBER [pacemakerLogin];

在所有SQL Server实例的服务器上，保存SQL Server登录名的凭据（仅root可读）：

echo 'pacemakerLogin' >> ~/pacemaker-passwd
echo 'asdfQWER!@34' >> ~/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

使用pcs创建
pcsd 是 pacemaker 的客户端命令执行程序，任何的功能都需要 pcsd 来启动和设置，pacemaker 是服务程序。当 pcsd, pacemaker 都配置好之后，使用命令 pcs 就可以用来和 pacemaker 交互了。所以 pcs 才是真正执行集群管理的程序软件，只要在其中一台节点上运行 pcs, 命令即将被送到各个节点去运行。

从安装的日志分析， corosync 会在安装 pacemaker 的时候一起安装，因此不需要特别去单独安装。pcs 是需要独立安装的。

接下来，创建并启动 Pacemaker 群集。对于此步骤，RHEL 和 Ubuntu 之间存在一个区别。虽然在这两个分发上，安装 pcs 会为 Pacemaker 群集配置默认配置文件，但在 RHEL 上，执行此命令会销毁任何现有配置并创建新群集。

从每个节点中删除默认的 Pacemaker 配置：

sudo pcs cluster destroy

为节点授权：

pcs host auth test1 test2 test3 -u hacluster

创建群集(仅在其中一个节点执行即可)：

pcs cluster setup cluster90 test1 test2 test3

启动并启用集群：

pcs cluster start --all
pcs cluster enable --all

使用Corosync创建
在创建群集之前，必须在主服务器上创建身份验证密钥，并将其复制到参与 AG 的其他服务器。
在主服务器上创建身份验证密钥：

sudo corosync-keygen

将生成的密钥复制到其他服务器：

sudo scp /etc/corosync/authkey root@test2:/etc/corosync/
sudo scp /etc/corosync/authkey root@test3:/etc/corosync/

若要创建群集，请在主服务器上编辑 /etc/corosync/corosync.conf 文件：

mv /etc/corosync/corosync.conf /etc/corosync/corosync.conf_bak
sudo vim /etc/corosync/corosync.conf

参考如下示例：

totem {
    version: 2
    cluster_name: cluster90
    transport: udpu
    crypto_cipher: none
    crypto_hash: none
}

logging {
    fileline: off
    to_stderr: yes
    to_logfile: yes
    logfile: /var/log/corosync/corosync.log
    to_syslog: yes
    debug: off
    logger_subsys {
        subsys: QUORUM
        debug: off
    }
}

quorum {
    provider: corosync_votequorum
}

nodelist {
    node {
        name: test1
        nodeid: 1
        ring0_addr: 10.0.8.86
    }
    node {
        name: test2
        nodeid: 2
        ring0_addr: 10.0.8.87
    }
        node {
        name: test3
        nodeid: 3
        ring0_addr: 10.0.8.88
    }
}

替换其他节点上的 corosync.conf 文件：

sudo scp /etc/corosync/corosync.conf root@test2:/etc/corosync/
sudo scp /etc/corosync/corosync.conf root@test3:/etc/corosync/

重启 pacemaker 和 corosync 服务：

sudo systemctl restart pacemaker corosync

确认群集的状态并验证配置：

sudo crm status

可看到当前并没有AG。

6.创建 AG

然后在主节点执行如下语句来创建：

CREATE AVAILABILITY GROUP [ag90]
      WITH (DB_FAILOVER = ON, CLUSTER_TYPE = EXTERNAL)
      FOR REPLICA ON
         N'test1'
               WITH (
            ENDPOINT_URL = N'tcp://test1:5022',
            AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
            FAILOVER_MODE = EXTERNAL,
            SEEDING_MODE = AUTOMATIC
            ),
         N'test2'
         WITH (
            ENDPOINT_URL = N'tcp://test2:5022',
            AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
            FAILOVER_MODE = EXTERNAL,
            SEEDING_MODE = AUTOMATIC
            ),
         N'test3'
         WITH(
            ENDPOINT_URL = N'tcp://test3:5022',
            AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
            FAILOVER_MODE = EXTERNAL,
            SEEDING_MODE = AUTOMATIC
            );

ALTER AVAILABILITY GROUP [ag90] GRANT CREATE ANY DATABASE;

将次要副本联接到 AG：
Pacemaker 用户需要具有对所有副本上可用性组的 ALTER、CONTROL 和 VIEW DEFINITION 权限。若要授予权限，在创建可用性组且将主副本和每个次要副本添加到可用性组之后，请在这些副本上立即运行以下 Transact-SQL：

GRANT ALTER, CONTROL, VIEW DEFINITION ON AVAILABILITY GROUP::ag90 TO pacemakerLogin;
GRANT VIEW SERVER STATE TO pacemakerLogin;

我们先在主节点完成授权。

然后在次要副本的每个 SQL Server 实例上运行以下 Transact-SQL，以加入 AG：

ALTER AVAILABILITY GROUP [ag90] JOIN WITH (CLUSTER_TYPE = EXTERNAL);

ALTER AVAILABILITY GROUP [ag90] GRANT CREATE ANY DATABASE;

加入后在两个辅助节点也执行上述的授权SQL。
此后可通过SSMS查看，并设置可读辅助副本。

7.将数据库添加到可用性组

确保添加到可用性组的数据库处于完整恢复模式，并具有有效的日志备份。
如果是测试数据库或新建的数据库，请执行数据库备份。

在主节点创建一个名为test的数据库并做一次全备：

CREATE DATABASE [test];
ALTER DATABASE [test] SET RECOVERY FULL;
BACKUP DATABASE [test]
   TO DISK = N'/var/opt/mssql/data/test.bak';

在主 SQL Server 副本上，运行以下 Transact-SQL 加入AG：

ALTER AVAILABILITY GROUP [ag90] ADD DATABASE [test];

注：请勿使用SSMS管理AG，会遇到许多稀奇古怪的问题。如果出现问题，如无法同步数据到辅助节点，可直接删除AG，并新建一个名称不同的AG，并重新开始上述步骤。

验证同步，主副本上创建测试表：

CREATE TABLE [dbo].[tb_log](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [LogTime] [datetime] NULL,
    [operator] [varchar](255) NULL,
    [action] [varchar](255) NULL
) ON [PRIMARY]
GO

然后将AG属性中“可读副本”选项开启，在辅助节点上查看数据已同步。

8.在Pacemaker群集中创建可用性组资源

在一台中执行：

pcs property show --all |grep stonith-enabled
pcs property set stonith-enabled=false

为可用性组创建一个名为ag90的主要/副本类型的资源 ocf:mssql:ag90。

~$ sudo crm

configure

primitive ag90_cluster \
ocf:mssql:ag \
params ag_name="ag90" \
meta failure-timeout=60s \
op start timeout=60s \
op stop timeout=60s \
op promote timeout=60s \
op demote timeout=10s \
op monitor timeout=60s interval=10s \
op monitor timeout=60s on-fail=demote interval=11s role="Master" \
op monitor timeout=60s interval=12s role="Slave" \
op notify timeout=60s
ms ms-ag90 ag90_cluster \
meta master-max="1" master-node-max="1" clone-max="3" \
clone-node-max="1" notify="true"

commit

创建虚拟 IP 资源(在一个节点上运行)：

sudo crm 

configure 

primitive virtualip \
ocf:heartbeat:IPaddr2 \
params ip=10.0.8.90

使用SSMS连接vip可以正常连接。

参考：

https://learn.microsoft.com/zh-cn/sql/linux/sql-server-linux-availability-group-cluster-pacemaker?tabs=ubuntu&view=sql-server-ver16#create-a-sql-server-login-for-pacemaker

LVS-NAT+Keepalived高可用群集
构建LVS-NAT+Keepalived高可用群集 -------client-------主LVS-------...
② OpenStack高可用集群部署方案(train版)—Key
① OpenStack高可用集群部署方案(train版)—基础配置② OpenStack高可用集群部署方案(tra...
① OpenStack高可用集群部署方案(train版)—基础配
① OpenStack高可用集群部署方案(train版)—基础配置② OpenStack高可用集群部署方案(tra...
服务部署如何做到高可用？这份“三级跳”秘籍送给你
高可用部署要求图1 高可用部署 (*注：随着服务满足高可用要求的增多，服务的高可用能力就越强) 一致性这里的一...
MongoDB：3.分片集群
环境 MongoDB分片群集包含以下组件：生产环境配置在生产群集中，确保数据冗余并确保系统具有高可用性。对于生...
LVS+KeepAlived高可用部署实战应用
LVS+KeepAlived高可用部署实战 1. 构建高可用集群 1.1 什么是高可用集群 1.2 高可用衡量标准...
EMQ高可用性+负载均衡架构
1. emq高可用+负载均衡部署 1. emq高可用+负载均衡部署1.1. 整体架构规划1.2. EMQ集群部署1...
端口冲突引起的cannot bind socket [0.0.0
在配置PXC+keepalived+haproxy高可用群集时，由于资源所限，将keepalived与haprox...
kubernetes搭建Harbor无坑及多中心Harbor仓库
harbor高可用可查看《kubernetes部署高可用Harbor》一、helm搭建harbor 1.安装he...
LVS-DR+keepalived高可用群集
构建LVS-DR+Keepalived高可用群集古人云—— 无规矩不成方圆我们不管做什么实验之前都是要有一定的...