/Mssql-docker-initialization-demo

Demonstration of Microsoft SQL Server on Linux in a Docker container with creating example database after initialization.

Primary LanguageDockerfileOtherNOASSERTION

Overview

This is example of Microsoft SQL server in Docker (Linux) container, with initialization script which creates database with schema after start-up.

It's simplified version of demo which was shown at the Nordic Infrastructure Conference (NIC) 2017 in Oslo, Norway on Feb 3, 2017. Node.js app was removed as well as importing data from .csv file - the point of this example is to show how to run and initialize database.

In my opinion running app which needs access to DB should be done from different Docker image, app should not stay in one Docker image with database.

Unfortunately MsSql doesn't have procedure to run scripts when DB started properly for the first time, like for example MySQL does, so initialization is made with sleep hack:

  1. Start DB
  2. At the same time run initialization script:
    1. Wait 90seconds to be sure that DB has fully started
    2. Run SQL script via SqlCmd utility

Running Database

Clone demo for the first time

First, create a folder on your host and then git clone this project into that folder:

git clone https://github.com/SoftwareDeveloperBlog/mssql-docker-initialization-demo

Build image

To run the demo you just need to build the image (from root directory):

docker build -t db-demo .

Run container

Then, you need to run the container:

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

If you want to see logs you can skip -d (detach) flag, but then be careful with CTRL+C not to stop the container. You can also run container in detach mode, then check its name or id with docker ps and use:

docker logs ContainerIdOrName -f

You can also use trick with xargs explained on SoftwareDeveloper.Blog:

docker run -p 1433:1433 -d db-demo | xargs docker logs -f

We use -f flag to follow new logs. You can also set your custom name to container, for example:

docker run -p 1433:1433 -d --name test-db db-demo
docker logs test-db -f

But exactly the same name can be given only once.

Connect to database

Then you can connect to the SQL Server with SQL Server Management Studio (SSMS). To do it from your host machine provide localhost as Server name. Choose SQL Server Authentication and provide sa user with password from Dockerfile.

Troubleshooting

Problems with SSMS: Upgrade SSMS to the newest version. At the time of writing, you need to restart SSMS every time you start new container, to have new internal name of DB updated.

Problems with started container: Be careful to have Unix line endings style in scripts which you pass to image, as Windows style may cause problems (usually with not clear message).