SQL Server 2019 AlwaysOn Availability Group on Docker Containers

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.

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:

We add Docker’s official GPG key:

We add the Docker repos:

Let’s finally install the Docker engine in the installation step:

We run the following command to verify the Docker installation:

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.

In order not to write sudo every time, we switch with sudo su:

To prepare the Image, we pull the container images of Ubuntu and SQL Server 2019 from the docker hub:

After the images are pulled, let’s list the images in our local:

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.

We run the following code to see running containers:

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):

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:

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:

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.

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.

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.

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:

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.

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.

If docker compose is not installed; You can install it with command apt install docker-compose -y

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.

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:

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:

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:

We run the following script on all nodes to create endpoints for AlwaysOn:

To enable the extended event session of AlwaysOn in all nodes at startup:

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:

We will join secondary nodes to AG:

After joining the secondary nodes, we will create a new database on the primary node and add it to the AG:

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.

Now we can down stack with Docker compose.

See you, stay healthy.

Resources used:

1. https://docs.microsoft.com/en-us/sql/linux/quickstart-install-connect-docker?view=sql-server-ver15
2. https://docs.microsoft.com/en-us/sql/linux/quickstart-install-connect-ubuntu?view=sql-server-ver15
3. https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-create-availability-group?view=sql-server-ver15
4. https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-configure-mssql-conf?view=sql-server-ver15
5. https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-configure-environment-variables?view=sql-server-ver15
6. https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-availability-group-cluster-ubuntu?view=sql-server-linux-ver15
7. https://docs.docker.com/engine/install/ubuntu/
8. https://docs.docker.com/compose/