Testing and Local Development with MSSQL (2 of 2)

Welcome to the second installment of this two-part blog series on creating robust automated testing and local development solutions for MSSQL. If you haven’t read the first post yet, please do so before proceeding (Testing and Local Development with MSSQL (1 of 2)) as this post builds upon concepts introduced there.

This post aims to guide the reader through creating a custom MSSQL container image with an embedded database and schema. While the process outlined in the first post works well, it’s not efficient. Each container initialization requires connecting to the target database, generating a DDL script, and executing the script inside an MSSQL container. Each local development machine and build server need the same container, and there is no need for each of them to duplicate the initialization process. Shifting those responsibilities to an image build step eliminates the repetition. As a bonus, using a base container image obviates the need for every entity using the container to have access to the target database.

The time-constrained can jump directly to the TL;DR.

Generating DDL Scripts

Part one of this series outlined an mssql-scripter container image responsible for generating a DDL script from the target database. Because we are embedding the schema into a custom image, there is no need to do this separately. A Docker multi-stage build is a better option. The Dockerfile depicted below is the first section of a custom MSSQL image.

# Create build stage named scripter
FROM python:3.9-slim AS scripter

WORKDIR /usr/src/app

# Install requisite apt packages
RUN echo "deb http://security.debian.org/debian-security jessie/updates main" >> /etc/apt/sources.list
RUN apt-get update && \ 
    apt-get install -y libicu67 libssl1.0.0 libffi-dev libunwind8 python3-dev

# Install mssql-scripter
RUN pip install --upgrade pip && \
    pip install mssql-scripter

# Get the connection string from the build command
ARG CONNECT_STRING

# Generate the DDL script and place it in a file names schema.sql
RUN mssql-scripter \
    --connection-string="$CONNECT_STRING" \
    -f ./schema.sql

################################################################################
FROM mcr.microsoft.com/mssql/server:2019-latest
# ...
COPY --from=scripter '/usr/src/app/schema.sql' ./
# ...
# See the next section for the remainder of the code
# ...

The first build stage, dubbed scripter, is nearly identical to the previous mssql-scripter image with two exceptions: the ARG CONNECT_STRING and RUN mssql-scripter... instructions.

The ARG instruction accepts a connection string from the build command (docker build --build-arg CONNECT_STRING="<YOUR_CONNECTION_STRING>"...). It’s essential to understand how this works because misuse could present two separate security issues. First, the build process prints the connection string to stdout, which logging applications could record¹. Second, even though docker discards build arguments after use, they are still retrievable via the docker history command. Luckily, the multi-part build discards scripter, so it shouldn’t be cause for concern. Just don’t attempt to use scripter as a stand-alone image.

The RUN mssql-scripter... instruction retrieves the DDL script from the target database and saves it in a file named schema.sql for the subsequent build stage to copy. Building the partial image exactly as shown above generates an MSSQL image with an embedded DDL script. To verify this, save the text as Dockerfile and use the following commands to print the contents of the DDL script.

# Build the container image
docker build \
    --build-arg CONNECT_STRING='<YOUR_CONNECTION_STRING>' \
    -t sql-test \
      --progress plain \
    ./

# Run the container
docker run \
    -e 'ACCEPT_EULA=Y' \
    -e SA_PASSWORD=<YOUR_STRONG_PASWORD> \
    --name sql-file-test \
    -p 1433:1433 \
    --rm \
    -d \
    sql-test

# Print the DDL script to stdout
docker exec sql-file-test cat /usr/src/app/schema.sql

# Destroy the container
docker stop sql-file-test

The next step is to invoke the DDL script.

Executing DDL Scripts

Executing the DDL script during image build is problematic. sqlcmd needs an initialized instance to run scripts against; however, the SQL Server process (sqlservr) doesn’t start until after container initialization. Staring sqlservr compounds the difficulty because it has to run as a foreground process, and the build won’t terminate while it’s running. It’s the proverbial “chicken or egg” problem. The solution is to launch a background process that will wait for sqlservr to initialize, invoke the DDL script, and terminate sqlservr. After initializing the background process, it’s safe to start sqlservr. The image below illustrates the concept.

The script below defines the background process depicted in the image above. Please save it to a file named invoke-schema.sql. The Dockerfile explained further down utilizes it during the build. Notice that it depends on an environment variable: $SA_PASSWORD. Recall that the previous post specifies said variable using a run command parameter. Run parameters aren’t available during image build, so a build parameter is the only option. The result is a container image with an embedded password². If your context prohibits this, it’s possible to change the password using sqlcmd³.

#!/bin/bash

# Wait for SQL Server to start
while ! </dev/tcp/localhost/1433 2>/dev/null; do
    sleep 2s
done

# Give SQL Server a few seconds to situate itself
sleep 5s

