How to Set Up a SQL Server Always On Environment Using Docker Containers

Introduction

For enterprise SQL Server DBAs, high availability is essential for maintaining seamless database operations—particularly in scenarios involving Change Data Capture (CDC) or other mission-critical functionalities. Docker containers can streamline the setup and management of development or testing environments for Always On Availability Groups (AOAG). By bundling configuration files, scripts, and dependencies into containers, teams gain a reproducible, portable, and efficient deployment mechanism.

This guide explains how to build an Always On environment inside Docker containers using Docker Desktop, Docker Compose, and a series of setup scripts. It walks through the creation of two containers (primary and secondary) configured in a clusterless Always On Availability Group scenario.

Prerequisites

Before proceeding, ensure you have the following:

1. Docker Desktop (running with WSL 2 on Windows)
   Install Docker Desktop on Windows | Docker Docs
2. Docker Compose
   Install Compose standalone | Docker Docs
3. Basic Familiarity with SQL Server and Availability Groups
   While this tutorial covers essential T-SQL scripts, some background knowledge of Always On concepts is recommended.

Overview of the Required Files

Your working directory (e.g., AlwaysOnDockerExample/) will include the following files and folders:

AlwaysOnDockerExample/
├── Dockerfile
├── docker-compose.yml
├── entrypoint.sh
├── dbinit.sh
└── sql_scripts/
    ├── aoag_primary.sql
    └── aoag_secondary.sql

Dockerfile: Defines the base image (SQL Server on Linux), installs or configures required software, and sets up environment variables. It also specifies which scripts to copy into the container and the command to run when the container starts.

docker-compose.yml: Orchestrates multiple containers. It describes how your containers should be built, connected, and what environment variables are passed in.

entrypoint.sh: Runs at container startup to finalize any dynamic configuration. Common tasks include setting backup directories, configuring environment variables, or making last-minute updates.

dbinit.sh: Performs the initial database setup. For example, it can run T-SQL scripts to create or update schemas, seed data, or configure high availability settings.

aoag_primary.sql & aoag_secondary.sql: SQL scripts responsible for creating and configuring the Always On Availability Group components. One script creates the primary database and configures the primary node, while the other configures the secondary replica.

Step-by-Step Implementation

Below are the detailed steps to create and run two SQL Server containers that form a clusterless Always On Availability Group.

1. Create the Dockerfile

Create a Dockerfile (e.g., in a sql/ directory) with the following content:

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

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

Key Points:

• Base Image: Uses mcr.microsoft.com/mssql/rhel/server:2019:latest for SQL Server on RHEL.
• Copying Files: The COPY . / command transfers your scripts and configuration files from the local directory to the container’s root directory.
• Script Permissions: RUN chmod +x db-init.sh ensures the script is executable.
• SQL Server Configuration: mssql-conf commands enable SQL Agent, Always On HADR, and set memory limits.
• Container Startup: The CMD instruction runs entrypoint.sh whenever the container starts.

2. Create the docker-compose.yml

In your main project directory, create a docker-compose.yml that references the Dockerfile above (assumed to be in a sql/ subfolder). The following example defines two containers—db1 (primary) and db2 (secondary):

version: "3"

services:
  db1:
    build: ./sql
    environment:
      SA_PASSWORD: ""        # Replace with a strong password
      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: ""        # Replace with a strong password
      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:

Key Points:

• Services (db1 and db2): Each service is a SQL Server instance configured for Always On.
• Environment Variables:
  • SA_PASSWORD, ACCEPT_EULA, and MSSQL_AGENT_ENABLED are required for Microsoft’s SQL Server image.
  • INIT_SCRIPT specifies which SQL file to execute (primary or secondary).
  • INIT_WAIT enforces a startup delay, ensuring SQL Server is ready before running the setup scripts.
• Ports: Maps the container’s port 1433 to host ports 2500 and 2600. Connect to localhost,2500 or localhost,2600 in SSMS or Azure Data Studio.
• Volumes:
  • mssql-server-shared is used to share a certificate file between containers.
  • mssql-server-backup provides a shared location for backups.
• Networks: Assigns both containers to the sqlaoag network for internal communication.

3. Create the Entry Script (entrypoint.sh)

Place the following in entrypoint.sh:

#!/bin/bash

# Set the default backup directory
/opt/mssql/bin/mssql-conf set filelocation.defaultbackupdir /var/opt/mssql/backup &

# Run the db-init.sh script in the background
sh ./db-init.sh &

# Start the SQL Server process (keeps container running)
/opt/mssql/bin/sqlservr

Key Points:

• Backup Directory: Configured after the volume is mounted, since the directory doesn’t exist at build time.
• dbinit.sh Execution: Invokes the database initialization script.
• SQL Server Service: Launches SQL Server and keeps the container alive.

4. Create the Database Initialization Script (dbinit.sh)

