Put default data into MS database

TL;DR: How do I launch an MSSQL container and init a database before another container starts? (and keep it running)

What is the best way to put data into an database?
I am trying to use docker-compose up to launch a server, but that server requires a MSSQL database with a cretin table structure (and change SA password). I have tried to initiate the database in the same and a separate Dockerfile, but there is always some major-breaking problem. Does anyone have an working example?

On the Dockerhub page https://hub.docker.com/_/microsoft-mssql-server, there is a link to an example that explains how to do what you ask for: https://github.com/twright-msft/mssql-node-docker-demo-app

Thanks! I will look into it. I have been reading https://hub.docker.com/r/microsoft/mssql-server-windows-express/, and that page does not have any examples.

Are you aiming for a linux container or a windows container? Your link points to a windows container.

Windows container. Will that be a problem (more than that I have to replace the sample code)?

I would expect the approach to be identical… though, the technical implement might be different. I have no idea … I never ran a single windows container in my life.

I won’t be able to help there.

Since the container is ephemeral, you should consider mapping an external volume which has the already created database on it. You could potentially create it by bringing up the container once and then running the mysql client in it to source the database from a dump file.
Here’s an example slightly redacted from a compose file I use:
mysql:
image: mysql:5.7.29
networks:
- backend
environment:
- MYSQL_ROOT_PASSWORD=mysql

  • MYSQL_DATABASE=mydatabase
    volumes:
    - /docker_var/mysql:/var/lib/mysql
    - /docker_var:/var/files
    ports:
    - 33061:3306

Right idea, wrong rdbms :slight_smile:

What I did was to include MSSQL in my image.

FROM microsoft/dotnet-framework:4.7.2-runtime
FROM microsoft/mssql-server-windows-express:2017-GA

And before my application starts, I restore an included backup (or SQL script).

sqlcmd -S mssql -U sa -P ******** -q “RESTORE DATABASE dbSystem FROM DISK = ‘dbSystem.bak’”
software.exe

I read somewhere that it was not recommended to include more than one FROM, but I didn’t see why, and it seams to work.
Thanks for the help, by the way!

So it is possible that the mssql-server-windo… already has the 4.7.2-runtime embedded in it, which would make the first FROM redundant…

is it possible to add/modify the start of the ‘calling’ container to include the powershell function Test-NetConnection (which is equivalent to TelNet) in a loop to test that the SQL service is ready and listening, and once that passes, then start the application in the listening container?

Also, [rickspiewak] makes a valid point (unless the data in the database is read only). If there are updates to the SQL data, there is a high chance of these changes being lost should the container fail, and have to be re-created. This is the model for containerization: If it ain’t running right, kill it and create a new one…

There is an environment variable in the MS SQL server images that allows one to attach a database at runtime, (provided that you create a mount point Volumes | Docker Docs ) . This allows one to have the MDF file on the host, and yet allow the SQL service in the container to access the database. This then allows you to re-create the container without risk of data loss…

Well, you basicly defined a multi-stage build, but did nothing with the first stage… so, yes it’s not required. An Image you build will have always one base Image.

Someone with Windows container experience needs to answer the rest.

But (if I don’t miss recall) remove FROM microsoft/mssql-server-windows-express:2017-GA from the file I can not use SQLCMD and if I remove FROM microsoft/dotnet-framework:4.7.2-runtime I can not use the intended software. With bouth I can, but I will check again though.