In case you deploy this container with persistent volume, when the container restart it will try to initialize again and will throw exception?

Most likely - you can simply check that, but I recommend this solution only for development environments. Easy work around - you can add if statement in .sql scripts to check whether DB is already created and filled with data.

# Wait to be sure that SQL Server came up
sleep 90s

This does not ensure that SQL server comes up. On other hand in some cases this makes process waiting for nothing. So called "sleep driven development" is just not effective in general.

Sure you're right, it's really ugly solution - so is the whole company behind it imo ;) As I described this is what Microsoft has suggested at the time of writing this article and like I've written in second paragraph, MySQL gave nice and simple solution for this case and this is what's missing for MsSQL.

https://github.com/vishnubo...

Also, "Note: credentials passed to sqlcmd tool must match those given as environment variable in Dockerfile" - you are aware that those env vars are also available in that script, yes?

Good point, thanks :)

Hi there, first of all, thanks for the very supportive article, rly saved me a big amount of time. Secondary, as far as i can see on the screenshots, u use EF for your example as well. So the question is on following: I'd like to have smth that could start sql in container, create databases (just like in your example), but after that it would be nice if it could return to application (which is not inside the container itself) and apply EF migrations like call (dotnet ef database update for all databases which were created inside the container). Is it somehow possible?

Thanks for commenting out. Chosen framework doesn't matter at all. You connect your application to the database engine just like it was regular installation. Application can live in another Docker container (both can be composed with docker-compose but doesn't need to be) or just inside host or even in some other computer in network. So if you need to run migrations from your application you're free to do that. You can as well not initialize database from script but from your app if you prefer to - you treat this DB just like regular DB installation.
In other article I demonstrate simple app composed via docker-compose with MySQL DB you can look how it's organized there - https://www.softwaredevelop...

Thanks for an answer, but I'm running migrations not from the application itself but with cmd (just from the applicaction database project). So I was curious if I can somehow do this all automatically (docker container run + db creation and than some tool which could run applying migration from the host machine) But seems like it's hardly possible.

You can do that easily too in many ways actually. This containers has sqlcmd built-in, it's even used in this articles example, you can use it to run migrations scripts as well. It can be done just after db initialization or in some point in the future when need arises :)

Great Blog. Thanks for sharing the wonderful and informative blog with Us. Really we got huge information and knowledge from here. Keep Sharing. We Equal Infotech is the leading Software Development Company in India.

Thank you for motivating words, I'm glad that your company find my articles useful. Last months I've been really busy both in my career and in my private life, but I have many new articles in ToDo list. Subscribe (with the button bellow article) to be notified just after new one get released :)

Thanks for this guide! I noticed that stopping and restarting the database container results in duplicated data. So the initialization script runs again. I am considering moving the initialization script to the Dockerfile, or add an if-else somewhere to rerun the scripts when data is already present. Do you have any suggestions on how to approach this?

Hi, thank you for feedback.

I have created simple solution once, with environment variable:

Yaml:

# Set SKIP_DB_INITIALIZATION to "yes", not to clean databases after db restart
environment:
SKIP_DB_INITIALIZATION: "no" # yes/no


Bash:

#!/bin/bash

if [[ "${SKIP_DB_INITIALIZATION}" == "yes" ]]; then
printf "\n\n\nInitializing DB disabled\n\n\n"

else # If variable not set at all then initializing assumed as enabled, for backward compatibility

printf "\n\n\nInitializing DB enabled, sleeping 30s to make sure DB started\n\n\n"
sleep 30s # Wait for the SQL Server to come up

printf "\n\n\nSleeping over, running initial scripts\n\n\n"
/opt/mssql-tools/bin/sqlcmd -S 127.0.0.1 -U sa -P mySecretPassword -d master -i /usr/src/app/CreateDb.sql
fi

(note that Disqus breakes indentations)

Which requires changing it manually, which was enough for me :) If you need something automatic you can try to create some query checking if your data is already present, but this is problematic, because you don't know when db is ready for queries, so you will need some kind of loop with retrying if querying failed due to db not ready, something like this - https://github.com/twright-...

This is exactly what I was looking for, It was very helpful thanks! :D

why wouldn't you just put your initialization script in a separate container and use docker-compose `depends_on` to wait for SQL start

here's an example

test-database:
image: mcr.microsoft.com/mssql/server
build: ms-sql-db
environment:
ACCEPT_EULA: Y
SA_PASSWORD: removed
MSSQL_PID: removed
tty: true
ports:
- 31433:1433
volumes:
- database-data:/var/opt/mssql
healthcheck:
test: nc -z localhost 1433 || exit -1
interval: 10s
timeout: 5s
retries: 5
start_period: 180s

db-init:
image: texas-protax/db-init:1.0
build:
context: .
dockerfile: ./Dockerfile-db-init
restart: on-failure:5
depends_on:
- test-database
environment:
ConnectionString: Server=test-database;Database=test-database;User Id=sa;Password=removed;
tty: true

Hi, thanks for your comment. That's because depends_on doesn't wait for anything it just determines the order in which containers start, so it will try to initialize immediately after db container starts, so when db is not ready yet. I see that you have set restarting on failures in your example so it's kind of work around which probably will work, but trying to run script when db engine is not ready seems risky to me.

Isn't "healthcheck" section guarantees that SQL Server is ready for connections?

Healthcheck section is not designed for this purpose, but even if it was you need to define a way to check your container. Usually applications have a public http endpoint "healthcheck" which returns system status. I don't know such a endpoint in MsSql server (nor any alternative, it doesn't need to be http). Maybe there is a way to query server somehow to get the status, but I don't know such a query neither. You can also "try" running query and see if it fails or works, but as I've written earlier it's work around and trying to run script when db engine is not ready seems like bed idea to me, but maybe it's better then sleeping, which of course is not good solution as well, it's just the recommendation which Microsoft has offered to us.

As someone who found your article useful, I would like to point out that you do not need to wait for a specified amount of time before connecting to the database. Please take a look at this example for a different implementation

https://github.com/twright-...

Hi, thanks for your comment. This is exactly example shown in official Microsoft image description, they just changed waiting fixed amount of time with for loop which retries every second until success - https://github.com/twright-... It seems better, but on the other hand no one knows how all potential scripts will behave in not fully initialized SQL Server. That way or another, both solutions are workarounds, not the proper solution like in MySQL.