How to Create a Pre-initialized SQL Server Docker Image

Introduction

For example, in testing, isn't it a common requirement to want a database with test data already prepared from the start?

I wanted to create such an image for SQL Server, but the officially introduced method is not

How to create a pre-initialized SQL Server image

but rather

How to create a SQL Server image that initializes on startup

and it is slightly different from what I was looking for.

Since it runs repeatedly during tests, I want it to be initialized from the start so it can be used immediately. For example, initializing the SQL Server sample DB "AdventureWorks" takes about 30 seconds, so I want to reduce this to as close to 0 as possible.

Therefore, in this article, I would like to explain how to achieve that.

Overview

Create a pre-initialized DB file using a multi-stage build and copy it into the image.

At startup, only attach the pre-initialized DB file.

I have published a working example below for your reference.

• https://github.com/nuitsjp/docker-adventureworks

Dockerfile

FROM mcr.microsoft.com/mssql/server:2022-latest as Builder
WORKDIR /work/
COPY build/ .
RUN /bin/bash ./setup.sh


FROM mcr.microsoft.com/mssql/server:2022-latest
WORKDIR /var/opt/mssql/data/
COPY --from=Builder /var/opt/mssql/data/AdventureWorks.mdf .
COPY --from=Builder /var/opt/mssql/data/AdventureWorks_log.ldf .
COPY run/ /opt/adventureworks

ENTRYPOINT ["/bin/bash", "/opt/adventureworks/entrypoint.sh"]

Roughly speaking, the final base image "mcr.microsoft.com/mssql/server:2022-latest" is started twice: once to create the DB file, and a second time to copy the DB file and turn it into the final image.

DB Initialization

setup.sh

#start SQL Server, start the script to create the DB and import the data, start the app
export ACCEPT_EULA=Y
export SA_PASSWORD=P@ssw0rd!
/opt/mssql/bin/sqlservr &

#run the setup script to create the DB and the schema in the DB
#do this in a loop because the timing for when the SQL instance is ready is indeterminate
for i in {1..50};
do
    /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P P@ssw0rd! -d master -i ./instawdb.sql
    if [ $? -eq 0 ]
    then
        echo "instawdb.sql completed"
        break
    else
        echo "not ready yet..."
        sleep 1
    fi
done

It is not doing anything particularly difficult.

First, start SQL Server.

/opt/mssql/bin/sqlservr &

After that, it uses sqlcmd within a for loop with instawdb.sql to create the DB and import the initial data. instawdb.sql is a query provided by AdventureWorks; in practice, you would use any initialization query you need.

The for loop is used to retry until successful, as an error will occur until SQL Server has finished starting up.

Image Startup Process

The entry point is specified as a shell script like this:

ENTRYPOINT ["/bin/bash", "/opt/adventureworks/entrypoint.sh"]

The contents are as follows:

#!/bin/bash

/opt/adventureworks/setup-database.sh & /opt/mssql/bin/sqlservr

It executes setup-database.sh and starts SQL Server. Please note that the SQL Server startup must be written on the right side; otherwise, the container will stop after setup-database.sh finishes.

The contents of setup-database.sh are as follows:

#run the setup script to create the DB and the schema in the DB
#do this in a loop because the timing for when the SQL instance is ready is indeterminate
for i in {1..50};
do
    /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P $SA_PASSWORD -d master -i /opt/adventureworks/setup-database.sql
    if [ $? -eq 0 ]
    then
        echo "setup-database.sql completed"
        break
    else
        echo "not ready yet..."
        sleep 1
    fi
done

It executes setup-database.sql using sqlcmd.

And the contents of setup-database.sql are as follows:

USE [master]
GO

EXEC sp_attach_db [AdventureWorks], N'/var/opt/mssql/data/AdventureWorks.mdf', N'/var/opt/mssql/data/AdventureWorks_log.ldf'
GO

It is just attaching the pre-initialized DB file. This allows for a quick startup.

Extra

The reason I arrived at this workflow is that when I tried to create an image by starting SQL Server and initializing it without using multi-stage builds, it didn't work correctly and I couldn't connect to the DB.

I suspect this is probably because it wasn't shut down properly...

1. The shutdown process for the Linux version of SQL Server did not work in the Docker version.
2. The sqlservr command in Docker does not have a shutdown option, at least not in the help or documentation.

I felt like it might be possible by exploring undocumented areas, but I gave up on that approach because I didn't want to use something that might stop working at any time.

So, is the DB file created this way healthy? I judged it would be fine because it involves stopping the DB, performing a detach process, and then copying and attaching it, which is a legitimate method recognized even on Docker.

That's all!