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:
- Docker Desktop (running with WSL 2 on Windows)
Install Docker Desktop on Windows | Docker Docs - Docker Compose
Install Compose standalone | Docker Docs - 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 runsentrypoint.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
anddb2
): Each service is a SQL Server instance configured for Always On. - Environment Variables:
SA_PASSWORD
,ACCEPT_EULA
, andMSSQL_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 ports2500
and2600
. Connect tolocalhost,2500
orlocalhost,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
oraoag_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.sql
-- 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.sql
-- 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:
- Open a terminal or PowerShell prompt in the folder containing
docker-compose.yml
. - Run:
docker-compose up --build
This command will build the images as defined in the Dockerfile and spin up the two containers (
db1
anddb2
). - Once both containers start, verify the logs. Look for confirmation messages from
dbinit.sh
indicating that the AOAG scripts have run successfully. - 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.