Implementation of Docker deployment of SQL Server 2019 Always On cluster

Docker deployment Always on cluster
Install Docker
Architecture
Prepare relevant container images

operating system

Start configuration-container

Step 1: Create a Dockerfile
Step 2: Compile the image
Step 3: Create a container
Step 4: Start the container
Step 5: Connect SSMS to MSSQL

Configuration - Database

Step 1: Connect to the main database - sqlNode1
Step 2: Connect to slave libraries - sqlNode2 and sqlNode3
Step 3: All nodes
Step 4: Create a high availability group

test

Reference Links

Docker deployment Always on cluster

SQL Server began supporting Linux in 2016. With the release of 2017 and 2019 versions, it began to support HA/DR, Kubernetes, and big data cluster solutions on Linux and container platforms.

In this article, we will install SQL Server 2019 on 3 nodes Docker container and create AlwaysOn availability group.

Our goal is to quickly prepare an environment using a single configuration file. Therefore, developers or testing teams can quickly perform tests such as compatibility, connectivity, code functionality, etc.

In this section, we will first prepare an Ubuntu-based image to be able to install an availability group on a container. We will then perform the necessary installation.

Important: This operation is not recommended for production environments. The installation was performed on Ubuntu 18.04.

Install Docker

I won’t introduce the installation of Docker, you can install it yourself.

Architecture

Hostname	IP	port	Role
sqlNode1	Host IP	1501:1433	host
sqlNode2	Host IP	1502:1433	Instances
sqlNode3	Host IP	1503:1433	Instances

Port indicates: External network port: Internal network port

Prepare relevant container images

Pull the Docker images of the operating system and database as follows

operating system

docker pull ubuntu:18.04

SQL Server 2019

docker pull mcr.microsoft.com/mssql/server:2019-latest

You can view the downloaded image information through docker images .

Mirror address: https://hub.docker.com/_/microsoft-mssql-server

Start configuration-container

After the environment is prepared, start the formal configuration and installation.

Step 1: Create a Dockerfile

Create a directory to store files such as dockerfile and docker-compose.yml.

mkdir /sql2019ha
cd /sql2019ha
touch dockerfile
vi dockerfile

The dockerfile content is as follows

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

illustrate :

FROM : indicates the image based on which the installation is based
RUN : Operations performed in the image
EXPOSE : Specify the service port
ENTRYPOINT : Run command

Step 2: Compile the image

Compile the image through Dockerfile for subsequent installation, command: docker build -t sqlag2019:ha .

Here, sqlag2019 is the image name, ha is the image tag, and . indicates compilation in the current directory because the Dockerfile is in the current directory.

The following output is concise. In fact, there is a lot of output... It also takes some time (installing some packages, databases, etc.), depending on the network speed

$ 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

The Successfully message at the end indicates that the compilation is successful. Otherwise, solve the problem according to the error message.

Step 3: Create a container

Now that the image is compiled, let's create and configure three containers through the docker-compose file. The specific contents are as follows:

$ 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

Step 4: Start the container

Then start the three containers through docker-compose up -d command, where -d means running in the background.

$ 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

Note: docker-compose needs to be installed separately, which is an executable file. Can be installed via apt, yum.

View container status

$ 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

Now the container has been started. Now connect to the database through SSMS to perform related checks and configure ALWAYSON.

Step 5: Connect SSMS to MSSQL

Connect to the corresponding database through the host's external network IP+port, as follows:

insert image description here

Note: There is a comma between the IP and the port

insert image description here

You can see that the database icon is also a Linux icon.

Configuration - Database

This part is to perform relevant configuration in the database, such as: creating KEY encryption files, managing users, available groups, etc.

Step 1: Connect to the main database - sqlNode1

The main database is node 1, the port is 1501, and the connection method is as shown above.

We extracted the certificate and private key into /tmp/dbm_certificate.cer and /tmp/dbm_certificate.pvk files.

We copy these files to other nodes and create a master key and certificate based on the following files: Execute the following script

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

Copy the file to the other two nodes:

$ 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/

Step 2: Connect to slave libraries - sqlNode2 and sqlNode3

The ports of the two slave libraries are 1502 and 1503. Then repeat the operations performed by the master library as follows:

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

Step 3: All nodes

Execute the following command on all nodes:

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];

Enable automatic startup ALWAYON and execute the following command on all nodes

ALTER EVENT SESSION AlwaysOn_health ON SERVER WITH (STARTUP_STATE=ON);
GO

Step 4: Create a high availability group

You can use SSMS tools and T-SQL. The following takes T-SQL as an example:

Run the following script to create an availability group in主節點. Note that CLUSTER_TYPE = NONE option was chosen because it is installed without a cluster management platform such as Pacemaker or Windows Server Failover Clustering.

If you are installing AlwaysOn AG on Linux, you should select CLUSTER_TYPE = EXTERNAL for Pacemaker:

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

Execute the following command in the slave library to add the slave library to the AG group:

ALTER AVAILABILITY GROUP [ag1] JOIN WITH (CLUSTER_TYPE = NONE);
ALTER AVAILABILITY GROUP [ag1] GRANT CREATE ANY DATABASE;
GO

At this point, the installation of the SQL Server AlwaysOn cluster in the Docker container is complete!

Note: When specifying CLUSTER_TYPE = NONE to create an availability group, the following command must be executed when performing a failover

ALTER AVAILABILITY GROUP [ag1] FORCE_FAILOVER_ALLOW_DATA_LOSS

test

Create a database on the primary database and add it to the availability group 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

Use SSMS to check whether the synchronization status is normal.

Reference Links

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/

This is the end of this article about the implementation of Docker deployment of SQL Server 2019 Always On cluster. For more relevant content about Docker deployment of SQLServer cluster, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!

You may also be interested in: