2023年6月30日发(作者：)

Docker部署SQLServer2019AlwaysOn集群的实现⽬录Docker部署Always on集群安装Docker架构准备相关容器镜像操作系统开始配置-容器步骤1：创建Dockerfile步骤2：编译镜像步骤3：创建容器步骤4：启动容器步骤5：SSMS连接MSSQL配置-数据库步骤1：连接主库-sqlNode1步骤2：连接从库-sqlNode2和sqlNode3步骤3：所有节点步骤4：创建⾼可⽤组测试参考连接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宿主机IP1501:1433主sqlNode2宿主机IP1502:1433副本sqlNode3宿主机IP1503:1433副本端⼝表⽰：外⽹端⼝：内⽹端⼝准备相关容器镜像拉取操作系统和数据库的Docker镜像，如下操作系统docker pull ubuntu:18.04SQL Server 2019docker pull /mssql/server:2019-latest可通过docker images来查看已下载的镜像信息。开始配置-容器环境准备完毕后，开始正式的配置安装。步骤1：创建Dockerfile创建⽬录⽤于存放dockerfile、等⽂件。mkdir /sql2019hacd /sql2019hatouch dockerfilevi dockerfiledockerfile内容如下FROM ubuntu:18.04

RUN apt-get update

RUN apt install sudo wget curl gnupg gnupg1 gnupg2 -yRUN apt install software-properties-common systemd vim -yRUN wget -qO- /keys/ | sudo apt-key add -

RUN add-apt-repository "$(wget -qO- /config/ubuntu/18.04/)"RUN apt-get updateRUN apt-get install -y mssql-server

RUN /opt/mssql/bin/mssql-conf set abled 1RUN /opt/mssql/bin/mssql-conf set d true

EXPOSE 1433

ENTRYPOINT /opt/mssql/bin/sqlservr说明：FROM:表⽰基于什么镜像进⾏安装的RUN:在镜像中进⾏的操作EXPOSE:指定服务端⼝ENTRYPOINT:运⾏命令步骤2：编译镜像通过dockerfile来编译镜像，⽤于后⾯的安装，命令：docker build -t sqlag2019:ha .其中sqlag2019为镜像名称，ha是镜像标签，.表⽰在当前⽬录下编译，因为dockerfile就在当前⽬录下。以下输出是精简的，实际上输出⾮常多…也需要⼀定时间(安装⼀些包、数据库等)，由⽹速决定$ docker build -t sqlag2019:ha .Sending build context to Docker daemon 2.56kBStep 1/12 : FROM ubuntu:18.04 ---> c3c304cb4f22Step 2/12 : RUN apt-get update ---> Running in 950e50f80f00Get:1 /ubuntu bionic InRelease [242 kB]Get:2 /ubuntu bionic-security InRelease [88.7 kB]Get:3 /ubuntu bionic-security/main amd64 Packages [932 kB]Get:4 /ubuntu bionic-updates InRelease [88.7 kB]...Step 3/12 : RUN apt install sudo wget curl gnupg gnupg1 gnupg2 -y ---> Running Step 8/12 : RUN sudo apt-get install -y mssql-server ---> Running in 43d82a503f8aReading Building Reading The following additional packages will be installed:Step 9/12 : RUN sudo /opt/mssql/bin/mssql-conf set abled 1 ---> Running in 166c6596d2ddSQL Server needs to be restarted in order to apply this setting. Please run'systemctl restart e'.Removing intermediate container 166c6596d2dd ---> bcdb057fed43Step 10/12 : RUN sudo /opt/mssql/bin/mssql-conf set d true ---> Running in 22dd6a93d1efSQL Server needs to be restarted in order to apply this setting. Please run'systemctl restart e'.Removing intermediate container 22dd6a93d1ef ---> 6b90afbaf94eStep 11/12 : EXPOSE 1433 ---> Running in bcc14f3b0badRemoving intermediate container bcc14f3b0bad ---> 4aae1563aa74Step 12/12 : ENTRYPOINT /opt/mssql/bin/sqlservr ---> Running in 68b6ed45ff6aRemoving intermediate container 68b6ed45ff6a ---> b7467618c371Successfully built b7467618c371Successfully tagged sqlag2019:ha最后出现Successfully表⽰编译成功，否则根据错误信息进⾏解决。步骤3：创建容器现在镜像编译好了，下⾯再通过docker-compose⽂件来创建、配置3个容器，具体内容如下：$ touch $ vi sion: '3'

