Docker container for SQL Server Linux keeps exiting

Question

I wanted to modify the behavior of container for SQL Server Linux by adding a simple database with one table as the container starts.

The docker image version where I am seeing this issue with is the latest version as of 5/20/2017 i.e. ctp2-1.

I am using Docker for Windows with the latest version at 17.05.0-ce. I increased RAM of the MobyLinuxVM to 6144MB since more than 4GB is recommended.

Steps to reproduce issue

PREPARE FILES

(1) Create a local Windows folder, in my case, C:\temp\docker\ (2) Add "Dockerfile" (note no file extension) with the following content. FROM microsoft/mssql-server-linux:latest COPY ./custom /tmp RUN chmod +x /tmp/entrypoint.sh \ && chmod +x /tmp/createdb.sh CMD /bin/bash /tmp/entrypoint.sh (3) Add a subfolder "custom" C:\temp\docker\custom\ (4) Add 3 files to "custom" subfolder with following content. (A) entrypoint.sh /opt/mssql/bin/sqlservr & /tmp/createdb.sh (B) createdb.sh sleep 30s /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P '!StrongPass45' -d master -i /tmp/createdb.sql (C) createdb.sql CREATE DATABASE DemoData; GO USE DemoData; GO CREATE TABLE Products (ID int, ProductName nvarchar(max)); GO

RUN DOCKER COMMANDS (YOU WILL SEE THE ISSUE AT THE LAST STEP HERE)

(1) Open PowerShell and cd to folder in step (1) above, in my case cd C:\temp\docker (2) Run docker build command docker build . (3) After image is built, run the following command and remember the first 3 letters of your image id, in my case "e65" docker images [![enter image description here][2]][2] (4) Create the container with the following command (note the last 3 characters should be replaced by yours, also use the same password you used above) docker run -d -e SA_PASSWORD="!StrongPass45" -e ACCEPT_EULA="Y" -p 1433:1433 e65 (5) Check your container is running docker ps -a [![enter image description here][2]][2] (6) Wait about 2 minutes and check your container status again. AT THIS POINT, THE CONTAINER WOULD HAVE EXITED. docker ps -a [![enter image description here][2]][2]

I checked the logs as follows.

docker logs e65

Attached below is the bottom part of logs right after SQL Server has successfully created the DemoData database.

[![enter image description here][2]][2]

IMO, the problem line in the logs is this one

Parallel redo is shutdown for database 'DemoData' with worker pool size [1].

I have tried various other SQL statements (even adding custom MDF and LDF files and attaching to them) to add behavior to OOB image. I have even been able to connect to the new database using SSMS for a few seconds before the container exits!

Has anyone seen this issue? Can someone give this a try?

Your startup command is probably returning, thus exiting the container. If you replace the -d with -it, add a /bin/bash to the end of your run command, you'll get an interactive prompt and can troubleshoot your CMD. — user2105103
– user2105103, Commented May 21, 2017 at 2:33
@user2105103 I did interactive, got bash prompt, then simply ran "/tmp/entrypoint.sh" which is exactly what CMD in Dockerfile is supposed to do too. Well, the container worked and did not exit using interactive. Do you know what may be going on? — SamDevx
– SamDevx, Commented May 21, 2017 at 4:18
Did the CMD return? Does it work if you comment out your createdb? — user2105103
– user2105103, Commented May 21, 2017 at 13:43
@user2105103, yes CMD is returning. If I comment out createdb, it works but there is no db obviously. So I guess the question is how to make CMD not return, right? Is this difficult to do? — SamDevx
– SamDevx, Commented May 21, 2017 at 17:10

SamDevx · Accepted Answer · 2017-05-21 19:27:23Z

A Solution That Works

Based on the feedback I received, it was clear that CMD from Dockerfile was returning. Here are the complete steps that solve the issue.

[Please let me know if you have found a more reliable solution which does not depend on a certain timeout value I have coded in this solution to allow SQL Server to bootstrap itself as the container starts]

PREPARE FILES

(1) Create a local Windows folder, in my case,

