Docker 容器部署 SQL Server AlwaysOn AG

最新推荐文章于 2025-04-25 14:45:20 发布

原创最新推荐文章于 2025-04-25 14:45:20 发布 · 2.5k 阅读

4 ·

CC 4.0 BY-SA版权

文章标签：

#sql #数据库 #mssql

SQLServer 同时被 3 个专栏收录

269 篇文章

订阅专栏

SQLServer 高可用性

31 篇文章

订阅专栏

SQLServer AlwaysOn

20 篇文章

订阅专栏

本文详细介绍了如何在Linux Ubuntu虚拟机上利用Docker镜像构建并配置SQL Server 2019的AlwaysOn Availability Group（AG），包括安装Docker、创建镜像、启动容器、配置实例、证书管理及AG设置等步骤。

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

今天我们就直接测试，使用容器构建 SQL Server AlwaysOn AG，这对于学习 AlwaysOn AG 的同学来说，是不错的选择。容器可以使用更少的资源，就可配置完成。文章比较详细，一步步跟踪操作即可实现！

现在，我们创建了一台Ubuntu虚拟机，接下来，我们继续安装 docker ，并通过模板（docker镜像）创建3个 SQL Server 实例容器，然后在这3个实例中配置 AlwaysOn AG。不管在 Linux 或是容器环境，没有配置系统集群的情况下，也是可以配置 SQL Server AlwaysOn AG 的，只是 AlwaysOn AG 并没有实现自动故障转移，不过我们可以进行手动故障转移。

首先安装 Docker & Docker-Compose

#如果有之前版本的docker安装，建议删除
apt-get remove docker docker-engine docker.io containerd runc
apt-get update
apt-get install -y apt-transport-https ca-certificates curl gnupg-agent software-properties-common

#添加Docker的官方GPG密钥
curl -fsSL https://download.docker.com/linux/ubuntu/gpg | sudo apt-key add -

#添加 Docker 存储库
add-apt-repository "deb [arch=amd64] https://download.docker.com/linux/ubuntu $(lsb_release -cs) stable"

#安装Docker引擎
apt-get update
apt-get install -y docker-ce docker-ce-cli containerd.io
apt-get install -y docker-compose

配置SQLServer实例镜像

# vi dockerfile
FROM ubuntu:20.04

RUN DEBIAN_FRONTEND="noninteractive"
RUN apt-get update && apt-get install -y tzdata
ENV TZ=Asia/Shanghai
RUN ln -snf /usr/share/zoneinfo/$TZ /etc/localtime && echo $TZ > /etc/timezone

RUN apt-get update
RUN apt install -y sudo wget curl gnupg gnupg1 gnupg
RUN apt install -y software-properties-common systemd vim
RUN apt install -y telnetd iputils-ping

RUN wget -qO- https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add -
RUN add-apt-repository "$(wget -qO- https://packages.microsoft.com/config/ubuntu/18.04/mssql-server-2019.list)"

RUN apt-get update
RUN apt-get install -y libldap2-dev checkinstall
RUN apt-get install -y mssql-server

RUN /opt/mssql/bin/mssql-conf set hadr.hadrenabled  1
RUN /opt/mssql/bin/mssql-conf set sqlagent.enabled true
RUN /opt/mssql/bin/mssql-conf set memory.memorylimitmb 2048

EXPOSE 1433
EXPOSE 5022

ENTRYPOINT /opt/mssql/bin/sqlservr

构建SQLServer实例镜像

配名称为 dockerfile，build 默认现在当前路径文件，当然你也可以指定。

# 先拉取系统镜像
docker pull ubuntu:20.04

#构建SQLServer镜像
docker build -t mssql2019:hadr .

#查看
docker images
------------------------------------------------------------------------------------
REPOSITORY          TAG                 IMAGE ID            CREATED             SIZE
------------------------------------------------------------------------------------
mssql2019           hadr                a9134337ba77        2 hours ago         1.8GB
ubuntu              20.04               ba6acccedd29        6 weeks ago         72.8MB
------------------------------------------------------------------------------------

docker compose 启动3个 SQL Server 容器

# vi docker-compose.yml
version: '3'

