Introduction

Containers continue to revolutionize enterprise architectures. Although there are many benefits of using this technology, many DBAs shy away from it. Perhaps because it’s a foreign technology, or simply because they don’t understand its applications.

When working as a DBA,  I felt the frustration of application teams when I told them they would need to submit N requests and wait a month or longer to provision a PoC environment. Among many other things, containers allow application teams to setup PoC environments in minutes. DBAs, too, can setup environments in minutes to test the behavior of SQL Server and/or test a script they need to execute across their enterprise fleet.

There are several articles and examples online on how to setup a docker containers running SQL Server. Microsoft themselves have plenty of instructions and guides on how to do so. However, there’s not an abundance of instructions on how configure SQL Server AlwaysOn in Docker. In this article, I will demonstrate how a SQL Server clusterless Availability Group can quickly be configured as a Docker application using docker-compose.

Note: We need to create a clusterless Availability Group because we don’t have a traditional WSFC or Pacemaker to provides us with the heartbeat necessary to create a traditional Availability Group. This means automatic failover will not work on our environment. For more information on this, please read Clusterless Availability Groups.

Installing Docker

This article is not meant to walk you through the installation of Docker. Here is a link with instructions on how to setup docker on your workstation: setting up docker.

Configuring a SQL Server Docker Image

One of the great features of Docker is that you can create a custom image based on an previously existing Docker image. In this example, we are creating a custom SQL Server image with the Always On feature enabled based on the Microsoft’s SQL Server 2019 CU4 RHEL 8 Docker image.

FROM mcr.microsoft.com/mssql/rhel/server:2019-CU4-rhel-8
COPY . /
USER root
RUN chmod +x db-init.sh
RUN /opt/mssql/bin/mssql-conf set sqlagent.enabled true
RUN /opt/mssql/bin/mssql-conf set hadr.hadrenabled  1
RUN /opt/mssql/bin/mssql-conf set memory.memorylimitmb 2048
CMD /bin/bash ./entrypoint.sh

Breaking down the dockerfile

In the first line of the dockerfile, we are specifying the source docker image for our build. In our case, Microsoft’s SQL Server 2019 CU4 RHEL 8.

FROM mcr.microsoft.com/mssql/rhel/server:2019-CU4-rhel-8

The copy command instructs the build to copy all files in the current directory into the root directory of the docker image.

COPY . /

We then need to assign execute permissions to the shell script db-init.sh. This will allow us to execute this script later on when containers are started. Note the line in which we specify the user to be root. When not used, we may encounter permissions issues when attempting to run the chmod command.

USER root
RUN chmod +x db-init.sh

We then run the mssql-conf tool to configure SQL Server. For more configuration information, please review mssql-conf.

Note that the RUN command in the dockerfile executes only at the image's build time, rather than every time a container starts.

RUN /opt/mssql/bin/mssql-conf set sqlagent.enabled true
RUN /opt/mssql/bin/mssql-conf set hadr.hadrenabled  1
RUN /opt/mssql/bin/mssql-conf set memory.memorylimitmb 2048

Finally, we define the entrypoint script. This script is executed every time a docker container is started or re-started. It will eventually invoke the shell script db-init.sh mentioned above.

CMD /bin/bash ./entrypoint.sh

Click here for more detailed information on the dockerfile anatomy.

Docker Compose

Docker compose is a tool which allows us to create multi-docker container applications. In this example, we will be creating a two-node availability group application. More detailed information about docker compose can be found here.

Folder Structure

Under a folder of your choosing, create a new folder named "sql". In this folder, we will create several scripts to configure our environment (more to come below).

To start off, create a file named "dockerfile" (note that this file must not have any extension). Copy and paste the content of the dockerfile in the previous section into this file.

(your folder)
¦ sql/
¦ sql/dockerfile
¦ sql/(other files)
+ docker-compose.yml

docker-compose.yml

The docker-compose file defines the application. In this case, it defines the two docker containers we will use as the nodes for our Availability Group.

Copy and paste the content below into the the docker-compose.yml file.

