30 Dec 2021
Running SQL Server in Docker
- Part 1 - Introduction
- Part 2 - Getting Started
- Part 3 - Creating a basic design
- Part 4 - Upgrade to .NET Core 3.1
- Part 5 - Database test
- Part 6 - Create the database
- Part 7 - No architecture deep dive
- Part 8 - First API mockup
- Part 9 - A new start
- Part 10 - Create the database scripts
- Part 11 - Create the group controller and service
- Part 12 - This Article
The webapi needs a decent database and SQLite is the right tool in this case. So I played a bit with the docker images of SQL Server and decided to go with that. The docker images give you the option to run the database when you need them without the need to install any dependency (apart from Docker). When the project is done, delete the containers and images and you are done. And docker is multi platform so it runs on my Linux and Windows workstation.
SQL Server in a Docker container
First of all the database needs to run. Based on the documentation I use this command.
docker run --rm -ti \ -p 1433:1433 \ --env "ACCEPT_EULA=Y" \ --env "SA_PASSWORD=SecretPassword-865" \ --env "MSSQL_PID=Express" \ --env "TZ=Europe/Amsterdam" \ --volume ~/src/football/__mssql/data:/var/opt/mssql/data \ --volume ~/src/football/__mssql/log:/var/opt/mssql/log \ --volume ~/src/football/__mssql/secrets:/var/opt/mssql/secrets \ --volume $PWD:/app \ --workdir /app \ --name sql1 \ --hostname sql1 \ mcr.microsoft.com/mssql/server:2019-latest
There is a lot going on in that command.
First a lot of environment variables are defined. Let’s go through them.
SA_PASSWORD=SecretPassword-865 are required to get the database running.
MSSQL_PID=Express determines which product I want to use.
For this project I really don’t need anything more then the Express Edition of SQL Server.
I also set the timezone to
TZ=Europe/Amsterdam to make sure the times are handled correctly.
Especially with the upcoming World Championship in Qatar, putting the times with a timezone offset in the database would be really nice.
The examples in the documentation all run the database detached, but I don’t need that.
I don’t mind having one tab in my terminal dedicated to a running database while developing.
Therefore I replaced
-d in the samples with
To exit the database I can simply press
Ctrl+C in that tab and it stops and the container is gone.
This has one major drawback.
The data is destroyed once the container is gone.
To fix that I added the
Now the data is stored outside the container in the given directories.
Note that to prevent permission denied errors when you run the command for the first time, you need to manually create the
secrets directories and
chmod 777 them.
This way the
mssql user in the container can write to the volumes.
Also note that I have a global gitignore rule to exclude everything that starts with two underscores.
__mssql directory is not interfering with my git repository even though it is located within my source directory.
Create the database
With the SQL Server running, the database needs to be created.
This is where the options
--volume $PWD:/app and
--workdir /app come into play.
With them you can
docker exec into to container and use
sqlcmd from within the container to run SQL commands to create or query the database.
docker exec -ti sql1 /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P SecretPassword-865 -i ./tables-mssql.sql
Again, no need to install
sqlcmd just use it within the container.
Not another tool you don’t use that much polluting the system.
All I need are some scripts to run these commands.