Mssql-docker: Container keeps exiting when a simple database is added with Dockerfile

[samdevx1029]

This is happening with version ctp2-1. Basically, the last line in Dockerfile has CMD to invoke entrypoint.sh. This calls /opt/mssql/bin/sqlservr (which successfully launches SQL Server) and another createdb.sh that waits 30 seconds (for SQL server to be launched), then executes createdb.sql with sqlcmd.
docker logs shows everything to be fine, custom database is created and is accessible externally via SSMS. However, after a few seconds, the container exits! docker logs between the time custom database is created and when the container exits are as follows.

2017-05-21 00:46:46.34 spid51      Starting up database 'DemoData'.
2017-05-21 00:46:46.38 spid9s      The tempdb database has 1 data file(s).
2017-05-21 00:46:46.39 spid22s     The Service Broker endpoint is in disabled or stopped state.
2017-05-21 00:46:46.39 spid22s     The Database Mirroring endpoint is in disabled or stopped state.
2017-05-21 00:46:46.40 spid22s     Service Broker manager has started.
2017-05-21 00:46:46.59 spid51      Parallel redo is started for database 'DemoData' with worker pool size [1].
2017-05-21 00:46:46.61 spid51      Parallel redo is shutdown for database 'DemoData' with worker pool size [1].
2017-05-21 00:46:46.70 spid6s      Recovery is complete. This is an informational message only. No user action is required.
Changed database context to 'DemoData'.

If you need more details, let me know. Here is a link to same issue on StackOverflow.

[twright-msft]

I think the problem here is that the CTP2-1 image no longer calls sqlservr.sh to start sqlservr. The CMD in the Dockerfile that we release on Docker Hub is not just '/opt/mssql/bin/sqlservr'. In other words we start the process directly and not from the sqlservr.sh script.

I updated the Dockerfile just before the CTP2-1 release to reflect this change.
https://github.com/Microsoft/mssql-docker/blob/master/linux/preview/Ubuntu/Dockerfile

If you have a personal Dockerfile that FROMs the mssql-server-linux image and you are using CMD .../sqlservr.sh please change that to CMD .../sqlservr

[samdevx1029]

@twright-msft, i was not calling sqlservr.sh at all. If you look at my comment above, CMD calls my own entrypoint.sh which then calls /opt/mssql/bin/sqlservr and another createdb.sh that executes SqlCmd. Basically what I am trying to do is create a custom db when SQL container starts. I use SqlCmd for that. However, I am having a huge issue with this error "Login failed for user 'sa'. Reason: An error occurred while evaluating the password.". I read this and this. I made sure password did not contain characters ODBC doesn't like. Result? From the same image I created, the container sometimes works just fine (my custom DB is there), and sometimes the DB is not there. docker logs shows that error when the DB is not there. I can still log in with sa from SSMS no problem but again no DB.

I think the question is after /opt/mssql/bin/sqlservr is executed and master, msdb,temp dbs etc. are created, how can we reliably execute SqlCmd to run a custom SQL afterwards? I tried doing that with entrypoint.sh but it is not working 100%.

I think it would help a lot of folks if you can show us how to create a custom DB as the container starts. Just the DB, no MVC or any other app. Or I can post my code here and you can comment?

Thanks!

[twright-msft]

Feel free to post your code or point me to a GitHub project or a Gist.

Here is an example that I created:
https://github.com/twright-msft/mssql-node-docker-demo-app

entrypoint.sh starts sqlservr && starts a import-data.sh script called import-data.sh && starts the app (npm start in this case).

The import-data.sh script sleeps for 90 seconds (I'm being very generous here) and then uses sqlcmd to connect to the SQL Server and run a setup.sql script. Then it imports some data using bcp.

The setup.sql script creates a DB and some schema in that DB.

I think the primary difference between what you did and what I did is that I start another script from entrypoint.sh to do the sqlcmd instead of trying to do sqlcmd in the entrypoint.sh script.

Hope that helps!

[fgauna12]

@twright-msft I have the same issue. I followed the github project you linked to. I am not using NodeJS and I am using Windows 10.
From my import-data.sh I am doing the same things (sleeping for 30s and calling my setup.sql)
My setup.sql just creates my database.
After the database is created, the container stops.
My entrypoint.sh looks like this:

/opt/mssql/bin/sqlservr & ./import-data.sh

If remove the import-data portion, my container doesn't stop itself.

[gperezivo]

@fgauna12 try using this endtrypoint.sh

/opt/mssql/bin/sqlservr & ./import-data.sh && tail -f /var/opt/mssql/log/errorlog

Docker waits for an endless command (as a service). When you remove the npm start from entrypoint, first PID of container ends and stop after import-data.sh finishes.
Adding tail -f PID 1 never ends 😄

[marefr]

Without the npm part I couldn't get the container to continue runnng. I turned it around eventually and got it to work:
./import-data.sh & /opt/mssql/bin/sqlservr

[nicksterx]

@marefr changing the ordering of the import-data.sh script fixed it for me as well.

Does anyone have any idea why that made a difference?

[twright-msft]

Basically, you need a non-terminating process to keep the container alive. In a series of commands separated by single ampersands the commands to the left of the right-most ampersand are run in the background. So - if you are executing a series of commands simultaneously using single ampersands, the command at the right-most position needs to be non-terminating. In my original example, the right-most command was 'npm start' a non-terminating process that runs the web server for the app. In the case above from @gperezivo, the right-most command is 'tail -f' which is non-terminating. In the case of @marefr, the right-most command is sqlservr which is a non-terminating command. 'import-data.sh' is a terminating command so it can't be the right-most or the container will shut down as soon as import-data.sh completes. Some people also use 'sleep inifinity' as the right-most command in an entrypoint.sh script. Hope that helps!

[nicksterx]

@twright-msft That does thanks!

[twright-msft]

OK great. I'll close out this issue for now. I've requested our documentation team to add this as a best practice to our best practices guide. https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-configure-docker?view=sql-server-linux-2017