SQL Server Always On可用性グループ構築（Docker）

前提

• 個人的なメモのつもりで記述しています。（気が向くままに書いていきます。）
• 各ファイルの解説は時間があれば、少しずつ追記していく予定です。

各ファイル

docker-compose.yml

docker-compose.yml

version: "3"
services:
  mssql1:
    build: ./mssql
    image: localhost:6000/mssql:2019-latest
    container_name: mssql1
    hostname: mssql1
    restart: always
    networks:
      sql_backend:
        ipv4_address: 172.16.0.10
    volumes:
      - mssql_volume1:/var/opt/mssql/
    ports:
      - 11433:1433
    environment:
      - ACCEPT_EULA=Y
      - SA_PASSWORD=Passw0rd
      - MSSQL_LCID=1041
      - TZ=Asia/Tokyo
      - CLUSTER=PRIMARY
  mssql2:
    image: localhost:6000/mssql:2019-latest
    container_name: mssql2
    hostname: mssql2
    restart: always
    networks:
      sql_backend:
        ipv4_address: 172.16.0.11
    volumes:
      - mssql_volume2:/var/opt/mssql/
    ports:
      - 21433:1433
    environment:
      - ACCEPT_EULA=Y
      - SA_PASSWORD=Passw0rd
      - MSSQL_LCID=1041
      - TZ=Asia/Tokyo
      - CLUSTER=SECONDARY
networks:
  sql_backend:
    driver: bridge
    ipam:
      driver: default
      config:
        - subnet: 172.16.0.0/24
volumes:
  mssql_volume1:
    driver: local
  mssql_volume2:
    driver: local

Dockerfile

FROM mcr.microsoft.com/mssql/server:2019-latest

USER root
RUN apt update \
    && apt-get install -y iputils-ping net-tools \
    && /opt/mssql/bin/mssql-conf set hadr.hadrenabled 1

COPY [ \
    "dbm_certificate.cer", \
    "dbm_certificate.pvk", \
    "/var/opt/mssql/data/" \
    ]

WORKDIR /docker-entrypoint-initdb.d
COPY ./initdb.d/ /docker-entrypoint-initdb.d/
RUN chmod -R +x /docker-entrypoint-initdb.d

ENTRYPOINT ["/bin/bash", "./entrypoint.sh"]

entrypoint.sh

entrypoint.sh

#!/bin/bash

/docker-entrypoint-initdb.d/init.sh &
/opt/mssql/bin/sqlservr

• init.sh

init.sh

#!/bin/bash

# ファイルで初期化済みかを判断する
if [ ! -f /var/opt/mssql/data/init_process.txt ]; then
    touch /var/opt/mssql/data/init_process.txt

    sleep 60
    echo "##########################"
    echo "共通処理"
    echo "##########################"
    /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P Passw0rd -i /docker-entrypoint-initdb.d/common.sql

    if [ $CLUSTER = "PRIMARY" ]; then

        echo "##########################"
        echo "プライマリサーバー処理"
        echo "##########################"
        /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P Passw0rd -i /docker-entrypoint-initdb.d/primary.sql

        echo "-----------------------"
        echo " DB 作成"
        /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P Passw0rd -i /docker-entrypoint-initdb.d/createDatabase.sql

    elif [ $CLUSTER = "SECONDARY" ]; then

        sleep 60
        echo "##########################"
        echo "セカンダリサーバー処理"
        echo "##########################"
        /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P Passw0rd -i /docker-entrypoint-initdb.d/secondary.sql

    else
        echo "##########################"
        echo "その他"
        echo "##########################"
    fi
else
    echo "#############################"
    echo " 初期化済みのため、処理スキップ "
    echo "#############################"
fi

common.sql

common.sql

PRINT '======================================='
PRINT '    プライマリ・セカンダリ共通クエリ 実行'
PRINT '======================================='

USE [master]
GO

PRINT '================================='
PRINT '使用最大メモリの設定'
EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'max server memory', 1024;
GO
RECONFIGURE;
GO

