Dockerizing SQL Server with Pre-Restored Databases

#docker #sqlserver #containers #database

Docker Containers

Docker is a tool for creating lightweight, portable containers that run services in isolated environments.

Creating a Customised SQL Server Docker Image

We’ll create a custom Docker image for SQL Server with a pre-restored database. Preloading the database in the image is useful when you need to run a container with the database for testing, automated testing in CI/CD pipelines, development environments, and any scenario where consistent data and easy database resets are needed.

Prerequisites

Docker Desktop installed
Basic familiarity with Docker and Dockerfiles
SQL Server familiarity

Overview – What We’ll Cover

Create a Database Backup File: Generate a backup file of the SQL Server database.
Write a Dockerfile: Define the Dockerfile to set up the SQL Server environment and configure the database.
Create a Database Restore Script: Add a script to restore the database from the backup file during the build.
Set Up Secrets for the Build: Securely pass the SA_PASSWORD using Docker BuildKit secrets.
Build the Docker Image: Build the Docker image with the pre-restored database.
Run a Container from the Image: Start a container using the custom Docker image with the preloaded database.

Create Database Backup File

We can create a backup file for this DemoDb database in Microsoft SQL Server Management Studio (SSMS).

To do this, right click on the database, select Tasks > Back Up, chose a file location for the backup, and select the ‘Full’ backup type. This process generates a .bak file, which can be used to restore the database in a Docker container.

Note: multiple databases can be preloaded onto a Docker image, but for this example we will just preload this DemoDb database.

Write a Dockerfile

FROM mcr.microsoft.com/mssql/server:2022-latest

# Accept license agreement
ENV ACCEPT_EULA=Y

USER root

# Copy files to container.
COPY DemoDb.bak /var/opt/mssql/DemoDb.bak
COPY pre-restore-db.sh /usr/src/app/pre-restore-db.sh

# Add executable permissions to script.
# Run database restore script with the password accessed as a secret.
# Remove backup files.
RUN --mount=type=secret,id=SA_PASSWORD \
  chmod +x /usr/src/app/pre-restore-db.sh \
  && SA_PASSWORD=$(cat /run/secrets/SA_PASSWORD) /bin/bash /usr/src/app/pre-restore-db.sh \
  && rm /usr/src/app/pre-restore-db.sh /var/opt/mssql/DemoDb.bak

# Ensure SQL Server is started when the container runs.
ENTRYPOINT ["/opt/mssql/bin/sqlservr"]

What this Dockerfile does:

Set up Base Image: Uses the latest SQL Server 2022 image from Microsoft’s container registry.
Environment Variables: Configures SQL Server to accept the license agreement (ACCEPT_EULA=Y).
Run Commands as Root: Switches to the root user to allow administrative tasks during the build.
Copy Files to Container: Copies the SQL backup file and database restore script into the container.
Set Permissions and Restore Database: Grants executable permissions to the restore script and restores the DemoDb database.
- Securely Access Password: Uses RUN --mount=type=secret,id=SA_PASSWORD to mount the SA_PASSWORD as a secret accessible only during the build step. When building the Docker image, we’ll pass the SA password using Docker BuildKit.
- Set SA_PASSWORD Variable: Reads the password securely with SA_PASSWORD=$(cat /run/secrets/SA_PASSWORD), making it available for the restore script without storing it in the final image.
- Clean Up: Removes the restore script and backup file after the database is restored to keep the image lean.
Define Entrypoint: Specifies SQL Server as the main process to start when the container runs.

Create a Database Restore Script

This script, pre-restore-db.sh, is run in the Dockerfile to restore the DemoDb database:

# Start SQL Server in the background
/opt/mssql/bin/sqlservr &

# Wait for SQL Server to come up
sleep 10s

# Restore DemoDb database
/opt/mssql-tools18/bin/sqlcmd -S localhost -U SA -P "$SA_PASSWORD" -C \
  -Q "RESTORE DATABASE DemoDb FROM DISK='/var/opt/mssql/DemoDb.bak' WITH MOVE 'DemoDb' TO '/var/opt/mssql/data/DemoDb.mdf', MOVE 'DemoDb_log' TO '/var/opt/mssql/data/DemoDb_log.ldf'" -C

# Stop SQL Server after the restoration is complete
kill $(pgrep sqlservr)

What this script does:

Starts SQL Server in the background: Runs the SQL Server instance so that the restore command can connect to it.
Waits for SQL Server to initialize: Pauses for a few seconds to ensure SQL Server is fully up and running..
Restores the DemoDb database: Uses the sqlcmd tool to restore the DemoDb database from a .bak file located in the container, with data and log files moved to the appropriate directories.
Stops SQL Server: After the restore process is complete, SQL Server is stopped to finalize the image build.

Setting Up Secrets for the Build

To securely pass the SA_PASSWORD for SQL Server without embedding it in the Dockerfile, we’ll use Docker BuildKit secrets. This method ensures the password is only accessible during the build process and is not stored in the final image layers.

Note: If security is not a primary concern for the password (e.g., it’s a temporary password only for building the Docker image), you could pass the password as an ARG in the Dockerfile.

Create a Secret File: Create a file named sa_password.txt with your SQL Server password.
Enable BuildKit from your terminal:
- For Unix-based environments (Linux, macOS) or Git Bash: Run export DOCKER_BUILDKIT=1
- For Windows PowerShell: Run $env:DOCKER_BUILDKIT = "1"

Build the Docker Image

Open a terminal in the directory containing the Dockerfile, along with the pre-restore-db.sh restore script, the database backup file, and the sa_password.txt file.

Run the following command to build the Docker image, passing the password from sa_password.txt securely:

docker build --secret id=SA_PASSWORD,src=sa_password.txt -t demodb-1.0 .

Replace the argument following ‘-t’ with your image tag. In this case we created the tag demodb-1.0.

We can see the image created with docker image ls:

Run a Container from the Image

Use the docker run command to run the container using the image we just created.
For Unix-based environments (Linux, macOS) or Git Bash:

docker run -e 'ACCEPT_EULA=Y' -e 'MSSQL_SA_PASSWORD=********' \
   --name 'demodb' -p 1401:1433 \
   -d demodb-1.0

For Windows PowerShell:

docker run -e "ACCEPT_EULA=Y" -e "MSSQL_SA_PASSWORD=********" `
   --name "demodb" -p 1401:1433 `
   -d demodb-1.0

What this command does:

Starts a container named 'demodb' with SQL Server accessible on port 1401 on your host machine.
The MSSQL_SA_PASSWORD is passed to set the SA password for SQL Server in the live container (replace the value with your password).

We can see the container running with docker ps:

We can now access the SQL Server running in the container from SSMS:
Start a connection to SQL Server:

Server name: localhost,1401
Authentication: SQL Server Authentication
Login Username: sa
Login Password: password you specified when running the container in the previous step

Once connected, we can see the restored database is there with all the data as expected.