mssql alwayson

准备工作：
安装docker-compose
```bash
#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
```

```bash
#拉取系统和sql server镜像
docker pull ubuntu:18.04
docker pull mcr.microsoft.com/mssql/server:2019-latest
```
```bash
#创建数据存储文件夹
mkdir -p /docker/mssql
touch Dockerfile
vim Dockerfile
```
```bash
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
```
```bash
#生成镜像，需要费点时间
docker build -t sql2019:mbs .
```
```bash
#创建文件并设置权限
mkdir -p sqlnode1 sqlnode2 sqlnode3
chown -R 10001:0  ./sqlnode1
chown -R 10001:0  ./sqlnode2
chown -R 10001:0  ./sqlnode3
```
```bash
#配置docker-compose.yml文件
touch docker-compose.yml
vim docker-compose.yml
```
```bash
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
```
```bash
#启动容器
docker-compose up -d
#查看容器状态
docker-compose ps
```
现在开始配置-数据库
连接主库-sqlNode1
我们将证书和私钥提取到/tmp/dbm_certificate.cer和/tmp/dbm_certificate.pvk文件中。
我们将这些文件复制到其他节点，并根据以下文件创建主密钥和证书：执行以下脚本
```sql
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
```
```bash
#将文件拷贝到其他两个节点：
 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.然后重复主库执行的操作，如下：
```sql
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
```
在所有节点上执行以下命令
```sql
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，在所有节点执行以下命令
```sql
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：
```sql
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组中
```sql
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创建可用组时，在执行故障转移时需执行以下命令
```sql
ALTER AVAILABILITY GROUP [ag1] FORCE_FAILOVER_ALLOW_DATA_LOSS
```
测试在主库上创建一个数据库，并加入到可用组AG中。
```sql
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
```

添加新评论

关于博主

文章分类

最受欢迎的文章

最近回复

其它