Better documentation about how to extend

[shawmanz32na]

I'm trying to extend the microsoft/mssql-server-linux image for a customized one to use in our deployment that automatically deploys a database backup, much like #120 . For specifics, I've made a new Dockerfile that looks like:

FROM microsoft/mssql-server-linux
COPY ./docker-entrypoint.sh /usr/src/app
COPY ./my_db_ backup.bak /usr/src/app
ENTRYPOINT ["/bin/bash", "/usr/src/app/docker-entrypoint.sh"]

with the docker-entrypoint.sh:

/opt/mssql/bin/sqlservr & \
/opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P "$SA_PASSWORD" -Q "RESTORE DATABASE MyDB FROM DISK = '/usr/src/app/my_db_backup.bak' WITH MOVE 'MyDB_Data' TO '/var/opt/mssql/data/mydb.mdf', MOVE 'MyDB_Log' TO '/var/opt/mssql/data/mydblog.ldf';"

This fails with the following error:

2018-01-05 22:16:26.87 Logon       Error: 18456, Severity: 14, State: 7.
2018-01-05 22:16:26.87 Logon       Login failed for user 'sa'. Reason: An error occurred while evaluating the password. [CLIENT: 172.1Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : Login failed for user 'sa'..
Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : TCP Provider: Error code 0x2749.
Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online..

Point being that my example is what I consider to be a very basic extension use case - launch a MSSQL container and restore my database. However, it does not appear to be straightforward, and the documentation, as far as I can find, does not describe how to do this. Sure, your Docker hub explanation (https://hub.docker.com/r/microsoft/mssql-server-linux/) points to an extremely basic example of how to extend (https://github.com/twright-msft/mssql-node-docker-demo-app), but doesn't show how to manage passing the configured SA_PASSWORD to commands, nor how to effectively restore a backup. As a comparison point, the Postgres image (https://hub.docker.com/_/postgres/) will automatically run any scripts you put in a particular folder on container start. It makes it very easy to set up/restore a db.

I would love to see a more complete example of how to launch a MSSQL container that restores a database backup on start.

[twright-msft]

Check out this example: https://github.com/twright-msft/mssql-node-docker-demo-app

The key thing missing in your current implementation is a slight delay to allow for sqlservr to start up before you try to connect to it using sqlcmd.

We have a few issues in the backlog to make these kinds of common use cases easier.

[IvanFarkas]

Is this a production ready bulletproof solution?

[mikebridge]

+1

I'm trying to derive an image which adds seeded data and would love to see a better solution. The 90s timeout and chain of script files in CMD is pretty ugly.

[tappoz]

Hey @shawmanz32na @mikebridge did you manage to extend the docker image?

I am doing this:

FROM microsoft/mssql-server-linux:2017-latest

ENV MSSQL_SA_PASSWORD=12345qwerASDF
ENV ACCEPT_EULA=Y

RUN /opt/mssql/bin/sqlservr --accept-eula & sleep 10 \
    && /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P $MSSQL_SA_PASSWORD \
       -q "CREATE DATABASE MyFirstOwnDb;  CREATE DATABASE MySecondOwnDb;" \
    && pkill sqlservr

# CMD [ "/opt/mssql/bin/sqlservr" ]

I've tried both with/without CMD [ "/opt/mssql/bin/sqlservr" ], but I keep getting the same timeout error when trying to connect with sqlcmd from outside the running container.

[mikebridge]

@tappoz I couldn't find a good solution for creating an image with seeded data in one shot. I seed a running image with a separate script.

[shawmanz32na]

@tappoz I was able to create an image that would seed our data, but it was overly complicated and required that I wait some amount of time during the image build and also later when starting the container. I've sanitized and posted my approach at https://gist.github.com/shawmanz32na/ee39c8aecdf643384e810d8dd4c8afae . Again, I'm not satisfied with it, but it's better than nothing.

[mindems]

@shawmanz32na You can optimize your workflow by installing netcat on the server image. I expanded on your approach in the final part of my getting started series.

[mikebridge]

@twright-msft Just wanted to check in again---have you made any progress on implementing anything to make this easier? Being able to create, seed and migrate dockerized mssql databases for various environments would be very powerful, but the sticking point is having to use a work-around to make sqlserver block until it's initialized. I am experimenting with using Kubernetes initContainers and docker two-stage builds to do this, and @shawmanz32na's gist helps but it would be nice to be able to have this run more cleanly.

[Falco20019]

I don't know the source anymore but this works quite well:

RUN ( /opt/mssql/bin/sqlservr --accept-eula & ) | grep -q "Service Broker manager has started" \
    && /creation/createScript.sh

[Vaccano]

RUN ( /opt/mssql/bin/sqlservr --accept-eula & ) | grep -q "Service Broker manager has started" \
    && /creation/createScript.sh

@Falco20019 - Does this insert the data at container startup time? Or at image creation time?

Is it possible to insert the data at image creation time? (So that the user of the container does not have to wait for the script to run.)

[Falco20019]

@Vaccano RUN commands are executed on image creation and create a new layer.

CMD / ENTRYPOINT are executed on the container.

So this is exactly what you are looking for :)