version: "3.8"
services:
    db1:
        build: ./sql
        environment:
            SA_PASSWORD: "MssqlPass123"
            ACCEPT_EULA: "Y"
            MSSQL_AGENT_ENABLED: "true"
            INIT_SCRIPT: "aoag_primary.sql"
            INIT_WAIT: 30
        ports:
            - "2500:1433"
        container_name: db1
        hostname: db1
        volumes:
            - mssql-server-shared:/var/opt/mssql/shared
            - mssql-server-backup:/var/opt/mssql/backup
        networks:
            - sqlaoag
    db2:
        build: ./sql
        environment:
            SA_PASSWORD: "MssqlPass123"
            ACCEPT_EULA: "Y"
            MSSQL_AGENT_ENABLED: "true"
            INIT_SCRIPT: "aoag_secondary.sql"
            INIT_WAIT: 50
        ports:
            - "2600:1433"
        container_name: db2
        hostname: db2
        volumes:
            - mssql-server-shared:/var/opt/mssql/shared
            - mssql-server-backup:/var/opt/mssql/backup
        networks:
            - sqlaoag
volumes:
    mssql-server-shared:
    mssql-server-backup:
networks:
    sqlaoag:

Breaking down the docker-compose.yml

"db1" and "db2" are the name of the services to be created by docker compose. In our case, each service will be a SQL Server node.

For the build parameter, we set it to the directory "sql" we created above. This will be the context of each container's build.

…
    db1:
        build: ./sql
  …
    db2:
        build: ./sql
   …

The environment parameters section correspond to the environment variables which will be passed to the docker image. The Microsoft’s SQL Server image has three environment variables:

• SA_PASSWORD
• ACCESS_EULA
• MSSQL_AGENT_ENABLED

More information on these can be found here.

In our docker image, we are adding the variables "INIT_SCRIPT" and "INIT_WAIT". "INIT_SCRIPT" corresponds to the T-SQL script we will be executing to configure SQL Server. Keeping in mind that one container will be our primary node and the other container will be the secondary node, we know the T-SQL script executed against each of these SQL Server instances to configure the Availability Group will be different.

We also introduce the variable "INIT_WAIT". This is the amount of time to wait before we start running the configuration script. When we start the docker container we need allow some time for the SQL Server service to start before executing our scripts.  Furthermore, the script on the secondary node must be executed after the Availability Group has been created on the primary node. This wait time, allows us to control the sequence of events.

…
    db1:
        …
        environment:
            SA_PASSWORD: "MssqlPass123"
            ACCEPT_EULA: "Y"
            MSSQL_AGENT_ENABLED: "true"
            INIT_SCRIPT: "aoag_primary.sql"
            INIT_WAIT: 30
        …
    db2:
        build: ./sql
        environment:
            SA_PASSWORD: "MssqlPass123"
            ACCEPT_EULA: "Y"
            MSSQL_AGENT_ENABLED: "true"
            INIT_SCRIPT: "aoag_secondary.sql"
            INIT_WAIT: 50
       …

The ports parameter defines the container ports to expose to the host system (your computer). This parameter follows the format of <host_port>:<container_port>. In the example below the port 2500 on the host maps to the container db1's port 1433. The same for service db2, where the port 2600 maps to the container's port 1433.

This means that from your host, you will be able to connect to these SQL Server instances by connecting to "localhost,2500" for db1 and "localhost,2600" for db2.

These ports can be changed at your discretion.

…
    db1:
…
        ports:
            - "2500:1433"
…
    db2:
…
        ports:
            - "2600:1433"
…

The parameters container_name and hostname are self-explanatory. They define the the container name attributed to the container instance running on the host and the hostname inside of the container, respectively.

…
    db1:
        …
        container_name: db1
        hostname: db1
        …

In the section below, we create a docker network dedicated for this application. We also attach the volumes "mssql-server-shared" and "mssql-server-backup" to both containers. The volume "mssql-server-backup" is not required, but it is there to provide a shared backup drive between the two containers.

On the other hand, the volume "mssql-server-shared" is required. This will be used by the T-SQL scripts to stage a certificate used to create the Availability Group.

We also defined a network named "sqlaoag" for this application. If not done, the containers will be created on the default docker network.

version: "3.8"
services:
    db1:
        …
        volumes:
            - mssql-server-shared:/var/opt/mssql/shared
            - mssql-server-backup:/var/opt/mssql/backup
        networks:
            - sqlaoag
    db2:
        …
        volumes:
            - mssql-server-shared:/var/opt/mssql/shared
            - mssql-server-backup:/var/opt/mssql/backup
        networks:
            - sqlaoag
volumes:
    mssql-server-shared:
    mssql-server-backup:
