Microsoft SQL Server is available for Linux so we can run it from Docker container, but usually we need to initialize database at startup, which currently is a bit tricky.

If you have seen my post which explains Docker compose, then you know that MySQL team has prepared very convenient way to run initialization scripts when DB has started. You only need to pass your script to proper directory and MySQL will run it when it’s successfully stared, so you can for example create database.

Unfortunately Microsoft SQL Server currently doesn’t have such a mechanism, and official documentation redirects to only one demo, which is coupled with node.js and without node it doesn’t work.

So I have created simplified demo, and here will explain how it works step by step.

Running MS SQL Server from Docker container without initialization

First let’s take a look at official information of how to start Microsoft SQL Server in container:

docker run -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=yourStrong(!)Password' -e 'MSSQL_PID=Express' -p 1433:1433 -d mcr.microsoft.com/mssql/server:2017-latest-ubuntu 

I prefer to manage base image and environment variables in text file, so let’s put all above to Dockerfile:

FROM mcr.microsoft.com/mssql/server:2017-latest-ubuntu

ENV SA_PASSWORD yourStrong(!)Password
ENV ACCEPT_EULA Y
ENV MSSQL_PID Express

Now we can add this file to Git,
build this image - docker build -t db-demo .
and run container in simpler way:

docker run -p 1433:1433 -d db-demo

This way we run clean MS SQL server.
We can additionally add EXPOSE 1433 entry to Dockerfile, to be able to access it from composed containers.

We should also use explicit image version (not latest) to be sure that this Dockerfile will always be valid (new versions will not break down Docker build or runtime initialization in container). So for example let’s choose version: FROM mcr.microsoft.com/mssql/server:2017-CU17-ubuntu (all tags list is usually given in Docker Hub image description).

How to initialize MS SQL server in Docker container

Now it’s time to add extra stuff to have initialization in our server.

The hack is to run at the same time SQL Server and script which will sleep 90seconds (to be sure that server already started) then do whole initialization (we’ll see how). It’s not my idea, this is solution currently recommended by Microsoft and nothing better comes to my mind neither.

First we override Dockerfile CMD entry from base image (which runs SQL server), with our own CMD command, which runs our custom script:

CMD /bin/bash ./entrypoint.sh

and this entrypoint.sh script looks like this:

# Run Microsoft SQl Server and initialization script (at the same time)
/usr/src/app/run-initialization.sh & /opt/mssql/bin/sqlservr

Which means: run run-initialization.sh scripts and at the same time run SQL Server. Base image runs server exactly the same way, which we can check with Docker inspect: docker inspect mcr.microsoft.com/mssql/server:2017-CU17-ubuntu | grep CMD. The only difference is that we add our script here.

Note: server command must be after &, otherwise Docker container will stop when run-initialization.sh script finishes job. In example referenced by Microsoft this order is reversed, but container works because of node.js server running.

Note2: be sure that you pass to Docker image script files with Unix line endings, otherwise you may encounter strange bugs.

So now let’s see how run-initialization.sh script looks like:

# Wait to be sure that SQL Server came up
sleep 90s

# Run the setup script to create the DB and the schema in the DB
# Note: make sure that your password matches what is in the Dockerfile
/opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P yourStrong(!)Password -d master -i create-database.sql

We assume that after 90 seconds DB server will already be ready to use, then invoke sqlcmd utility to run SQL script - 90 seconds is time suggested by Microsoft, in my local environments (for development and tests) I’ve set 30seconds and never had problem with it.

Note: credentials passed to sqlcmd tool must match those given as environment variable in Dockerfile

That’s it, in your SQL script you can do whatever you need to do as startup. In our demo we just create database with schema:

CREATE DATABASE DemoData;
GO
USE DemoData;
GO
CREATE TABLE Products (ID int, ProductName nvarchar(max));
GO

At the end we need to copy those scripts inside Docker image. First we’ll create new directory for them with RUN entry in Dockerfile:

RUN mkdir -p /usr/src/app

then copy files there:

WORKDIR /usr/src/app
COPY . /usr/src/app

and to be sure that run-initialization.sh script will be treated as executable we use chmod command:

RUN chmod +x /usr/src/app/run-initialization.sh

That’s it, whole Dockerfile is available in mentioned GitHub repo.

How to initialize database with data

If you need to have database filled with data, there are several ways to achieve that.

Docker commit

If it’s only for debugging purposes you can do Docker commit to save container state as new image, to recreate it whenever you want. It can be useful for example when you reproduced bug and don’t want to lose it during debugging. Just save database state, then you can recreate whole environment with bug already prepared, as many times as you need. But this way is not recommended for production, because you don’t see explicitly what is in this data so it’s hard to maintain.

Docker volume

You can have your database files already on disk and new server instance just mounted to it with Docker volume. It’s good solution, because you will probably use volume that way or another, to easily persist database.

Script database schema and data

You can script whole database with data included, straight from Microsoft SQL Server Management studio, just click on database name, choose Tasks -> Generate Scripts… and you can choose in advanced options Types of data to scripts - Schema and data.

You can also separate scripts to create schema (empty database) and to add data (fill database), and just run sqlcmd utility several times:

sleep 90s
/opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P yourStrong(!)Password -d master -i create-database.sql
/opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P yourStrong(!)Password -d master -i fill-database.sql

To do so first generate script with Schema only, then once again with Data only. I prefer do it this way to separate schema from data, but it’s just my preference, I feel that it’s cleaner this way but don’t have any tangible evidence that it’s better.

Troubleshooting MS SQL Docker initialization

Like I have written before, always remember to set Unix EOL in files which you copy to Docker image, otherwise you may have incomprehensible bugs.

Moreover, at the moment of writing this article, Microsoft SQL Server Management Studio must be restarted every time container is stopped and recreated from scratch, because MSSMS caches internal DB name and doesn’t refresh it, so there is mismatch. That way or another, upgrade MSSMS to the newest version, because as far as I know older versions works even worse.

Summary

Starting new Docker container with Microsoft SQL Server is really simple. Initializing DB server could be equally simple like in MySQL, but unfortunately Microsoft didn’t get this done so far, so at this moment we need to use ugly hack with sleep. Luckily workaround is already prepared, and I hope that after reading this article it’s easy to understand how exactly it works.

If you want to see MS SQL Server database composed up with Dotnet Core app, I have created demo for it as well (even if you are not interested in .NET, you may want to see SQL Server in Docker Compose).

Please tell me your feelings in comment, about MS SQL server in Docker in general, or about this article if you have any suggestions, or share it with others :)

Git Untold Interface Cheat Sheet

Download free, easy to print Git Cheat sheet!