Persist SQL Server Container Data-Part1

By default docker container does not persist data however there are ways available to us to bypass that limitation. In this post, I will explain the basic way to persist data within SQL Server containers.

Volumes(-v, –volumes) and Mount (–mount) will allow us to persist data within a docker container however Docker volumes are the preferred way for handling persistent data created by and used by Docker containers.

Volumes are easier to back up or migrate than bind mounts. You can easily manage volumes using Docker CLI commands or the Docker API moreover there are compatible with both Linux and Windows containers. There can be more safely shared among multiple containers and let you store volumes on remote hosts or cloud providers, to encrypt the contents of volumes, or to add other functionality.

For this demo we will map C:\docker\sqldemo Windows directory drive to /var/opt/mssql which is the Linux SQL Server container directory drive however same can be applied for Linux environment. -V option allow you map your local volumes drive to your SQL Server docker containers.

$Container_Name = "mydemo"
docker run --rm --name $Container_Name  `
    -e "SA_PASSWORD=yourStrong(!)Password"  `
    -p1433:1433 `
    -e "ACCEPT_EULA=Y" `
    -v 'c:\docker\sqldemo:/var/opt/mssql' `
    --hostname $Container_Name  `
    -d mcr.microsoft.com/mssql/server:2019-CU4-ubuntu-16.04

If you go to C:\docker\sqldemo from your local directory, you will see all SQL Server systems databases plus other binary directories are created as you can see from below screenshot.

If you are not using Powershell for this demo, please run docker ps -a to get docker container id replace variable $Container_Name with the container id in order for you to access SQL Server.

# $Container_Name = "mydemo"
docker exec -it $Container_Name /opt/mssql-tools/bin/sqlcmd `
    -S localhost -U SA -P "yourStrong(!)Password" `
    -Q "SET NOCOUNT ON;
        GO
        CREATE DATABASE Demo_DB1;
        GO
    "

As you can see from below screenshot. Both Demo_db database data and log files are created successfully.

Data will always persist even if you stop and restart SQL Server container service unless if you dropped the directory or Demo_DB database.

Below there is an explanation of used parameters:

• -e (or -env) – sets environment variables, in our case we accept End User License Agreement and sets a password for “sa”
• –hostname – sets container hostname, we can omit this, then docker will generate its own name
• -p (or –publish) – publishes a container’s port to the host. In our case default SQL port (1433) is published on port 1401
• -v (or –volume) – binds mount a volume. It is the location of persistent storage for SQL Server databases
• –name – assigns a name to the container
• -d (or–detach ) – runs the container in background and print container ID