networks:
    sqlaoag:

Always On Configuration Scripts

In the previous section we introduced the build directory "sql". In this section, I will describe what the contents of this folder are. The directory structure should be as shown below. Note that we have already reviewed the dockerfile's content.

(your folder)
¦ sql/
¦ sql/aoag_primary.sql
¦ sql/aoag_secondary.sql
¦ sql/db-init.sh
¦ sql/entrypoint.sh
+ sql/dockerfile

entrypoint.sh

The entrypoint.sh script is the script which will be executed every time the docker container starts (or restarts). This script performs three steps:

1. Configure the default backup directory – this configuration could not be added ion the docker file because at build time this directory does not exist
2. It invokes the db-init.sh script
3. It starts the sqlservr service. It is important that this is the last step. Docker containers need a non-terminating execution to keep the container alive

#Set the defaultbackupdir (needs to be done here after the volume from docker-compose has been mapped)
#run db-init.sh script
#run sqlservr service so docker container does not stop
/opt/mssql/bin/mssql-conf set filelocation.defaultbackupdir /var/opt/mssql/backup &
sh ./db-init.sh &
/opt/mssql/bin/sqlservr

db-init.sh

This script uses SQLCMD to authenticate to the SQL Server instance and executes the Availability Group configuration scripts. Notice the sleep/wait at the beginning of the script. This is required because when the docker container starts we need to allow for some time for SQL Server to come up.

#wait for the SQL Server to come up
SLEEP_TIME=$INIT_WAIT
SQL_SCRIPT=$INIT_SCRIPT
echo "sleeping for ${SLEEP_TIME} seconds ..."
sleep ${SLEEP_TIME}
echo "#######    running set up script ${SQL_SCRIPT}   #######"
#run the setup script to create the DB and the schema in the DB
#if this is the primary node, remove the certificate files.
#if docker containers are stopped, but volumes are not removed, this certificate will be persisted
if [ "$SQL_SCRIPT" = "aoag_primary.sql" ]
then
    rm /var/opt/mssql/shared/aoag_certificate.key 2> /dev/null
    rm /var/opt/mssql/shared/aoag_certificate.cert 2> /dev/null
fi
#use the SA password from the environment variable
/opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P $SA_PASSWORD -d master -i $SQL_SCRIPT
echo "#######      AOAG script execution completed     #######"

aoag_primary.sql & aoag_secondary.sql

These are the T-SQL scripts used to actually create and configure the Availability Group (AG). The aoag_primary script creates a sample database named SALES, the AG, as well as all its required components (such as the HADR endpoint). The aoag_secondary script creates the required components and it joins the node to the Availability Group.

-- AOAG_PRIMARY
--create sample database
USE [master]
GO
CREATE DATABASE Sales
GO
USE [SALES]
GO
CREATE TABLE CUSTOMER([CustomerID] [int] NOT NULL, [SalesAmount] [decimal] NOT NULL)
GO
INSERT INTO CUSTOMER (CustomerID, SalesAmount) VALUES (1,100),(2,200),(3,300)
--change recovery model and take full backup for db to meet requirements of AOAG
ALTER DATABASE [SALES] SET RECOVERY FULL ;
GO
BACKUP DATABASE [Sales] TO  DISK = N'/var/opt/mssql/backup/Sales.bak' WITH NOFORMAT, NOINIT,  NAME = N'Sales-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO
USE [master]
GO
--create logins for aoag
-- this password could also be originate from an environemnt variable passed in to this script through SQLCMD
CREATE LOGIN aoag_login WITH PASSWORD = 'Pa$$w0rd';
CREATE USER aoag_user FOR LOGIN aoag_login;
-- create certificate for AOAG
-- this password could also be originate from an environemnt variable passed in to this script through SQLCMD
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Pa$$w0rd';
GO
CREATE CERTIFICATE aoag_certificate WITH SUBJECT = 'aoag_certificate';
BACKUP CERTIFICATE aoag_certificate
TO FILE = '/var/opt/mssql/shared/aoag_certificate.cert'
WITH PRIVATE KEY (
        FILE = '/var/opt/mssql/shared/aoag_certificate.key',
        ENCRYPTION BY PASSWORD = 'Pa$$w0rd'
    );