services:
  db1:
    container_name: sqlNode1
    image: mssql2019:hadr
    hostname: sqlNode1
    domainname: lab.local
    environment:
      SA_PASSWORD: "Str0ngPa@w0rd"
      ACCEPT_EULA: "Y"
    ports:
    - "1501:1433"
    extra_hosts:
      sqlNode2.lab.local: "10.10.10.102"
      sqlNode3.lab.local: "10.10.10.103"
    networks:
      internal:
        ipv4_address: 10.10.10.101

  db2:
    container_name: sqlNode2
    image: mssql2019:hadr
    hostname: sqlNode2
    domainname: lab.local
    environment:
      SA_PASSWORD: "Str0ngPa@w0rd"
      ACCEPT_EULA: "Y"
    ports:
    - "1502:1433"
    extra_hosts:
      sqlNode1.lab.local: "10.10.10.101"
      sqlNode3.lab.local: "10.10.10.103"
    networks:
      internal:
        ipv4_address: 10.10.10.102

  db3:
    container_name: sqlNode3
    image: mssql2019:hadr
    hostname: sqlNode3
    domainname: lab.local
    environment:
      SA_PASSWORD: "Str0ngPa@w0rd"
      ACCEPT_EULA: "Y"
    ports:
    - "1503:1433"
    extra_hosts:
      sqlNode1.lab.local: "10.10.10.101"
      sqlNode2.lab.local: "10.10.10.102"
    networks:
      internal:
        ipv4_address: 10.10.10.103

networks:
    internal:
      driver: bridge
      ipam:
        driver: default
        config:
          - subnet: 10.10.10.0/24

启动时，Docker 将创建一个新的 bridge 网络，可通过 ifconfig 或 docker network ls 查看。当然，你也可以使用一个已存在的虚拟网络。接下来，才是启动！

#Docker Compose 启动堆栈，-d 后台运行
docker-compose up -d

docker ps
docker-compose ps
------------------------------------------------------------------------------------
  Name                Command               State                Ports
------------------------------------------------------------------------------------
sqlNode1   /bin/sh -c /opt/mssql/bin/ ...   Up      0.0.0.0:1501->1433/tcp, 5022/tcp
sqlNode2   /bin/sh -c /opt/mssql/bin/ ...   Up      0.0.0.0:1502->1433/tcp, 5022/tcp
sqlNode3   /bin/sh -c /opt/mssql/bin/ ...   Up      0.0.0.0:1503->1433/tcp, 5022/tcp
------------------------------------------------------------------------------------

安装客户端工具 sqlcmd

实例已经创建好SQLServer实例了，怎么去连接呢？我们需要一个客户端工具——sqlcmd。

curl https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add -
curl https://packages.microsoft.com/config/ubuntu/21.04/prod.list | sudo tee /etc/apt/sources.list.d/msprod.list
apt-get update
apt-get install -y mssql-tools unixodbc-dev
echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bash_profile
echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc
source ~/.bashrc

此时我们可以通过该工具进行连接操作实例了！

sqlcmd -S "127.0.0.1,1501" -d "master" -U SA -P "Str0ngPa@w0rd" -Q "print @@servername"

当然，你也可以通过Windows客户端工具 SSMS 进行连接操作。IP为虚拟机Ubuntu的IP地址，端口为容器对外映射的端口。

SQL Server AlwaysOn AG 配置

3个实例创建登录名、主密钥和证书

#创建登录名、主密钥和证书
for port in {1501..1503}
do
sqlcmd -S "127.0.0.1,$port" -d "master" -U SA -P "Str0ngPa@w0rd" -Q "
CREATE LOGIN dbm_login WITH PASSWORD = 'MyStr0ngPa@w0rd';
GO
CREATE USER dbm_user FOR LOGIN dbm_login;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'MyStr0ngPa@w0rd';
GO
CREATE CERTIFICATE dbm_certificate_$port WITH SUBJECT = 'dbm';
GO
CREATE ENDPOINT [Hadr_endpoint]
    AS TCP (LISTENER_IP = (0.0.0.0), LISTENER_PORT = 5022)
    FOR DATA_MIRRORING (
        ROLE = ALL,
        AUTHENTICATION = CERTIFICATE dbm_certificate_$port,
        ENCRYPTION = REQUIRED ALGORITHM AES
    );
