Persisting SQL Server Data in Docker Containers – Part 2

So in my previous post, we discussed Docker Volumes and how they have a lifecycle independent of the container enabling us to service the container image independent of the data inside the container. Now let’s dig into Volumes a little bit more and learn where Docker actually stores that data on the underlying operating system.

Docker Volumes

When we working with Docker Volumes we can use the -v option to create the Volume at the time the container is created or we can create the Volume ahead of time with the docker volume create command. The left side sqldata1 is the Volume name. The right side /var/opt/mssql is the directory the Volume will be mapped to inside the container.

We can specify the location of the Volume on the underlying OS but if we don’t it will be created in the default location defined in your Docker preferences which is /var/lib/docker/volumes. Let’s go with the default location for now…we’re going to cover specifying locations in an upcoming post.

docker run 
    --name 'sql19' 
    -e 'ACCEPT_EULA=Y' -e 'MSSQL_SA_PASSWORD='$PASSWORD 
    -p 1433:1433 
    -v sqldata1:/var/opt/mssql 
    -d mcr.microsoft.com/mssql/server:2019-latest

On a system where Linux is the base OS running Docker the container runtime will create Volumes on the base OS’s file system and map them into the container. So as we create Volumes, they will land in /var/lib/docker/volumes when using the code above that doesn’t specify a specific file location on the base OS. Well, /var/lib/docker/volumes is quite a Linux’y sounding path and if I try to browse to that path on my Mac or Windows it doesn’t exist. OK, I don’t have a Windows system but I think it’s a safe bet that’s not there either 🙂

Let’s use the inspect command to get more information on a Docker resource and here we’re using docker volume inspect on sqldata1.

docker volume inspect sqldata1
[
    {
        "CreatedAt": "2019-09-01T11:51:25Z",
        "Driver": "local",
        "Labels": null,
        "Mountpoint": "/var/lib/docker/volumes/sqldata1/_data",
        "Name": "sqldata1",
        "Options": null,
        "Scope": "local"
    }
]

In the output here we can see the Mountpoint, which is where the data lives on the underlying OS, as /var/lib/docker/volumes/sqldata1/_data that’s where Docker put the Volume since I didn’t define a path it put the Volume data in the default location of /var/lib/docker/volumes…but like we said a second ago that path doesn’t exists on my local filesystem…what’s going on?

Docker Volumes on non-Linux Operating Systems

Docker on Mac (and Windows uses the same technique for now) exposes a small Linux VM to provide kernel services to Linux containers. When we create a Docker Volume without specifying a file location for the Volume it will be created *inside* this VM. So that’s where our Volume data is stored. Let’s keep digging…

Using the technique defined here we can get a shell into that Virtual Machine and browse the file system to find the actual data files. Here’s how you attach a shell to the Docker VM.

screen ~/Library/Containers/com.docker.docker/Data/vms/0/tty

Now let’s decompose this command below a bit…screen is a command used to attach a shell to a process, the path (~/Library/Containers/com.docker.docker/Data/vms/0/tty) is the default disk image path defined in your Docker preferences. And the file tty is a process running that exposes a shell interface to the running VM supporting our Linux containers. Everything in Linux is a file, so that tty is actually a terminal process inside the VM that’s exposed outside the VM on your base operating system’s file system. Screen will ‘open’ that file and start to read from it…since it’s exposes a terminal you get a shell into the VM.

Now that we’re inside the VM let’s look around and see where our Volume data lives, we see all of the actual SQL Server file data supporting the instance and the databases and log files.

ls /var/lib/docker/volumes/
metadata.db  sqldata1

In the directory listing above we’ll see a directory for each named Docker Volume in this case we see sqldata1.

ls /var/lib/docker/volumes/sqldata1/_data/
data     log      secrets

Inside of sqldata1, we will start to see the directories and files created by SQL Server in the Docker Volume. Remember SQL Server thinks this is /var/opt/mssql because this path is being mapped into the container at that location.

ls /var/lib/docker/volumes/sqldata1/_data/data/
Entropy.bin                     model.mdf
MS_AgentSigningCertificate.cer  modellog.ldf
TestDB1.mdf                     msdbdata.mdf
TestDB1_log.ldf                 msdblog.ldf
master.mdf                      tempdb.mdf
mastlog.ldf                     templog.ldf

And if we look inside the data directory we’ll see system and user databases.

What’s Next?

So now that we know where Docker Volume data lives when running Linux Containers on a Mac (this same technique applies to Windows too), in our next post we’re going to go a little bit further into this and define a specific location for Docker to store our data on the base OS.

The post Persisting SQL Server Data in Docker Containers – Part 2 appeared first on Centino Systems Blog.