GO
-- create HADR endpoint on port 5022
CREATE ENDPOINT [Hadr_endpoint]
STATE=STARTED
AS TCP (
    LISTENER_PORT = 5022,
    LISTENER_IP = ALL
)
FOR DATA_MIRRORING (
    ROLE = ALL,
    AUTHENTICATION = CERTIFICATE aoag_certificate,
    ENCRYPTION = REQUIRED ALGORITHM AES
)
GRANT CONNECT ON ENDPOINT::Hadr_endpoint TO [aoag_login];
GO

---------------------------------------------------------------------------------------------
--CREATE PRIMARY AG GROUP ON PRIMARY CLUSTER PRIMARY REPLICA
---------------------------------------------------------------------------------------------
--for clusterless AOAG the failover mode always needs to be manual
DECLARE @cmd AS NVARCHAR(MAX)
SET @cmd ='
CREATE AVAILABILITY GROUP [AG1]
WITH (
    CLUSTER_TYPE = NONE
)
FOR REPLICA ON
N''<SQLInstanceName>'' WITH
(
    ENDPOINT_URL = N''tcp://<SQLInstanceName>:5022'',
    AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
    SEEDING_MODE = AUTOMATIC,
    FAILOVER_MODE = MANUAL,
    SECONDARY_ROLE (ALLOW_CONNECTIONS = ALL)
),
N''db2'' WITH
(
    ENDPOINT_URL = N''tcp://db2:5022'',
    AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
    SEEDING_MODE = AUTOMATIC,
    FAILOVER_MODE = MANUAL,
    SECONDARY_ROLE (ALLOW_CONNECTIONS = ALL)
);
';
--replace local server name into the script above
DECLARE @create_ag AS nvarchar(max)
SELECT @create_ag = REPLACE(@cmd,'<SQLInstanceName>',@@SERVERNAME)
--execute creation of AOAG
exec sp_executesql @create_ag
--wait a bit and add database to AG
USE [master]
GO
WAITFOR DELAY '00:00:10'
ALTER AVAILABILITY GROUP [AG1] ADD DATABASE [SALES]
GO

--AOAG_SECONDARY
USE [master]
GO
--create login for aoag
-- this password could also be originate from an environemnt variable passed in to this script through SQLCMD
-- it should however, match the password from the primary script
CREATE LOGIN aoag_login WITH PASSWORD = 'Pa$$w0rd';
CREATE USER aoag_user FOR LOGIN aoag_login;
-- create certificate
-- this time, create the certificate using the certificate file created in the primary node
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Pa$$w0rd';
GO
-- this password could also be originate from an environemnt variable passed in to this script through SQLCMD
-- it should however, match the password from the primary script
CREATE CERTIFICATE aoag_certificate
    AUTHORIZATION aoag_user
    FROM FILE = '/var/opt/mssql/shared/aoag_certificate.cert'
    WITH PRIVATE KEY (
    FILE = '/var/opt/mssql/shared/aoag_certificate.key',
    DECRYPTION BY PASSWORD = 'Pa$$w0rd'
)
GO
--create HADR endpoint
CREATE ENDPOINT [Hadr_endpoint]
STATE=STARTED
AS TCP (
    LISTENER_PORT = 5022,
    LISTENER_IP = ALL
)
FOR DATA_MIRRORING (
    ROLE = ALL,
    AUTHENTICATION = CERTIFICATE aoag_certificate,
    ENCRYPTION = REQUIRED ALGORITHM AES
)
GRANT CONNECT ON ENDPOINT::Hadr_endpoint TO [aoag_login];
GO
--add current node to the availability group
ALTER AVAILABILITY GROUP [AG1] JOIN WITH (CLUSTER_TYPE = NONE)
ALTER AVAILABILITY GROUP [AG1] GRANT CREATE ANY DATABASE
GO

Starting the Application

In one of the sections above we explained the anatomy of docker-compose.yml file. Here, I explain how we can run the application. To start the application use a terminal such as Command Prompt or PowerShell (or shell if on Linux). Navigate to your folder and run the command docker-compose up.

This command will read the local docker-compose.yml file, download the required docker image and build our custom SQL image. Once completed (it takes about 2 minutes, longer if the base image needs to be downloaded), you should be able to authenticate to SQL Server using SSMS or SQLCMD from your computer and access the environment we created.

When the work is completed, you may bring down your environment by running the command docker-compose down.

Download the scripts

You may download the scripts from my GitHub repo here.