services: db1: container_name: sqlNode1 image: sqlag2019:ha hostname: sqlNode1 domainname: environment: SA_PASSWORD: "MyPassWord123" ACCEPT_EULA: "Y" ports: - "1501:1433" extra_hosts: : "172.16.238.22" : "172.16.238.23" networks: internal: ipv4_address: 172.16.238.21

db2: container_name: sqlNode2 image: sqlag2019:ha hostname: sqlNode2 domainname: environment: SA_PASSWORD: "MyPassWord123" ACCEPT_EULA: "Y" ports: - "1502:1433" extra_hosts: : "172.16.238.21" : "172.16.238.23" networks: internal: ipv4_address: 172.16.238.22

db3: container_name: sqlNode3 image: sqlag2019:ha hostname: sqlNode3 domainname: environment: SA_PASSWORD: "MyPassWord123" ACCEPT_EULA: "Y" ports: - "1503:1433" extra_hosts: : "172.16.238.21" : "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 -dCreating network "sql2019hademo_internal" with the default driverCreating sqlNode2 ...Creating sqlNode1 ...Creating sqlNode2Creating sqlNode3 ...Creating sqlNode1Creating sqlNode2 ... done注意：docker-compose是需要单独安装的，就是⼀个可执⾏⽂件。可通过apt、yum来安装。查看容器状态$ docker-compose psName Command State Ports--------------------------------------------------------------------------sqlNode1 /bin/sh -c /opt/mssql/bin/ ... Up 0.0.0.0:1501->1433/tcpsqlNode2 /bin/sh -c /opt/mssql/bin/ ... Up 0.0.0.0:1502->1433/tcpsqlNode3 /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_和/tmp/dbm_⽂件中。我们将这些⽂件复制到其他节点，并根据以下⽂件创建主密钥和证书：执⾏以下脚本USE masterGO

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';goCREATE CERTIFICATE dbm_certificate WITH SUBJECT = 'dbm';BACKUP CERTIFICATE dbm_certificateTO FILE = '/tmp/dbm_'WITH PRIVATE KEY ( FILE = '/tmp/dbm_', ENCRYPTION BY PASSWORD = 'MyStr0ngPa$w0rd' );GO将⽂件拷贝到其他两个节点：$ docker cp sqlNode1:/tmp/dbm_ .$ docker cp sqlNode1:/tmp/dbm_ .$ docker cp dbm_ sqlNode2:/tmp/$ docker cp dbm_ sqlNode2:/tmp/$ docker cp dbm_ sqlNode3:/tmp/$ docker cp dbm_ 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';GOCREATE CERTIFICATE dbm_certificate

AUTHORIZATION dbm_user FROM FILE = '/tmp/dbm_' WITH PRIVATE KEY ( FILE = '/tmp/dbm_', 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;GOALTER DATABASE agtestdb SET RECOVERY FULL;GOBACKUP DATABASE agtestdb TO DISK = '/var/opt/mssql/data/';GOALTER AVAILABILITY GROUP [ag1] ADD DATABASE [agtestdb];GO通过SSMS查看同步状态是否正常.参考连接到此这篇关于Docker部署SQL Server 2019 Always On集群的实现的⽂章就介绍到这了,更多相关Docker部署SQLServer集群内容请搜索以前的⽂章或继续浏览下⾯的相关⽂章希望⼤家以后多多⽀持！