SQL Server 2019 AlwaysOn Availability Group on Docker Container Part 1
SQL Server started to support Linux with the 2016 version. Along with the 2017 and 2019 versions, it started 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 Docker Container on 3 nodes and create AlwaysOn Availability Group.
Our goal is to make our environment ready quickly with a single configuration file. Thus, developer or test teams can quickly perform tests such as compatibility, connectivity, code functional.
In this section, we will first prepare an image based on Ubuntu to be able to install Availability Group on the container. We will then perform the necessary installations.
Important: It is not recommended to perform operations in a production environment. The installations were performed on Ubuntu 18.04.
You can find detailed information about Docker installation here.
1 2 3 4 5 6 | $ lsb_release -a No LSB modules are available. Distributor ID: Ubuntu Description: Ubuntu 18.04.4 LTS Release: 18.04 Codename: bionic |
If there is a docker installation from the previous version, it is recommended to remove it.
Next we need to install the packages required for docker installation:
1 2 3 4 5 6 7 8 | $ sudo apt-get remove docker docker-engine docker.io containerd runc $ sudo apt-get update $ sudo apt-get install \ apt-transport-https \ ca-certificates \ curl \ gnupg-agent \ software-properties-common |
We add Docker’s official GPG key:
1 | $ curl -fsSL https://download.docker.com/linux/ubuntu/gpg | sudo apt-key add - |
We add the Docker repos:
1 2 3 4 | $ sudo add-apt-repository \ "deb [arch=amd64] https://download.docker.com/linux/ubuntu \ $(lsb_release -cs) \ stable" |
Let’s finally install the Docker engine in the installation step:
1 2 | $ sudo apt-get update $ sudo apt-get install docker-ce docker-ce-cli containerd.io |
We run the following command to verify the Docker installation:
1 | $ sudo docker run hello-world |
The output should be as follows. There is a warning that starts with “Unable to…”. It is normal to see this warning because there is no image in the local and it pulls the image from the docker hub.
1 2 3 4 5 6 7 8 9 10 11 | Unable to find image 'hello-world:latest' locally latest: Pulling from library/hello-world 0e03bdcc26d7: Pull complete Digest: sha256:6a65f928fb91fcfbc963f7aa6d57c8eeb426ad9a20c7ee045538ef34847f44f1 Status: Downloaded newer image for hello-world:latest Hello from Docker! This message shows that your installation appears to be working correctly. .. To try something more ambitious, you can run an Ubuntu container with: $ docker run -it ubuntu bash |
In order not to write sudo every time, we switch with sudo su:
1 | $ sudo su |
To prepare the Image, we pull the container images of Ubuntu and SQL Server 2019 from the docker hub:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | $ docker pull ubuntu:18.04 18.04: Pulling from library/ubuntu 23884877105a: Pull complete bc38caa0f5b9: Pull complete 2910811b6c42: Pull complete 36505266dcc6: Pull complete Digest: sha256:3235326357dfb65f1781dbc4df3b834546d8bf914e82cce58e6e6b676e23ce8f Status: Downloaded newer image for ubuntu:18.04 docker.io/library/ubuntu:18.04 $ docker pull mcr.microsoft.com/mssql/server:2019-latest 2019-latest: Pulling from mssql/server 5b7339215d1d: Pull complete 14ca88e9f672: Pull complete .. 9a00f22bcd90: Pull complete 1a5a58ce1097: Pull complete Digest: sha256:360f6e6da94fa0c5ec9cbe6e391f411b8d6e26826fe57a39a70a2e9f745afd82 Status: Downloaded newer image for mcr.microsoft.com/mssql/server:2019-latest mcr.microsoft.com/mssql/server:2019-latest |
After the images are pulled, let’s list the images in our local:
1 2 3 4 5 | $ docker images REPOSITORY TAG IMAGE ID CREATED SIZE ubuntu 18.04 c3c304cb4f22 5 weeks ago 64.2MB mcr.microsoft.com/mssql/server 2019-latest d60e9ac97708 2 months ago 1.5GB hello-world latest bf756fb1ae65 5 months ago 13.3kB |
To check, let’s create the SQL Server 2019 container and check the version information. To accept the user agreement and set the SA password, we set the environment variables and map the outer port to the container port.
1 2 3 | $ docker run -e "ACCEPT_EULA=Y" -e "SA_PASSWORD=Str0ngPa$w0rd" \ -p 1501:1433 --name sqldemo \ -d mcr.microsoft.com/mssql/server:2019-latest |
We run the following code to see running containers:
1 2 3 | $ docker ps -a CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES 9c3eceb18f3f mcr.microsoft.com/mssql/server:2019-latest "/opt/mssql/bin/perm…" 36 seconds ago Up 34 seconds 0.0.0.0:1501->1433/tcp sqldemo |
We only see the container we have created, if there are containers you have created for testing purposes, you can see different containers in the list.
We run the following commands to switch to interactive mode and learn the SQL Server version (PRINT was used to get clean output in the terminal):
1 2 3 4 5 6 7 8 | $ docker exec -it sqldemo "bash" mssql@9c3eceb18f3f:/$ /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -Q"PRINT @@VERSION" Password: Microsoft SQL Server 2019 (RTM-CU4) (KB4548597) - 15.0.4033.1 (X64) Mar 14 2020 16:10:35 Copyright (C) 2019 Microsoft Corporation Developer Edition (64-bit) on Linux (Ubuntu 18.04.4 LTS) <X64> mssql@9c3eceb18f3f:/$ exit |
It seems that SQL Server 2019 Developer Edition RTM CU4 has been installed. In this section we can finally list the active containers and stop and remove the ones we do not currently need:
1 2 3 4 5 6 7 | $ docker ps -a CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES 9c3eceb18f3f mcr.microsoft.com/mssql/server:2019-latest "/opt/mssql/bin/perm…" 6 minutes ago Up 6 minutes 0.0.0.0:1501->1433/tcp sqldemo $ docker stop 9c3eceb18f3f 9c3eceb18f3f $ docker rm 9c3eceb18f3f 9c3eceb18f3f |
Additional information: Instead of the entire container id, you can type the first 2 or 3 characters that make it unique.
Example: You can write 9c instead of 9c3eceb18f3f if there is not any other container that starts with 9c.
Again, if you want to list only container ids of active containers, you can use the following command:
1 | $ docker ps -aq |
SQL Server 2019 AlwaysOn Availability Group on Docker Container Part 2
We completed the docker installation in Part 1 and pulled the images to our local.
In this part, we will prepare the Ubuntu 18.04 image we pulled from the docker hub for SQL Server 2019 HA and make it ready for installation. First, let’s create a new directory. We will store our configuration files in this directory.
1 2 3 4 | $ mkdir sql2019ha-demo $ cd sql2019ha-demo $ ls -l total 0 |
In order to build a new container image, we will create a file named dockerfile and add the necessary instructions. You can find detailed information about Dockerfile here.
1 2 | $ touch dockerfile $ vi dockerfile |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | 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 |
Let me explain the keywords and their values, respectively.
FROM: The image, that the image to be created will be based on.
RUN: Command sets to be run during image preparation phase.
EXPOSE: With this command, the port to access the container is determined. So the port that the container will listen to.
ENTRYPOINT: To run commands, services etc. when the container is started.
After our Dockerfile is prepared, we can now build our image and make it ready for use.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 | $ 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 |
In the last two lines, we see that the build process was successful and that the image was successfully created in our local. Let’s see the image we prepared by running the following command:
1 2 3 4 5 6 | $ docker images REPOSITORY TAG IMAGE ID CREATED SIZE sqlag2019 ha b7467618c371 About a minute ago 1.4GB ubuntu 18.04 c3c304cb4f22 5 weeks ago 64.2MB mcr.microsoft.com/mssql/server 2019-latest d60e9ac97708 2 months ago 1.5GB hello-world latest bf756fb1ae65 5 months ago 13.3kB |
Now that the image is ready, we can create the configuration file with docker-compose to get 3 SQL nodes up at once. You can find detailed information about Docker Compose here. Make sure that the Docker Compose configuration file is in the directory you are working in.
1 2 | $ touch docker-compose.yml $ vi docker-compose.yml |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 | version: '3' services: db1: container_name: sqlNode1 image: sqlag2019:ha hostname: sqlNode1 domainname: lab.local environment: SA_PASSWORD: "Str0ngPa$w0rd" 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: "Str0ngPa$w0rd" 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: "Str0ngPa$w0rd" 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 |
To up the stack with Docker Compose, we just need to run the command below. By default, it will find the docker-compose.yml file in the relevant directory and will create and up the servers according to the directives here. With the -d parameter at the end of the command, we determine that we want it to run in detached mode, that is, in the background.
1 2 3 4 5 6 7 8 | $ 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 |
If docker compose is not installed; You can install it with command apt install docker-compose -y
1 2 3 4 5 6 | $ 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 |
The above command displays containers created with docker compose. Now our hosts are up and accessible. For the installation of AlwaysOn AG, we can make the necessary checks by accessing from SSMS or from the terminal.
We run the command below to get the ip address of the host where the docker is located.
1 2 3 4 | $ ifconfig ens33: flags=4163<UP,BROADCAST,RUNNING,MULTICAST> mtu 1500 inet 192.168.72.176 netmask 255.255.255.0 broadcast 192.168.72.255 .. |
We can access our SQL Server nodes via 192.168.72.176 and 1501, 1502 and 1503 ports respectively.
The Linux icon next to the nodes is attracted your attention? 🙂
In the last part, we will complete the AlwaysOn AG installation on Docker Container. In the previous section, we performed the connection test for the containers.
SQL Server 2019 AlwaysOn Availability Group on Docker Container Part 3
By connecting to the primary node, that is, the node with port 1501, we will create login, master key and certificate with the following script.
We extract the certificate and private key to /tmp/dbm_certificate.cer and /tmp/dbm_certificate.pvk files.
We will copy these files to other nodes and create master keys and certificates according to these files:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | 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 |
We will copy the files with cer and pvk extensions from the container to other nodes by running the following commands on the Docker host:
1 2 3 4 5 6 | $ 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/ |
We connect to secondary nodes.(secondary nodes works on ports 1502 and 1503). We create login, master key and certificate by running the following scripts:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | 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 |
We run the following script on all nodes to create endpoints for AlwaysOn:
1 2 3 4 5 6 7 8 9 10 | 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]; GO |
To enable the extended event session of AlwaysOn in all nodes at startup:
1 2 | ALTER EVENT SESSION AlwaysOn_health ON SERVER WITH (STARTUP_STATE=ON); GO |
We run the script below to create a availability group in the primary node. Note that the CLUSTER_TYPE = NONE option was chosen because it was installed without a cluster management platform like Pacemaker or Windows Server Failover Cluster.
If you are going to install AlwaysOn AG on Linux, you should choose CLUSTER_TYPE = EXTERNAL for Pacemaker:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 | 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 |
We will join secondary nodes to AG:
1 2 3 | ALTER AVAILABILITY GROUP [ag1] JOIN WITH (CLUSTER_TYPE = NONE); ALTER AVAILABILITY GROUP [ag1] GRANT CREATE ANY DATABASE; GO |
After joining the secondary nodes, we will create a new database on the primary node and add it to the AG:
1 2 3 4 5 6 7 8 | 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 |
Finally, we check the status of the AG on the AlwaysOn Availability Groups Dashboard:
Thus, we have completed the Always On availability group installation on Docker Container.
As additional information; When you install with “CLUSTER_TYPE = NONE”, if you want to perform failover, you need to make a controlled failover with the command below. But first you should check that the last commit time of the secondary node you want to perform failover.
1 | ALTER AVAILABILITY GROUP [ag1] FORCE_FAILOVER_ALLOW_DATA_LOSS |
Now we can down stack with Docker compose.
1 2 3 4 5 6 7 8 | $ docker-compose down Stopping sqlNode1 ... done Stopping sqlNode3 ... done Stopping sqlNode2 ... done Removing sqlNode1 ... done Removing sqlNode3 ... done Removing sqlNode2 ... done Removing network sql2019hademo_internal |
See you, stay healthy.
Resources used:
- 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/
Does this installation method allow for AD authentication? If not, do you know how to accomplish allowing for AD auth with a SQL Server container on a Linux host?
Hi,
AD integration is possible for sql server on linux, but testing is required for docker and alwayson.
Did you tried this solution with always on availability group listener ,please share. Thanks
Hi,
Thanks for awesome article.
I want add Availability Group Listener to this configuration,
Please Help me.
as fas as I know you can not add listener due to cluster type none
hi,
thanks for the help with this article.
can you tell how we connecting to the database through always-on LISTENER?