GO
ALTER ENDPOINT [Hadr_endpoint] STATE = STARTED;
GO
GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO [dbm_login];
GO
BACKUP CERTIFICATE dbm_certificate_$port
TO FILE = '/tmp/dbm_certificate_$port.cer'
WITH PRIVATE KEY (
        FILE = '/tmp/dbm_certificate_$port.pvk',
        ENCRYPTION BY PASSWORD = 'MyStr0ngPa@w0rd'
    );
GO"
done

将带有 cer 和 pvk 扩展名的文件从容器复制到其他容器节点

docker cp sqlNode1:/tmp/dbm_certificate_1501.cer .
docker cp sqlNode1:/tmp/dbm_certificate_1501.pvk .
docker cp sqlNode2:/tmp/dbm_certificate_1502.cer .
docker cp sqlNode2:/tmp/dbm_certificate_1502.pvk .
docker cp sqlNode3:/tmp/dbm_certificate_1503.cer .
docker cp sqlNode3:/tmp/dbm_certificate_1503.pvk .

docker cp dbm_certificate_1501.cer sqlNode2:/tmp/
docker cp dbm_certificate_1501.pvk sqlNode2:/tmp/
docker cp dbm_certificate_1501.cer sqlNode3:/tmp/
docker cp dbm_certificate_1501.pvk sqlNode3:/tmp/
docker cp dbm_certificate_1502.cer sqlNode1:/tmp/
docker cp dbm_certificate_1502.pvk sqlNode1:/tmp/
docker cp dbm_certificate_1502.cer sqlNode3:/tmp/
docker cp dbm_certificate_1502.pvk sqlNode3:/tmp/
docker cp dbm_certificate_1503.cer sqlNode1:/tmp/
docker cp dbm_certificate_1503.pvk sqlNode1:/tmp/
docker cp dbm_certificate_1503.cer sqlNode2:/tmp/
docker cp dbm_certificate_1503.pvk sqlNode2:/tmp/

各节点相互还原证书

sqlcmd -S "127.0.0.1,1501" -d "master" -U SA -P "Str0ngPa@w0rd" -Q "
CREATE CERTIFICATE dbm_certificate_1502  
    AUTHORIZATION dbm_user
    FROM FILE = '/tmp/dbm_certificate_1502.cer'
    WITH PRIVATE KEY (
        FILE = '/tmp/dbm_certificate_1502.pvk',
        DECRYPTION BY PASSWORD = 'MyStr0ngPa@w0rd'
    );
GO
CREATE CERTIFICATE dbm_certificate_1503  
AUTHORIZATION dbm_user
FROM FILE = '/tmp/dbm_certificate_1503.cer'
WITH PRIVATE KEY (
    FILE = '/tmp/dbm_certificate_1503.pvk',
    DECRYPTION BY PASSWORD = 'MyStr0ngPa@w0rd'
    );
GO"

sqlcmd -S "127.0.0.1,1502" -d "master" -U SA -P "Str0ngPa@w0rd" -Q "
CREATE CERTIFICATE dbm_certificate_1501  
    AUTHORIZATION dbm_user
    FROM FILE = '/tmp/dbm_certificate_1501.cer'
    WITH PRIVATE KEY (
        FILE = '/tmp/dbm_certificate_1501.pvk',
        DECRYPTION BY PASSWORD = 'MyStr0ngPa@w0rd'
    );
GO
CREATE CERTIFICATE dbm_certificate_1503  
    AUTHORIZATION dbm_user
    FROM FILE = '/tmp/dbm_certificate_1503.cer'
    WITH PRIVATE KEY (
        FILE = '/tmp/dbm_certificate_1503.pvk',
        DECRYPTION BY PASSWORD = 'MyStr0ngPa@w0rd'
    );
GO"

sqlcmd -S "127.0.0.1,1503" -d "master" -U SA -P "Str0ngPa@w0rd" -Q "
CREATE CERTIFICATE dbm_certificate_1501  
    AUTHORIZATION dbm_user
    FROM FILE = '/tmp/dbm_certificate_1501.cer'
    WITH PRIVATE KEY (
        FILE = '/tmp/dbm_certificate_1501.pvk',
        DECRYPTION BY PASSWORD = 'MyStr0ngPa@w0rd'
    );
