Testing and Local Development with MSSQL (1 of 2)

The most challenging part of designing an effective automated testing strategy is accurately simulating databases. Microsoft’s SQL Server (MSSQL) is particularly irksome because of its large footprint, lengthy startup time, plethora of configuration options and ability to store custom logic (stored procedures, user-defined functions, …). These characteristics make it onerous to materialize production equivalent instances within automated pipelines efficiently. This post is the first in a two-part series demonstrating a technique to assuage said difficulties.

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

MSSQL Docker Image

Microsoft’s relatively recently¹ released MSSQL container images - available for both Windows and Linux - are a giant leap forward for local development and automated testing (see the Docker hub repo for full details). While it is possible to use local installations of MSSQL, the most notable advantage of containers is configuration management. Installing, patching, and maintaining MSSQL installations across an array of build servers and development machines is expensive. Thanks to the magic of containers, it’s easy to construct fully-featured instances with full fidelity across many environments. Try it yourself using the shell command below²:

docker run \
    -e 'ACCEPT_EULA=Y' \
    -e 'SA_PASSWORD=' \
    -p 1433:1433 \
    --rm \
    -d \
    mcr.microsoft.com/mssql/server:2019-latest

Take special note of the two environmental variables specified in the command above. ACCEPT_EULA confirms your acceptance of the End User Licensing Agreement. Omission of this variable results in failure to initialize. SA_PASSWORD is also required - it, as the name suggests, sets the System Administrator (sa) password. The MSSQL Password Policy specifies the minimum complexity requirements. A non-compliant password results in a silent failure.

Next, connect to the containerized instance using your favorite SQL management tool (SQL Server Management Studio (SSMS) shown below).

Finally, destroy the container:

docker stop $(docker ps --filter ancestor=mcr.microsoft.com/mssql/server:2019-latest -q)

MSSQL containers are ideal for local development and automated testing. There are, however, a few obstacles that are worthy of mention. The licensing restrictions differ based on the supplied MSQSL_PID environment variable. The default value is Developer, which is free for non-production use-specifying other values such as Standard or Enterprise subject the container to the same licensing fees as its non-containerized counterpart. Some advanced features such as replication, SQL agent, and full-text indexing aren’t supported by default ⁴. Additionally, the ephemeral nature and performance impediments posed by MSSQL containers may deem them unsuitable for production workloads. There are options to overcome these limitations, but they are outside of the scope of this post.

Given the ability to easily create and destroy instances, the next challenge to overcome is initializing a database and associated schema⁵.

Generating DDL Scripts

To obtain the Data Definition Language (DDL) scripts from the database under test, one could use the SSMS GUI to generate scripts; however, that’s not conducive to automated testing. A better solution is to use mssql-scripter, a command-line utility that duplicates Management Studio’s scripting capabilities.

FROM python:3.9-slim

WORKDIR /usr/src/app

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

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

ENTRYPOINT ["mssql-scripter"]

Build the container image:

docker build -t mssql-scripter .

Next, generate the DDL script (See the complete list of configuration options here: Usage Guide).

docker run --rm mssql-scripter --connection-string="<YOUR_CONNECTION_STRING>" > schema.sql

The shell command above generates a schema.sql file containing all the DDL scripts required to create an identical database complete with all its components (tables, functions, stored procedures, …). The final puzzle piece is to execute the SQL script against an MSSQL instance.

Executing DDL Scripts

Once again, it’s entirely possible to invoke a DDL script using a GUI tool like SSMS; however, this is problematic from an automation perspective. A better option is to use the sqlcmd utility baked into the base image. sqlcmd reads Transact-SQL statements from a command prompt and sends them to an MSSQL instance ⁷. See the usage guide here. Experiment with the commands below to familiarize yourself.

# Create a MSSQL container
docker run \
    -e 'ACCEPT_EULA=Y' \
    -e 'SA_PASSWORD=<YOUR_STRONG_PASSWORD>' \
    --name sql-test \
    -p 1433:1433 \
    --rm \
    -d \
    mcr.microsoft.com/mssql/server:2019-latest
# Execute a SQL query inside the container
docker exec \
    sql-test /opt/mssql-tools/bin/sqlcmd -I -S localhost -U sa -P '<YOUR_STRONG_PASSWORD>' -q 'SELECT * FROM INFORMATION_SCHEMA.TABLES'

Invoking the DDL script is as easy as copying it to a container and sending it sqlcmd as shown below.

# Copy the DDL script created in the previous section to the container
docker cp ./schema.sql sql-test:/schema.sql

# Invoke the script using sqlcmd
docker exec sql-test /opt/mssql-tools/bin/sqlcmd -I -S localhost -U sa -P <YOUR_STRONG_PASSWORD> -d master -i /schema.sql

The result is an MSSQL container populated with a database and associated schema. Additionally, the container has guaranteed fidelity with the target. The tools required for robust automated testing and local development environments with MSSQL are present. As a bonus, installing any software (other than a container platform) is unnecessary ⁸.

Conclusion

MSSQL container images, mssql-scripter, and sqlcmd make it relatively easy to build robust automated testing and local development solutions. For most situations, the process outlined in this post is more than sufficient. The major drawback to this approach is efficiency. Generating and invoking a DDL script is relatively expensive. The next post in this series outlines a process for embedding a schema directly into a container image.

TL;DR

The end-to-end process for creating MSSQL instances for test and local development that are guaranteed to have fidelity with their target environments is as follows:

# Save this as Dockerfile
FROM python:3.9-slim

WORKDIR /usr/src/app 

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

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

ENTRYPOINT ["mssql-scripter"]

docker build -t mssql-scripter .

docker run --rm mssql-scripter --connection-string="<YOUR_CONNECTION_STRING>" > schema.sql

docker run \
    -e 'ACCEPT_EULA=Y' \
    -e 'SA_PASSWORD=<YOUR_STRONG_PASSWORD>' \
    --name sql-test \
    -p 1433:1433 \
    --rm \
    -d \
    mcr.microsoft.com/mssql/server:2019-latest

docker cp ./schema.sql sql-test:/schema.sql

docker exec sql-test /opt/mssql-tools/bin/sqlcmd -I -S localhost -U sa -P <YOUR_STRONG_PASSWORD> -d master -i /schema.sql

While this is sufficient for most applications, generating and invoking a DDL script is relatively expensive. The next post in this series outlines a process for embedding a schema directly into a container image.

1. The first MSSQL docker container was released in 2017. ↩

2. All shell commands in this post were tested using WSL2 with docker desktop; however, they should work with any Linux-based environment with docker installed. ↩

3. The password is available inside the container via an environment variable which could be a security concern for some applications. ↩

4. See https://github.com/Microsoft/mssql-docker/issues/27 and https://schwabencode.com/blog/2019/10/27/MSSQL-Server-2017-Docker-Full-Text-Searchi for more details. ↩

5. Although not explicitly outlined in this post, it’s also possible to use the same tools to extract and load data. It’s not advisable to load all production data; however, it is advantageous to load some amount of test data into a container instance. ↩

6. Microsoft hasn’t done a great job keeping it up-to-date. ↩

7. sqlcmd does much more than this; however, the additional functionality isn’t germane to this post. ↩

8. Operations and Developers alike should be thrilled to know they have fewer applications to install and maintain. ↩