It is a huge hasstle to work with a database in a docker container if you cannot persist the data in the event that the container shuts down. In this short post I will show you a cool way to set up shared volumes and auto mount your databases.

You could setup just one named volume and manually reattach the databases after a new container spins up.

This isn’t really ideal, what we’d want is for the databases to automatically be attached to the new container. Thankfully there’s an easy way to do it, so let’s run through the steps here.

Here’s how to do it.

First thing, is to create two named volumes: –

docker volume create mssqlsystem
docker volume create mssqluser

And now spin up a container with the volumes mapped: –

docker run --restart unless-stopped --name sqlserver \
--hostname sqlserver --volume mssqlsystem:/var/opt/mssql \
--volume mssqluser:/var/opt/sqlserver -e "ACCEPT_EULA=Y" \
-e "MSSQL_SA_PASSWORD=SOMESTRONGPASSWORD" -p 16120:1433 \
-d mcr.microsoft.com/mssql/server:2019-latest

The mssqluser named volume is going to be mounted as /var/opt/sqlserver and the mssqlsystem volume is going to be mounted as /var/opt/mssql. This is the key to the databases automatically being attached in the new container, /var/opt/mssql is the location of the system databases.

If we didn’t mount a named volume for the system databases any changes to those databases (particularly for the master database) would not be persisted so the new container would have no record of any user databases created.

By persisting the location of the system databases, when SQL starts up in the new container the changes made to the master database are retained and therefore has a record of the user databases. This means the user databases will be in the new instance in the new container (as long as we’ve persisted the location of those databases, which we’re doing with the mssqluser named volume).

You can now go ahead an create a database and you will be able to see it now exists in /var/lib/docker/volumes/mssqlsystem/_data/data on the host OS

Testing the persistence of the data

Let us now destroy the container and see if our datase survives: -

docker kill sqlserver
docker rm sqlserver

That container is gone, but we still have our named volumes: –

docker volume ls

So we can now spin up another container, using those volumes: –

docker run --restart unless-stopped --name sqlserver \
--hostname sqlserver --volume mssqlsystem:/var/opt/mssql \
--volume mssqluser:/var/opt/sqlserver -p 16120:1433 \
-d mcr.microsoft.com/mssql/server:2019-latest

The environment variables ACCEPT_EULA and SA_PASSWORD do not need to be set for the second container as we have persisted the system databases so their values have been retained

When you now connect to the SQL instance in the new container the database is there.

Changing the SA password for the SQL instance

You can change to sa password at anytime by running this code: -

docker exec -it sqlserver /opt/mssql-tools/bin/sqlcmd \
-S localhost -U SA -P "<OLDPASSWORD>" \
-Q "ALTER LOGIN SA WITH PASSWORD='<NEWPASSWORD>'"

Thanks for reading.