In the same folder as the entrypoint.sh, add dbinit.sh:

#!/bin/bash

# Wait for SQL Server to fully start
SLEEP_TIME=$INIT_WAIT
SQL_SCRIPT=$INIT_SCRIPT

echo "Sleeping for ${SLEEP_TIME} seconds..."
sleep ${SLEEP_TIME}

echo "####### Running setup script ${SQL_SCRIPT} #######"

# If this is the primary node, remove any existing certificate files
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

# Execute the SQL script using SQLCMD
/opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P "$SA_PASSWORD" -d master -i "$SQL_SCRIPT"

echo "####### AOAG script execution completed #######"

Key Points:

• Startup Delay: The script waits a specified number of seconds (INIT_WAIT) to ensure the SQL Server process is listening.
• Certificate Cleanup: If the script is for the primary node, it clears any existing certificate files.
• SQLCMD: Executes the respective T-SQL script (aoag_primary.sql or aoag_secondary.sql) to configure AOAG.

5. Create the T-SQL Scripts (aoag_primary.sql and aoag_secondary.sql)

Store these in a sql_scripts/ directory. Below is an example of the Primary script, followed by the Secondary script. They configure a sample Sales database and set up an HADR endpoint and availability group named AG1.

-- AOAG_PRIMARY

-- 1. Create and seed a 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);

-- 2. Switch the database to FULL recovery and take a backup
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

-- 3. Create logins and a certificate for the AOAG
USE [master];
GO

CREATE LOGIN aoag_login WITH PASSWORD = 'Pa$w0rd';
CREATE USER aoag_user FOR LOGIN aoag_login;

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

-- 4. Create an 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

-- 5. Create the Availability Group
DECLARE @cmd AS NVARCHAR(MAX);
SET @cmd = '
CREATE AVAILABILITY GROUP [AG1]
WITH (
    CLUSTER_TYPE = NONE
)
FOR REPLICA ON
N'''' WITH
(
    ENDPOINT_URL = N''tcp://: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)
);';

DECLARE @create_ag AS NVARCHAR(MAX);
SELECT @create_ag = REPLACE(@cmd, '', @@SERVERNAME);

EXEC sp_executesql @create_ag;

-- 6. Add the Sales database to AG after a short delay
WAITFOR DELAY '00:00:10';

ALTER AVAILABILITY GROUP [AG1] ADD DATABASE [Sales];
GO

-- AOAG_SECONDARY

USE [master];
GO

-- 1. Create login and user for AOAG
CREATE LOGIN aoag_login WITH PASSWORD = 'Pa$w0rd';
CREATE USER aoag_user FOR LOGIN aoag_login;

-- 2. Create master key and certificate from primary's backup
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Pa$w0rd';
GO

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

-- 3. Create the HADR endpoint for the secondary
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

-- 4. Join the secondary to the existing availability group
ALTER AVAILABILITY GROUP [AG1] JOIN WITH (CLUSTER_TYPE = NONE);
ALTER AVAILABILITY GROUP [AG1] GRANT CREATE ANY DATABASE;
GO

6. Confirm the File Structure

Your project folder should look similar to this:

(your folder)
├── docker-compose.yml
└── sql/
    ├── Dockerfile
    ├── aoag_primary.sql
    ├── aoag_secondary.sql
    ├── dbinit.sh
    └── entrypoint.sh

Make sure each file is in the correct directory, and that your scripts are executable (e.g., chmod +x entrypoint.sh dbinit.sh).

7. Build and Run the Containers

Follow these steps to build and run the containers:

1. Open a terminal or PowerShell prompt in the folder containing docker-compose.yml.
2. Run:

   docker-compose up --build

   This command will build the images as defined in the Dockerfile and spin up the two containers (db1 and db2).

3. Once both containers start, verify the logs. Look for confirmation messages from dbinit.sh indicating that the AOAG scripts have run successfully.
4. Test connectivity by opening a SQL client (e.g., SQL Server Management Studio or Azure Data Studio) and connecting to:
   • localhost,2500 for the primary container (db1)
   • localhost,2600 for the secondary container (db2)

Conclusion

Deploying a clusterless SQL Server Always On Availability Group with Docker containers offers a streamlined, repeatable, and efficient way to set up high availability for development or testing scenarios. By defining all necessary components—such as database configurations, scripts, and environment settings—in a Dockerfile and docker-compose.yml, you can automate the creation of a consistent environment on any machine. This eliminates the complexity of manual configuration and helps ensure that DBAs, BI developers, and other stakeholders can focus on innovating rather than troubleshooting environment inconsistencies.

Docker’s reproducible containers and Compose’s multi-container orchestration together provide a robust and agile approach to testing high availability solutions. Whether you are exploring new features, simulating disaster recovery scenarios, or maintaining CDC in a resilient architecture, this setup offers a strong foundation for reliable SQL Server deployments.