# Run the DDL script to create the DB and the schema
/opt/mssql-tools/bin/sqlcmd -I -S localhost -U sa -P $SA_PASSWORD -d master -i ./schema.sql

# Kill the SQL server process so the docker build will exit
pkill sqlservr

The remainder of the Dockerfile started in the previous section is depicted below. Take some time to study it in tandem with the image above, then create a Dockerfile.

FROM python:3.9-slim AS scripter
# ...
# See the previous section for the scripter build stage code
# ... 
################################################################################
FROM mcr.microsoft.com/mssql/server:2019-latest 

# Required to do anything interesting inside mssql containers
USER root

# Create a working dicrectory
RUN mkdir -p /usr/src/app
WORKDIR /usr/src/app

# Get the schema.sql file from the scripter build stage
COPY --from=scripter '/usr/src/app/schema.sql' ./

# Copy the script responsible for invoking schema.sql
COPY ./invoke-schema.sh ./

# Get the SA password from the build command
ARG SA_PASSWORD

# Set requisite env variables
ENV ACCEPT_EULA Y
ENV SA_PASSWORD=$SA_PASSWORD

# # Run invoke-schema.sh in the backgound and start SQL Server
RUN /usr/src/app/invoke-schema.sh & /opt/mssql/bin/sqlservr

# Remove the initalization files
RUN rm ./schema.sql ./invoke-schema.sh

The last task is to build the image and run the container using the shell commands below.

docker build \
    --build-arg SA_PASSWORD='<YOUR_STRONG_PASSWORD>' \
    --build-arg CONNECT_STRING="<YOUR_CONNECTION_STRING>" \
    -t sql-test \
    ./

docker run -p 1433:1433 --rm -d sql-test

Although outlined previously, the two build-arg parameters are worth recapitulation. The multi-stage build jettisons the CONNECT_STRING parameter after use; however, not before printing it to stdout where it’s susceptible to logging. The SA_PASSWORD parameter doesn’t print; however, it becomes embedded in the image. There is no cause for concern, assuming typical use cases. However, please evaluate your scenario before adopting this technique.

Conclusion

This post outlined all the tools necessary to use MSSQL for local development and automated test pipelines efficiently. Embedding the target database and associated schema into a custom image dramatically reduces container initialization times.

TL;DR

Embedding a database and schema directly into a custom MSSQL image dramatically decreases container initialization time. See the steps below.

#!/bin/bash

# Wait for SQL Server to start
while ! </dev/tcp/localhost/1433 2>/dev/null; do
    sleep 2s
done

# Give SQL Server a few seconds to situate itself
sleep 5s

# Run the DDL script to create the DB and the schema
/opt/mssql-tools/bin/sqlcmd -I -S localhost -U sa -P $SA_PASSWORD -d master -i ./schema.sql

# Kill the SQL server process so the docker build will exit
pkill sqlservr

# Create build stage named scripter 
FROM python:3.9-slim AS scripter

WORKDIR /usr/src/app

# Install requisite apt packages
RUN echo "deb http://security.debian.org/debian-security jessie/updates main" >> /etc/apt/sources.list
RUN apt-get update && \
    apt-get install -y libicu67 libssl1.0.0 libffi-dev libunwind8 python3-dev

# Install mssql-scripter
RUN pip install --upgrade pip && \
    pip install mssql-scripter

# Get the connection string from the build command
ARG CONNECT_STRING

# Generate the DDL script and place it in a file names schema.sql
RUN mssql-scripter \
    --connection-string="$CONNECT_STRING" \
    -f ./schema.sql

################################################################################
FROM mcr.microsoft.com/mssql/server:2019-latest

# Required to do anything interesting inside mssql containers
USER root

# Create a working dicrectory
RUN mkdir -p /usr/src/app
WORKDIR /usr/src/app

# Get the schema.sql file from the scripter build stage
COPY --from=scripter '/usr/src/app/schema.sql' ./

# Copy the script responsible for invoking schema.sql
COPY ./invoke-schema.sh ./

# Get the SA password from the build command
ARG SA_PASSWORD

# Set requisite env variables
ENV ACCEPT_EULA Y
ENV SA_PASSWORD=$SA_PASSWORD

# # Run invoke-schema.sh in the backgound and start SQL Server
RUN /usr/src/app/invoke-schema.sh & /opt/mssql/bin/sqlservr

# Remove the initalization files
RUN rm ./schema.sql ./invoke-schema.sh

docker build \
    --build-arg SA_PASSWORD='<YOUR_STRONG_PASSWORD>' \
    --build-arg CONNECT_STRING="<YOUR_CONNECTION_STRING>" \
    -t sql-test \
    ./

docker run -p 1433:1433 --rm -d sql-test

The result is a container image with an embedded database and associated schema that initializes in seconds.