| 主機名 | IP | 端口 | 角色 |
|---|---|---|---|
| sqlNode1 | 宿主機IP | 1501:1433 | 主 |
| sqlNode2 | 宿主機IP | 1502:1433 | 副本 |
| sqlNode3 | 宿主機IP | 1503:1433 | 副本 |
端口表示：外網(wǎng)端口：內(nèi)網(wǎng)端口
拉取操作系統(tǒng)和數(shù)據(jù)庫的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
環(huán)境準備完畢后，開始正式的配置安裝。
創(chuàng)建目錄用于存放dockerfile、docker-compose.yml等文件。
mkdir /sql2019ha cd /sql2019ha touch dockerfile vi dockerfile
dockerfile內(nèi)容如下
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
說明：
通過dockerfile來編譯鏡像，用于后面的安裝，命令：docker build -t sqlag2019:ha .
其中sqlag2019為鏡像名稱，ha是鏡像標簽，.表示在當前目錄下編譯，因為dockerfile就在當前目錄下。
以下輸出是精簡的，實際上輸出非常多…也需要一定時間(安裝一些包、數(shù)據(jù)庫等)，由網(wǎng)速決定
$ 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
最后出現(xiàn)Successfully表示編譯成功，否則根據(jù)錯誤信息進行解決。
現(xiàn)在鏡像編譯好了，下面再通過docker-compose文件來創(chuàng)建、配置3個容器，具體內(nèi)容如下：
$ 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
然后通過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是需要單獨安裝的，就是一個可執(zhí)行文件?？赏ㄟ^apt、yum來安裝。
查看容器狀態(tài)
$ 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
至此容器已經(jīng)啟動完成，下面通過SSMS連接數(shù)據(jù)庫進行相關檢查和配置ALWAYSON。
通過宿主機的外網(wǎng)IP+端口連接相應的數(shù)據(jù)庫，如下：
注意：IP和端口之間是逗號
可以看到數(shù)據(jù)庫的圖標也是Linux的圖標。
這部分就是在數(shù)據(jù)庫中進行相關配置，如：創(chuàng)建KEY加密文件，管理用戶、可用組等。
主庫也就是節(jié)點1，端口是1501，連接方法如上圖。
我們將證書和私鑰提取到/tmp/dbm_certificate.cer和/tmp/dbm_certificate.pvk文件中。
我們將這些文件復制到其他節(jié)點，并根據(jù)以下文件創(chuàng)建主密鑰和證書：執(zhí)行以下腳本
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
將文件拷貝到其他兩個節(jié)點：
$ 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/
兩個從庫的端口分別是：1502和1503.然后重復主庫執(zhí)行的操作，如下：
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
在所有節(jié)點上執(zhí)行以下命令
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，在所有節(jié)點執(zhí)行以下命令
ALTER EVENT SESSION AlwaysOn_health ON SERVER WITH (STARTUP_STATE=ON); GO
可以用SSMS工具和T-SQL兩種方式，下面以T-SQL為例：
運行以下腳本在主節(jié)點中創(chuàng)建一個可用性組。 請注意，選擇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
在從庫中執(zhí)行以下命令，將從庫加入到AG組中
ALTER AVAILABILITY GROUP [ag1] JOIN WITH (CLUSTER_TYPE = NONE); ALTER AVAILABILITY GROUP [ag1] GRANT CREATE ANY DATABASE; GO
至此在Docker容器中安裝SQL Server Alwayson集群已經(jīng)完成了！
注意：當指定CLUSTER_TYPE = NONE創(chuàng)建可用組時，在執(zhí)行故障轉移時需執(zhí)行以下命令
ALTER AVAILABILITY GROUP [ag1] FORCE_FAILOVER_ALLOW_DATA_LOSS
在主庫上創(chuàng)建一個數(shù)據(jù)庫，并加入到可用組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查看同步狀態(tài)是否正常.
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集群的實現(xiàn)的文章就介紹到這了,更多相關Docker部署SQLServer集群內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！
