mssql alwayson

本文详细介绍了在Docker容器中部署SQL Server 2019 AlwaysOn可用性组（AG）的完整过程。包括：安装docker-compose、拉取Ubuntu及SQL Server镜像、构建自定义镜像、配置三个节点的docker-compose.yml、启动容器；然后在主节点创建登录、主密钥、证书并分发到从节点；配置端点、启用AlwaysOn健康会话；创建无集群管理器（CLUSTER_TYPE=NONE）的可用性组，将从节点加入AG；最后测试创建数据库并加入可用性组。

Docker
作者：zhuge···预计阅读 24 分钟·1,344 阅读·0 评论
mssql alwayson

准备工作： 安装docker-compose

#https://github.com/docker/compose/releases
curl -L https://get.daocloud.io/docker/compose/releases/download/v2.12.2/docker-compose-`uname -s`-`uname -m` > /usr/local/bin/docker-compose
sudo chmod +x /usr/local/bin/docker-compose
sudo ln -s /usr/local/bin/docker-compose /usr/bin/docker-compose
#检查是否正确安装
docker-compose version

#拉取系统和sql server镜像
docker pull ubuntu:18.04
docker pull mcr.microsoft.com/mssql/server:2019-latest

#创建数据存储文件夹
mkdir -p /docker/mssql
touch Dockerfile
vim 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
#安装sql-tools
#curl https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add -
#curl https://packages.microsoft.com/config/ubuntu/18.04/prod.list | sudo tee /etc/apt/sources.list.d/msprod.list
#apt-get update
#apt-get install 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
EXPOSE 1433
ENTRYPOINT /opt/mssql/bin/sqlservr

#生成镜像，需要费点时间
docker build -t sql2019:mbs .

#创建文件并设置权限
mkdir -p sqlnode1 sqlnode2 sqlnode3
chown -R 10001:0  ./sqlnode1
chown -R 10001:0  ./sqlnode2
chown -R 10001:0  ./sqlnode3

#配置docker-compose.yml文件
touch docker-compose.yml
vim docker-compose.yml

version: '3'
 
services:
  db1:
    container_name: sqlNode1
    image: sql2019:mbs
    hostname: sqlNode1
    domainname: sql.local
    environment:
      SA_PASSWORD: "@Mssql2022"
      ACCEPT_EULA: "Y"
      MSSQL_PID: "HMWJ3-KY3J2-NMVD7-KG4JR-X2G8G"
    ports:
    - "1501:1433"
    volumes:
    - "./sqlNode1:/var/opt/mssql"
    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: sql2019:mbs
    hostname: sqlNode2
    domainname: sql.local
    environment:
      SA_PASSWORD: "@Mssql2022"
      ACCEPT_EULA: "Y"
      MSSQL_PID: "HMWJ3-KY3J2-NMVD7-KG4JR-X2G8G"
    ports:
    - "1502:1433"
    volumes:
    - "./sqlNode2:/var/opt/mssql"
    extra_hosts:
      sqlNode1.sql.local: "172.16.238.21"
      sqlNode3.sql.local: "172.16.238.23"
    networks:
        internal:
                ipv4_address: 172.16.238.22
 
  db3:
    container_name: sqlNode3
    image: sql2019:mbs
    hostname: sqlNode3
    domainname: sql.local
    environment:
      SA_PASSWORD: "@Mssql2022"
      ACCEPT_EULA: "Y"
      MSSQL_PID: "HMWJ3-KY3J2-NMVD7-KG4JR-X2G8G"
    ports:
    - "1503:1433"
    volumes:
    - "./sqlNode3:/var/opt/mssql"
    extra_hosts:
      sqlNode1.sql.local: "172.16.238.21"
      sqlNode2.sql.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
#查看容器状态
docker-compose ps

现在开始配置-数据库 连接主库-sqlNode1 我们将证书和私钥提取到/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/

连接从库-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

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

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

运行以下脚本在主节点中创建一个可用性组。 请注意，选择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 testdb;
GO
ALTER DATABASE testdb SET RECOVERY FULL;
GO
BACKUP DATABASE testdb TO DISK = '/var/opt/mssql/data/testdb.bak';
GO
ALTER AVAILABILITY GROUP [ag1] ADD DATABASE [testd]b;
GO

相关文章

Docker

在 Ubuntu 系统中修改 Docker 和 Containerd 数据存储位置

42 阅读
Docker

在Ubuntu 中安装 NVIDIA Container Toolkit

29 阅读
Docker

创建net8镜像

865 阅读

评论

加载中...