C:\temp\docker\

(2) Add "Dockerfile" (note no file extension) with the following content.

FROM microsoft/mssql-server-linux:latest COPY ./custom /tmp RUN chmod +x /tmp/entrypoint.sh CMD /bin/bash /tmp/entrypoint.sh

(3) Add a subfolder "custom"

C:\temp\docker\custom\

(4) Add 2 files to "custom" subfolder as follows.

entrypoint.sh

#!/bin/bash set -e run_cmd="/opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P HdkPKD57%@6876^ -l 30 -d master -i /tmp/createdb.sql" >&2 echo "Allowing 30 seconds for SQL Server to bootstrap, then creating database.." until $run_cmd & /opt/mssql/bin/sqlservr; do >&2 echo "This should not be executing!" done

createdb.sql

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

RUN DOCKER COMMANDS

(A) Open PowerShell and cd to folder in step (1) above, in my case

cd C:\temp\docker

(B) Run docker build command

docker build .

(C) After image is built, get first 3 characters of the image, in my case 086

docker images

(D) Create a container using the correct image id and correct password

docker run -d -e 'SA_PASSWORD=HdkPKD57%@6876^' -e 'ACCEPT_EULA=Y' -p 1433:1433 086

(E) Check your container is running

docker ps -a

This container does not exit! Intended database "DemoData" is created. Problem solved!

docker logs 2aa command (2aa is my container id, yours will be different) shows clean build, no errors or warnings. The logs begin as follows

Allowing 30 seconds for SQL Server to bootstrap, then creating database.. This is an evaluation version. There are [173] days left in the evaluation period. 2017-05-21 17:39:50.69 Server Setup step is copying system data file 'C:\templatedata\master.mdf' to '/var/opt/mssql/data/master.mdf'. ....

and end as follows.

2017-05-21 17:39:54.20 spid51 Starting up database 'DemoData'. 2017-05-21 17:39:54.43 spid51 Parallel redo is started for database 'DemoData' with worker pool size [1]. 2017-05-21 17:39:54.44 spid51 Parallel redo is shutdown for database 'DemoData' with worker pool size [1]. 2017-05-21 17:39:54.51 spid7s Recovery is complete. This is an informational message only. No user action is required. Changed database context to 'DemoData'.

Connect with SSMS

I was able to successfully connect to this database using SSMS as shown below (IP address 10.0.75.1 is that of the docker host that contains the container)

Important Notes

sqlcmd SA Password

sqlcmd is the utility being used to run dbcreate.SQL and create the database DemoData. The utility uses ODBC driver on Linux and is sensitive to how you can specify values for switches, especially the password -P.

To avoid login related issues found here and explained here, choose your strong password carefully and specify it in entrypoint.sh under -P without wrapping with double quotes or single quotes or []. See (4) above.

Further, this password has to match the docker run environment variable you are passing to the container. See (D) above to avoid password mismatch.

Detailed documentation on sqlcmd is here.
sqlcmd Login Timeout

Note how I used the -l switch to specify login timeout of 30 seconds for sqlcmd in entrypoint.sh file. This is the crux of my solution to avoid the CMD from returning (which makes the container to exit). This timeout is sufficiently long enough for SQL server to start.

#1 - Chg your sa password ;) #2 - Another approach would be to have the container start up normally, then docker exec the createdb command afterwards.
@user2105103, #2 would work but in my case I'm automating everything on a build machine and pushing the image to a registry. A puller simply needs to docker pull <image> and the db will be there. That saves a puller from knowing details of creating databases. I have automated scenarios where the databases are created using Entity Framework Code First Migration and pushed as images to registry. Another developer would then docker-compose a new app where the db part is already taken care of. Not sure what you meant by #1 - Change your sa password.
I've been trying to follow this, but am stumped where the .sql script never appears to be run. Are there any steps that might have been left out? I've verified if I run it from inside bash it works just fine, but like you prefer a fully automated solution

Collectives™ on Stack Overflow

Docker container for SQL Server Linux keeps exiting

1 Answer 1

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Related