GO
CREATE CERTIFICATE dbm_certificate_1502 
    AUTHORIZATION dbm_user
    FROM FILE = '/tmp/dbm_certificate_1502.cer'
    WITH PRIVATE KEY (
        FILE = '/tmp/dbm_certificate_1502.pvk',
        DECRYPTION BY PASSWORD = 'MyStr0ngPa@w0rd'
    );
GO"

现在，开始创建 AlwaysOn Availability Group，因为非集群，所以设置 CLUSTER_TYPE = EXTERNAL

sqlcmd -S "127.0.0.1,1501" -d "master" -U SA -P "Str0ngPa@w0rd" -Q "
CREATE AVAILABILITY GROUP [MyAG]
    WITH (CLUSTER_TYPE = NONE)
    FOR REPLICA ON
    N'sqlNode1'
        WITH (
        ENDPOINT_URL = N'tcp://sqlNode1:5022',
        AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
            SEEDING_MODE = AUTOMATIC,
            FAILOVER_MODE = MANUAL,
            SECONDARY_ROLE (ALLOW_CONNECTIONS = ALL)
        ),
    N'sqlNode2'
        WITH (
        ENDPOINT_URL = N'tcp://sqlNode2:5022',
        AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
            SEEDING_MODE = AUTOMATIC,
            FAILOVER_MODE = MANUAL,
            SECONDARY_ROLE (ALLOW_CONNECTIONS = ALL)
        ),
    N'sqlNode3'
        WITH (
        ENDPOINT_URL = N'tcp://sqlNode3:5022',
        AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
            SEEDING_MODE = AUTOMATIC,
            FAILOVER_MODE = MANUAL,
            SECONDARY_ROLE (ALLOW_CONNECTIONS = ALL)
        );
GO"

两个辅助节点加入 AG

sqlcmd -S "127.0.0.1,1502" -d "master" -U SA -P "Str0ngPa@w0rd" -Q "
ALTER AVAILABILITY GROUP [MyAG] JOIN WITH (CLUSTER_TYPE = NONE);
GO
ALTER AVAILABILITY GROUP [MyAG] GRANT CREATE ANY DATABASE;
GO"

sqlcmd -S "127.0.0.1,1503" -d "master" -U SA -P "Str0ngPa@w0rd" -Q "
ALTER AVAILABILITY GROUP [MyAG] JOIN WITH (CLUSTER_TYPE = NONE);
GO
ALTER AVAILABILITY GROUP [MyAG] GRANT CREATE ANY DATABASE;
GO"

现在，我们可以在Primary节点创建数据库进行测试。我们配置 AG时已经设置自动播种模式（SEEDING_MODE = AUTOMATIC），所以数据库就不需要通过备份还原初始化了。

sqlcmd -S "127.0.0.1,1501" -d "master" -U SA -P "Str0ngPa@w0rd" -Q "
CREATE DATABASE test;
GO
ALTER DATABASE test SET RECOVERY FULL;
GO
BACKUP DATABASE test TO DISK = 'NUL:';
GO
BACKUP LOG test TO  DISK = 'NUL:';
GO
ALTER AVAILABILITY GROUP [MyAG] ADD DATABASE [test];
GO"

如何切换角色呢？比如我们将primary切换到端口为 1502 的节点。

sqlcmd -S "127.0.0.1,1502" -d "master" -U SA -P "Str0ngPa@w0rd" -Q "ALTER AVAILABILITY GROUP [MyAG] FORCE_FAILOVER_ALLOW_DATA_LOSS"

如果你想用到开发或测试环境，你也可以把数据设置存储到容器之外的磁盘位置，但资源要提前规划好（生产环境没必要容器化数据库）。当然，容器化的一个好处就是，你可以创建更多的独立容器实例来模拟各种测试，相比以前我们开很多虚拟机方便多了！

更多资源，请关注公众号

参考：

Quickstart: Run SQL Server container images with Docker

SQL Server 2019 AlwaysOn Availability Group on Docker Containers