Docker 容器部署 SQL Server AlwaysOn AG
SQL Server 2016 开始支持 Linux。随着2017 和2019 版本的推出，它开始支持Linux和Container平台上的HA/DR、Kubernetes和大数据集群解决方案。今天我们就直接测试，使用容器构建SQL Server AlwaysOn AG，这对于学习AlwaysOn 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
华为开发者空间，是为全球开发者打造的专属开发空间，汇聚了华为优质开发资源及工具，致力于让每一位开发者拥有一台云主机，基于华为根生态开发、创新。
更多推荐
0
0- 0
已为社区贡献11条内容
所有评论(0)