Docker部署SQL Server集群

Posted on Edited on In Views: Symbols count in article: 5.4k Reading time ≈ 5 mins.
使用Docker快速部署SQL Server Always On集群测试环境

SQL Server在2016年开始支持Linux。随着2017和2019版本的发布，它开始支持Linux和容器平台上的HA/DR、Kubernetes和大数据集群解决方案。

在本文中，我们将在3个节点的Docker容器上安装SQL Server 2019，并创建AlwaysOn可用性组。

我们的目标是使用单个配置文件快速准备好环境。

因此，开发人员或测试团队可以快速执行诸如兼容性、连通性、代码功能等测试。

重要提示：不建议在生产环境中执行操作。

安装Docker

快速安装Docker

架构

主机名 IP 端口 角色
sqlNode1 宿主机IP 1501:1433 主
sqlNode2 宿主机IP 1502:1433 副
sqlNode3 宿主机IP 1503:1433 副

部署

环境准备完毕后，开始正式的配置安装。

创建容器

通过docker-compose文件来创建、配置3个容器，具体内容如下：

1
vi docker-compose.yml

插入以下内容

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
version: '3'
 
services:
  db1:
    container_name: sqlNode1
    image: mcr.microsoft.com/mssql/server:2019-latest
    hostname: sqlNode1
    domainname: lab.local
    environment:
      MSSQL_SA_PASSWORD: "bb123456??"
      ACCEPT_EULA: "Y"
      MSSQL_ENABLE_HADR: 1
      MSSQL_AGENT_ENABLED: "true"
    ports:
    - "1501:1433"
    extra_hosts:
      sqlNode2.labl.local: "172.16.238.22"
      sqlNode3.labl.local: "172.16.238.23"
    networks:
        internal:
                ipv4_address: 172.16.238.21
 
  db2:
    container_name: sqlNode2
    image: mcr.microsoft.com/mssql/server:2019-latest
    hostname: sqlNode2
    domainname: lab.local
    environment:
      MSSQL_SA_PASSWORD: "bb123456??"
      ACCEPT_EULA: "Y"
      MSSQL_ENABLE_HADR: 1
      MSSQL_AGENT_ENABLED: "true"
    ports:
    - "1502:1433"
    extra_hosts:
      sqlNode1.lab.local: "172.16.238.21"
      sqlNode3.lab.local: "172.16.238.23"
    networks:
        internal:
                ipv4_address: 172.16.238.22
 
  db3:
    container_name: sqlNode3
    image: mcr.microsoft.com/mssql/server:2019-latest
    hostname: sqlNode3
    domainname: lab.local
    environment:
      MSSQL_SA_PASSWORD: "bb123456??"
      ACCEPT_EULA: "Y"
      MSSQL_ENABLE_HADR: 1
      MSSQL_AGENT_ENABLED: "true"
    ports:
    - "1503:1433"
    extra_hosts:
      sqlNode1.lab.local: "172.16.238.21"
      sqlNode2.lab.local: "172.16.238.22"
    networks:
        internal:
                ipv4_address: 172.16.238.23
 
networks:
    internal:
      ipam:
            driver: default
            config:
                - subnet: 172.16.238.0/24

启动容器

然后通过docker-compose up -d命令启动三个容器，其中-d表示在后台运行。

1
docker compose up -d

image-20221020160443178

查看容器状态

1
2
3
4
5
[root@acme-1 ~]# docker compose ps
NAME                COMMAND                  SERVICE             STATUS              PORTS
sqlNode1            "/opt/mssql/bin/perm…"   db1                 running             0.0.0.0:1501->1433/tcp, :::1501->1433/tcp
sqlNode2            "/opt/mssql/bin/perm…"   db2                 running             0.0.0.0:1502->1433/tcp, :::1502->1433/tcp
sqlNode3            "/opt/mssql/bin/perm…"   db3                 running             0.0.0.0:1503->1433/tcp, :::1503->1433/tcp

配置

通过宿主机的外网IP+端口连接相应的数据库，如下：

image-20221020161204446

image-20221020161232090

配置互信

主库-生成密钥

连接到主库，也就是节点1，端口是1501

将证书和私钥提取到/tmp/dbm_certificate.cer和/tmp/dbm_certificate.pvk文件中

将文件复制到其他节点，并根据以下文件创建主密钥和证书：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
USE master
GO
 
