• 企业400电话
  • 微网小程序
  • AI电话机器人
  • 电商代运营
  • 全 部 栏 目

    企业400电话 网络优化推广 AI电话机器人 呼叫中心 网站建设 商标✡知产 微网小程序 电商运营 彩铃•短信 增值拓展业务
    Docker部署SQL Server 2019 Always On集群的实现

    Docker部署Always on集群

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

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

    我们的目标是使用单个配置文件快速准备好环境。因此,开发人员或测试团队可以快速执行诸如兼容性、连通性、代码功能等测试。

    在本节中,我们将首先准备一个基于Ubuntu的映像,以便能够在容器上安装可用性组。然后我们将执行必要的安装。

    重要提示:不建议在生产环境中执行操作。安装是在Ubuntu 18.04上执行的。

    安装Docker

    安装Docker就不介绍了,自行安装即可.

    架构

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

    端口表示:外网端口:内网端口

    准备相关容器镜像

    拉取操作系统和数据库的Docker镜像,如下

    操作系统

    docker pull ubuntu:18.04
    

    SQL Server 2019

    docker pull mcr.microsoft.com/mssql/server:2019-latest
    

    可通过docker images来查看已下载的镜像信息。

    镜像地址:https://hub.docker.com/_/microsoft-mssql-server

    开始配置-容器

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

    步骤1:创建Dockerfile

    创建目录用于存放dockerfile、docker-compose.yml等文件。

    mkdir /sql2019ha
    cd /sql2019ha
    touch dockerfile
    vi dockerfile
    

    dockerfile内容如下

    FROM ubuntu:18.04
     
    RUN apt-get update
     
    RUN apt install sudo wget curl gnupg gnupg1 gnupg2 -y
    RUN apt install software-properties-common systemd vim -y
    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 mssql-server
     
    RUN /opt/mssql/bin/mssql-conf set hadr.hadrenabled  1
    RUN /opt/mssql/bin/mssql-conf set sqlagent.enabled true
     
    EXPOSE 1433
     
    ENTRYPOINT /opt/mssql/bin/sqlservr
    

    说明

    步骤2:编译镜像

    通过dockerfile来编译镜像,用于后面的安装,命令:docker build -t sqlag2019:ha .

    其中sqlag2019为镜像名称,ha是镜像标签,.表示在当前目录下编译,因为dockerfile就在当前目录下。

    以下输出是精简的,实际上输出非常多…也需要一定时间(安装一些包、数据库等),由网速决定

    $ docker build -t sqlag2019:ha .
    Sending build context to Docker daemon   2.56kB
    Step 1/12 : FROM ubuntu:18.04
     ---> c3c304cb4f22
    Step 2/12 : RUN apt-get update
     ---> Running in 950e50f80f00
    Get:1 http://archive.ubuntu.com/ubuntu bionic InRelease [242 kB]
    Get:2 http://security.ubuntu.com/ubuntu bionic-security InRelease [88.7 kB]
    Get:3 http://security.ubuntu.com/ubuntu bionic-security/main amd64 Packages [932 kB]
    Get:4 http://archive.ubuntu.com/ubuntu bionic-updates InRelease [88.7 kB]...
    Step 3/12 : RUN apt install sudo wget curl gnupg gnupg1 gnupg2 -y
     ---> Running in edc9d15b2383
    ..
    ..
    Step 8/12 : RUN sudo apt-get install -y mssql-server
     ---> Running in 43d82a503f8a
    Reading package lists...
    Building dependency tree...
    Reading state information...
    The following additional packages will be installed:
    Step 9/12 : RUN sudo /opt/mssql/bin/mssql-conf set hadr.hadrenabled  1
     ---> Running in 166c6596d2dd
    SQL Server needs to be restarted in order to apply this setting. Please run
    'systemctl restart mssql-server.service'.
    Removing intermediate container 166c6596d2dd
     ---> bcdb057fed43
    Step 10/12 : RUN sudo /opt/mssql/bin/mssql-conf set sqlagent.enabled true
     ---> Running in 22dd6a93d1ef
    SQL Server needs to be restarted in order to apply this setting. Please run
    'systemctl restart mssql-server.service'.
    Removing intermediate container 22dd6a93d1ef
     ---> 6b90afbaf94e
    Step 11/12 : EXPOSE 1433
     ---> Running in bcc14f3b0bad
    Removing intermediate container bcc14f3b0bad
     ---> 4aae1563aa74
    Step 12/12 : ENTRYPOINT /opt/mssql/bin/sqlservr
     ---> Running in 68b6ed45ff6a
    Removing intermediate container 68b6ed45ff6a
     ---> b7467618c371
    Successfully built b7467618c371
    Successfully tagged sqlag2019:ha
    

    最后出现Successfully表示编译成功,否则根据错误信息进行解决。

    步骤3:创建容器

    现在镜像编译好了,下面再通过docker-compose文件来创建、配置3个容器,具体内容如下:

    $ touch docker-compose.yml
    $ vi docker-compose.yml
    
    version: '3'
     
    services:
      db1:
        container_name: sqlNode1
        image: sqlag2019:ha
        hostname: sqlNode1
        domainname: lab.local
        environment:
          SA_PASSWORD: "MyPassWord123"
          ACCEPT_EULA: "Y"
        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: sqlag2019:ha
        hostname: sqlNode2
        domainname: lab.local
        environment:
          SA_PASSWORD: "MyPassWord123"
          ACCEPT_EULA: "Y"
        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: sqlag2019:ha
        hostname: sqlNode3
        domainname: lab.local
        environment:
          SA_PASSWORD: "MyPassWord123"
          ACCEPT_EULA: "Y"
        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
    

    步骤4:启动容器

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

    $ docker-compose up -d
    Creating network "sql2019hademo_internal" with the default driver
    Creating sqlNode2 ...
    Creating sqlNode1 ...
    Creating sqlNode2
    Creating sqlNode3 ...
    Creating sqlNode1
    Creating sqlNode2 ... done
    

    注意:docker-compose是需要单独安装的,就是一个可执行文件。可通过apt、yum来安装。

    查看容器状态

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

    至此容器已经启动完成,下面通过SSMS连接数据库进行相关检查和配置ALWAYSON。

    步骤5:SSMS连接MSSQL

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

    注意:IP和端口之间是逗号


    可以看到数据库的图标也是Linux的图标。

    配置-数据库

    这部分就是在数据库中进行相关配置,如:创建KEY加密文件,管理用户、可用组等。

    步骤1:连接主库-sqlNode1

    主库也就是节点1,端口是1501,连接方法如上图。

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

    我们将这些文件复制到其他节点,并根据以下文件创建主密钥和证书:执行以下脚本

    USE master
    GO
     
    CREATE LOGIN dbm_login WITH PASSWORD = 'MyStr0ngPa$w0rd';
    CREATE USER dbm_user FOR LOGIN dbm_login;
    GO
     
    CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'MyStr0ngPa$w0rd';
    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 = 'MyStr0ngPa$w0rd'
        );
    GO
    

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

    $ 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/
    

    步骤2:连接从库-sqlNode2和sqlNode3

    两个从库的端口分别是:1502和1503.然后重复主库执行的操作,如下:

    CREATE LOGIN dbm_login WITH PASSWORD = 'MyStr0ngPa$w0rd';
    CREATE USER dbm_user FOR LOGIN dbm_login;
    GO
     
    CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'MyStr0ngPa$w0rd';
    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 = 'MyStr0ngPa$w0rd'
    );
    GO
    

    步骤3:所有节点

    在所有节点上执行以下命令

    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,在所有节点执行以下命令

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

    步骤4:创建高可用组

    可以用SSMS工具和T-SQL两种方式,下面以T-SQL为例:

    运行以下脚本在主节点中创建一个可用性组。 请注意,选择CLUSTER_TYPE = NONE选项是因为它是在没有诸如Pacemaker或Windows Server故障转移群集之类的群集管理平台的情况下安装的。

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

    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组中

    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创建可用组时,在执行故障转移时需执行以下命令

    ALTER AVAILABILITY GROUP [ag1] FORCE_FAILOVER_ALLOW_DATA_LOSS

    测试

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

    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
    

    通过SSMS查看同步状态是否正常.

    参考连接

    https://docs.microsoft.com/en-us/sql/linux/quickstart-install-connect-docker?view=sql-server-ver15
    https://docs.microsoft.com/en-us/sql/linux/quickstart-install-connect-ubuntu?view=sql-server-ver15
    https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-create-availability-group?view=sql-server-ver15
    https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-configure-mssql-conf?view=sql-server-ver15
    https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-configure-environment-variables?view=sql-server-ver15
    https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-availability-group-cluster-ubuntu?view=sql-server-linux-ver15
    https://docs.docker.com/engine/install/ubuntu/
    https://docs.docker.com/compose/

    到此这篇关于Docker部署SQL Server 2019 Always On集群的实现的文章就介绍到这了,更多相关Docker部署SQLServer集群内容请搜索脚本之家以前的文章或继续浏览下面的相关文章希望大家以后多多支持脚本之家!

    上一篇:IIS的web.config中跨域访问设置方法
    下一篇:Docke中安装redis的方法
  • 相关文章
  • 

    © 2016-2020 巨人网络通讯 版权所有

    《增值电信业务经营许可证》 苏ICP备15040257号-8

    Docker部署SQL Server 2019 Always On集群的实现 Docker,部署,SQL,Server,2019,