PRINT '================================='
PRINT 'AlwaysOn_health イベント セッションを有効化'
ALTER EVENT SESSION  AlwaysOn_health ON SERVER WITH (STARTUP_STATE=ON);
GO

PRINT '================================='
PRINT 'マスターキー作成'
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Passw0rd';
GO

PRINT '================================='
PRINT '証明書を作成'
CREATE CERTIFICATE dbm_certificate
    FROM FILE = '/var/opt/mssql/data/dbm_certificate.cer'
    WITH PRIVATE KEY (
    FILE = '/var/opt/mssql/data/dbm_certificate.pvk',
    DECRYPTION BY PASSWORD = 'Passw0rd'
    );
GO

PRINT '================================='
PRINT 'エンドポイントを作成'
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
);
GO

IF (SELECT state FROM sys.endpoints WHERE name = N'Hadr_endpoint') <> 0
BEGIN
    PRINT '●================================'
	ALTER ENDPOINT [Hadr_endpoint] STATE = STARTED
END
GO

primary.sql

primary.sql

PRINT '================================='
PRINT '    プライマリサーバー用処理 実行    '
PRINT '================================='

use [master]
GO

PRINT '-------------------------------'
PRINT '    TestDB作成　&　フルバックアップ'
CREATE DATABASE [TestDB];
ALTER DATABASE [TestDB] SET RECOVERY FULL;
BACKUP DATABASE [TestDB] TO DISK = N'/var/opt/mssql/data/TestDB.bak';

PRINT '-------------------------------'
PRINT '    AlwaysOn_health イベント セッションを有効'
ALTER EVENT SESSION [AlwaysOn_health] ON SERVER STATE=START
GO

PRINT '-------------------------------'
PRINT '    可用性グループ作成   '
CREATE AVAILABILITY GROUP [MyAg]
WITH (
    AUTOMATED_BACKUP_PREFERENCE = SECONDARY,
    DB_FAILOVER = ON,
    CLUSTER_TYPE = NONE,
    REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT = 0
    )
FOR DATABASE TestDB
REPLICA ON
N'mssql1' WITH (
    ENDPOINT_URL = N'TCP://mssql1:5022',
    FAILOVER_MODE = MANUAL,
    AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
    BACKUP_PRIORITY = 50,
    SEEDING_MODE = AUTOMATIC,
    SECONDARY_ROLE(ALLOW_CONNECTIONS = ALL)
)
,
N'mssql2' WITH (
    ENDPOINT_URL = N'TCP://mssql2:5022',
    FAILOVER_MODE = MANUAL,
    AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
    BACKUP_PRIORITY = 50,
    SEEDING_MODE = AUTOMATIC,
    SECONDARY_ROLE(ALLOW_CONNECTIONS = ALL)
)
;
GO

ALTER AVAILABILITY GROUP [MyAg] GRANT CREATE ANY DATABASE
GO

secondary.sql

secondary.sql

PRINT '================================='
PRINT '    セカンダリサーバー用処理 実行'
PRINT '================================='

use [master]
GO

ALTER AVAILABILITY GROUP [MyAg] JOIN WITH (CLUSTER_TYPE = NONE);
GO

ALTER AVAILABILITY GROUP [MyAg] GRANT CREATE ANY DATABASE;
GO

createDatabase.sql

createDatabase.sql

PRINT '================================='
PRINT '    データベース作成.sql 実行'
PRINT '================================='

PRINT '-------------------------------'
PRINT '    ConfigDB作成　&　フルバックアップ'
USE [master]
GO

CREATE DATABASE [AK_ConfigDB]
GO
ALTER DATABASE [AK_ConfigDB] SET RECOVERY FULL
GO

BACKUP DATABASE [AK_ConfigDB] TO DISK = N'/var/opt/mssql/data/AK_ConfigDB.bak';
GO

PRINT '------------------------------------------'
PRINT ' AVAILABILITY GROUP にDB(AK_ConfigDB)を追加'
ALTER AVAILABILITY GROUP MyAg ADD DATABASE AK_ConfigDB
GO