CREATE LOGIN dbm_login WITH PASSWORD = 'bb123456??';
CREATE USER dbm_user FOR LOGIN dbm_login;
GO
 
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'bb123456??';
go
CREATE CERTIFICATE dbm_certificate WITH SUBJECT = 'dbm';
BACKUP CERTIFICATE dbm_certificate
TO FILE = '/tmp/dbm_certificate.cer'
WITH PRIVATE KEY (
        FILE = '/tmp/dbm_certificate.pvk',
        ENCRYPTION BY PASSWORD = 'bb123456??'
    );
GO

将文件拷贝到其他两个节点：

在服务器中执行

1
2
3
4
5
6
docker cp sqlNode1:/tmp/dbm_certificate.cer .
docker cp sqlNode1:/tmp/dbm_certificate.pvk .
docker cp dbm_certificate.cer sqlNode2:/tmp/
docker cp dbm_certificate.pvk sqlNode2:/tmp/
docker cp dbm_certificate.cer sqlNode3:/tmp/
docker cp dbm_certificate.pvk sqlNode3:/tmp/

从库-导入密钥

1
2
3
4
5
6
7
8
9
10
11
12
13
14
CREATE LOGIN dbm_login WITH PASSWORD = 'bb123456??';
CREATE USER dbm_user FOR LOGIN dbm_login;
GO
 
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'bb123456??';
GO
CREATE CERTIFICATE dbm_certificate   
    AUTHORIZATION dbm_user
    FROM FILE = '/tmp/dbm_certificate.cer'
    WITH PRIVATE KEY (
    FILE = '/tmp/dbm_certificate.pvk',
    DECRYPTION BY PASSWORD = 'bb123456??'
);
GO

监听和重启

最后在所有节点执行以下命令

1
2
3
4
5
6
7
8
9
CREATE ENDPOINT [Hadr_endpoint]
    AS TCP (LISTENER_IP = (0.0.0.0), 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];

启用开机自启动ALWAYON，在所有节点执行以下命令

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

创建高可用组

行以下脚本在主节点中创建一个可用性组。

请注意，选择CLUSTER_TYPE = NONE选项是因为它是在没有诸如Pacemaker或Windows Server故障转移群集之类的群集管理平台的情况下安装的。

如果要在Linux上安装AlwaysOn AG，则应为Pacemaker选择CLUSTER_TYPE = EXTERNAL：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
CREATE AVAILABILITY GROUP [AG1]
        WITH (CLUSTER_TYPE = NONE)
        FOR REPLICA ON
        N'sqlNode1'
            WITH (
            ENDPOINT_URL = N'tcp://sqlNode1:5022',
            AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
                SEEDING_MODE = AUTOMATIC,
                FAILOVER_MODE = MANUAL,
            SECONDARY_ROLE (ALLOW_CONNECTIONS = ALL)
                ),
        N'sqlNode2'
            WITH (
            ENDPOINT_URL = N'tcp://sqlNode2:5022',
            AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
                SEEDING_MODE = AUTOMATIC,
                FAILOVER_MODE = MANUAL,
            SECONDARY_ROLE (ALLOW_CONNECTIONS = ALL)
                ),
        N'sqlNode3'
            WITH (
            ENDPOINT_URL = N'tcp://sqlNode3:5022',
            AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
                SEEDING_MODE = AUTOMATIC,
                FAILOVER_MODE = MANUAL,
            SECONDARY_ROLE (ALLOW_CONNECTIONS = ALL)
                );
GO

在从库中执行以下命令，将从库加入到AG组中

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

至此在Docker容器中安装SQL Server Alwayson集群已经完成了

故障转移

注意：当指定CLUSTER_TYPE = NONE创建可用组时无法自动故障转移，在执行故障转移时需执行以下命令，并检查要执行故障转移的辅助节点的最后提交时间

1
ALTER AVAILABILITY GROUP [ag1] FORCE_FAILOVER_ALLOW_DATA_LOSS

测试

在主库上创建一个数据库，并加入到可用组AG中。

1
2
3
4
5
6
7
8
CREATE DATABASE agtestdb;
GO
ALTER DATABASE agtestdb SET RECOVERY FULL;
GO
BACKUP DATABASE agtestdb TO DISK = '/var/opt/mssql/data/agtestdb.bak';
GO
ALTER AVAILABILITY GROUP [ag1] ADD DATABASE [agtestdb];
GO

参考文档：

为 Linux 上的 SQL Server 配置环境变量 - SQL Server | Microsoft Learn

Related Posts