Initialize MS SQL Server in Docker container - creating database at startup
Few days back I wrote a blog post about initializing SQL Server while running a
docker-compose command. In that implementation I was using another docker image to run the migrations. In this post I am creating a dockerfile, some shell scripts and the SQL Script file which will create the database while running the
docker-compose up command.
If you inspect the SQL Server docker image with the help of docker inspect command (
docker inspect mcr.microsoft.com/mssql/server:2019-latest | Select-String -Pattern 'CMD' -CaseSensitive -SimpleMatch) you will be able to see the launch command for SQL Server executable like this -
"CMD [\"/opt/mssql/bin/sqlservr\"]" I am running the command against SQL Server 2019 and using powershell - this command will launch the SQL Server instance when we run the SQL Server docker container.
So here is the dockerfile I have created - we will explore each step in detail.
First I need a base image - I am using the SQL Server 2019 image. Next I am switching to root user - This step is required for setting the executable permission for the
entrypoint.sh script file. Next I am copying the
setup.sql - for this demo I have created one file, you can use multiple SQL files or use the file generated using database migrations.
Next I am copying the
import-data.sh - this script file is responsible for executing the SQL file in the SQL Server.
In this script I am running the SQLCMD command in a loop and if it is successfully completed, stops the execution. You can use Sleep command as well - but this is recommended than sleep command. Next I am copying the
entrypoint.sh shell script - in this file we are executing the
import-data.sh shell script and starting the SQL Server. The password used in the script is same as the password we are providing while running the
docker-compose file or
docker run command.
Next we are making the
entrypoint.sh as executable file using the
chmod command. And finally we are running the
entrypoint.sh - which will execute the
import-data.sh shell script and later run the SQL Server. You need to make sure the commands are in order - in the sample repo provided in the official documentation the order of the commands is different which may not work as expected. In the sample it is working because the container running a NodeJS server.
Lets run the container, I have created the file with the name
Db.Dockerfile and running using the command -
docker build -t anuraj/todosql --file .\Db.Dockerfile ., and here is the output.
This will create the SQL Server image with the files and you can run the container with following command -
docker run -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=Password@12345' -p 14331:1433 -d anuraj/todosql. We can connect to the SQL Server using Sql Management studio and verify whether the database is created.
Using this way we can create a SQL Server docker images with Database initialization. We can use this image in docker-compose.yml file and deploy the app to azure web app or any other cloud provider which supports docker-compose file.
Happy Programming :)