Persisting data using named volumes - dbafromthecold/SqlServerAndContainersGuide GitHub Wiki

So far in this guide we've gone through spinning up a container and connecting to SQL Server. However containers are ephemeral by default so any changes made in SQL will be lost when we delete the container. Meaning that any databases we create will be lost, really lost. There's no way to get them back.

So let's run through how we can persist our databases from one container to another by using docker named volumes.

To create a named volume run: -

docker volume create mssqlsystem
docker volume create mssqluser

/images/3.PersistingData/NamedVolumes/1.docker_volume_create.png

Notice we're creating two volumes here, one will be for the system databases and one will be for our user databases.

To check the volumes: -

docker volume ls

/images/3.PersistingData/NamedVolumes/2.docker_volume_ls.png

Now we can spin up a container with those volumes mapped: -

docker container run -d `
-p 15789:1433 `
--volume mssqlsystem:/var/opt/mssql `
--volume mssqluser:/var/opt/sqlserver `
--env ACCEPT_EULA=Y `
--env MSSQL_SA_PASSWORD=Testing1122 `
--name sqlcontainer1 `
mcr.microsoft.com/mssql/server:2019-CU5-ubuntu-18.04

/images/3.PersistingData/NamedVolumes/3.docker_container_run.png

The mssqlsystem volume is mapped to /var/opt/mssql which is the location of the system databases. The mssqluser volume is mapped to /var/opt/sqlserver which will be created for us.

By persisting the system databases we won't have to manually attach any user databases we create in any further containers that use these volumes. The master database will be persisted which will have a record of the database(s).

Let's run through that here.

First thing we need to do is grant the mssql user access to the location mapped to the mssqluser volume. We need to do this as SQL Server 2019 by default does not run as root within the container, it runs under the mssql user: -

docker exec -u 0 sqlcontainer1 bash -c "chown -R mssql /var/opt/sqlserver"

Now we can create a database: -

mssql-cli -S localhost,15789 -U sa -P Testing1122 `
-Q "CREATE DATABASE [testdatabase] ON PRIMARY (NAME='testdatabase',FILENAME='/var/opt/sqlserver/testdatabase.mdf') LOG ON (NAME='testdatabase_log',FILENAME='/var/opt/sqlserver/testdatabase_log.ldf');"

/images/3.PersistingData/NamedVolumes/4.mssql_cli_create_database.png

Check that the database has been created: -

mssql-cli -S localhost,15789 -U sa -P Testing1122 -Q "SELECT [name] FROM sys.databases;"

/images/3.PersistingData/NamedVolumes/5.mssql_cli_get_databases.png

And now let's blow away that container: -

docker container rm sqlcontainer1 -f

/images/3.PersistingData/NamedVolumes/6.docker_container_rm.png

Confirm that it's gone: -

docker container ls -a

/images/3.PersistingData/NamedVolumes/7.docker_container_ls.png

OK, container is gone but we still have our volumes: -

docker volume ls

/images/3.PersistingData/NamedVolumes/2.docker_volume_ls.png

So let's now spin up another container, remapping the volumes: -

docker container run -d `
-p 15799:1433 `
--volume mssqlsystem:/var/opt/mssql `
--volume mssqluser:/var/opt/sqlserver `
--env ACCEPT_EULA=Y `
--env MSSQL_SA_PASSWORD=Testing1122 `
--name sqlcontainer2 `
mcr.microsoft.com/mssql/server:2019-CU5-ubuntu-18.04

/images/3.PersistingData/NamedVolumes/8.docker_container_run2.png

And let's check that our database is there: -

mssql-cli -S localhost,15799 -U sa -P Testing1122 -Q "SELECT [name] FROM sys.databases;"

/images/3.PersistingData/NamedVolumes/9.mssql_cli_get_databases2.png

Excellent! By using named volumes we've persisted a database from one container to another!