Persisting SQL Server Data in Docker Containers – Part 3
In the first two posts in this series we discussed the need for data persistency in containers then we discussed where the data actually lives on our systems. Now let’s look at specifying the location of the data on the underlying file system of the base OS.
This is the third post in a three part series on Persisting SQL Server Data in Docker Containers. The first post introducing Docker Volumes is here. The second post on where Docker actually stores your data is here.
Exposing Directories on the Base OS into a Container
Now what if I wanted to expose a directory from my base OS, macOS directly into the container avoiding placing my data inside the Docker Linux VM. Let’s try it and see what happens…let’s start up a container with a Docker Volume mapping /Users/demo/demos/data on the base OS into the container at /var/opt/mssql.
docker run \
--name 'sql19dv' \
-e 'ACCEPT_EULA=Y' -e 'MSSQL_SA_PASSWORD='$PASSWORD \
-p 1433:1433 \
-v /Users/demo/demos/data:/var/opt/mssql \
-d mcr.microsoft.com/mssql/server:2019-latest
If we do a docker ps -a we’ll find our conainer existed with a non-zero exit code. That’s bad.
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES 2f4a9efc2f89 mcr.microsoft.com/mssql/server:2019-latest "/opt/mssql/bin/perm…" 21 seconds ago Exited (1) 11 seconds ago sql19dv
The first thing you should do when this happens is to examine the container’s logs. We can do that with docker logs sql19dv (where sql19dv is our container name) and we’ll get this output.
This program has encountered a fatal error and cannot continue running at Sun Sep 1 14:19:06 2019
The following diagnostic information is available:
Reason: 0x00000006
Message: Kernel bug check
Address: 0x6b047d50
Parameters: 0x10861f590
Stack Trace:
000000006b13542e
000000006b047dab
000000006b03447e
000000006b043025
000000006b0431f6
000000006b1336fc
000000006b13226f
000000006b175661
Process: 9 - sqlservr
Thread: 13 (application thread 0x4)
Instance Id: 866b1bc1-211d-4390-aa43-a48b32d6f78e
Crash Id: 07c2a35d-5ddf-4e5d-ad69-a91ef0f5d0e9
Build stamp: 228a531f7a324b94dd3127e706f889b081f5677bd368be8b30485d8edda4d02b
Distribution: Ubuntu 16.04.6 LTS
Processors: 6
Total Memory: 6246559744 bytes
Timestamp: Sun Sep 1 14:19:06 2019
Last errno: 2
Last errno text: No such file or directory
Ubuntu 16.04.6 LTS
Capturing core dump and information to /var/opt/mssql/log...
dmesg: read kernel buffer failed: Operation not permitted No journal files were found.
No journal files were found.
Sun Sep 1 14:19:07 UTC 2019 Capturing program information
Sun Sep 1 14:19:08 UTC 2019 Attempting to capture a dump with paldumper
Captured a dump with paldumper
Sun Sep 1 14:19:11 UTC 2019 Capturing program binaries
Sun Sep 1 14:19:12 UTC 2019 Compressing the dump files
In the output above, SQL Server crashes trying to access the file inside the container that isn’t there…see the ‘Last errno text’ using strace inside the container yields the following information.
[pid 11] lstat("/opt/mssql/lib/system", 0x7f8f15b63350) = -1 ENOENT (No such file or directory)
[pid 11] <... lstat resumed> 0x7f8f15b63350) = -1 ENOENT (No such file or directory)
Let’s Map a Different Path and See What Happens
docker run \
--name 'sql19dv2' \
-e 'ACCEPT_EULA=Y' -e 'MSSQL_SA_PASSWORD='$PASSWORD \
-p 1432:1433 \
-v /Users/demo/demos/data:/var/opt/mssql/data \
-d mcr.microsoft.com/mssql/server:2019-latest
Here we’re mapping to /var/opt/mssql/data (where above we mapped /var/opt/mssql). This mapping will fail and the container won’t start but this time for a different reason.
If we look at docker logs you’ll find the following error in from the SQL Server Error Log.
2019-09-02 18:10:15.08 Server Error 87(The parameter is incorrect.) occurred while opening file '/var/opt/mssql/data/master.mdf' to obtain configuration information at startup. An invalid startup option might have caused the error. Verify your startup options, and correct or remove them if necessary.
Makes sense…we changed where SQL Server is reading/writing data. macOS doesn’t support a file mode called O_DIRECT which allows for unbuffered read/write access to the file opened using the open system call. O_DIRECT is used by systems that manage their own file caching, like relational database management systems (RDBMS). So as SQL starts up and tries to open the master database with O_DIRECT the files can’t be opened because the macOS kernel doesn’t support this mode. And this is the reason why we have to have that Linux VM around. That Linux VM will support O_DIRECT option on the file opened. See more about this at the GitHub issue here.
An strace of the thread shows the following:
open("/var/opt/mssql/data/master.mdf", O_RDONLY|O_DIRECT
<... open resumed> ) = -1 EINVAL (Invalid argument)
...output omitted...
--- SIGSEGV {si_signo=SIGSEGV, si_code=SEGV_ACCERR, si_addr=0xb804b1fe8} —
...the remainder of the strace shows the creation of the SQL Server Error Log record above...
This issue is specific to macOS. On a Linux machine it would map the base OS directory directly into the container and the file operations will work because the kernel supports the correct file modes on the open system call. On a Window machine this works as confirmed by my friend and fellow MVP Andrew Pruski.
But we still can use our base OS directories…really!
All isn’t lost if you’re running Linux containers on a Mac and need to run SQL Server to access the base OS’s file system. We can still use Docker Volumes for other parts of the container. Let’s create a container using TWO Docker Volumes. Let’s define sqldata1 as using the file system inside the Docker VM and we’ll define a second Docker Volume that we can use to read/write other information…like backups.
docker run \
--name 'sql19dv1' \
-e 'ACCEPT_EULA=Y' -e 'MSSQL_SA_PASSWORD='$PASSWORD \
-p 1433:1433 \
-v sqldata1:/var/opt/mssql \
-v /Users/demo/demos/backup:/backup \
-d mcr.microsoft.com/mssql/server:2019-latest
In this configuration our SQL instance will use sqldata1 mapped to /var/opt/mssql for its instance data directories inside the Docker Linux VM so SQL is able to open the files with the appropriate file modes. But we can still read/write information directly to our base OS in the directory /Users/demo/demos/backup which is mapped into the container at the location /backup. Backup files do not use the O_DIRECT flag.
Now let’s run a backup of our database to that location.
sqlcmd -S localhost,1433 -U sa -Q "BACKUP DATABASE [TestDB1] TO DISK = '/backup/TestDB1.bak'" -P $PASSWORD -W
And if we look at that directory on the base operating system we’ll see the databases backup outside the container. That’s cool. Now your automatic backups of your workstations can pick up that file and back it up into the cloud for you…right?
ls -la /Users/demo/demos/backup total 6504 drwxr-xr-x 3 demo staff 96 Sep 1 10:04 . drwxr-xr-x 5 demo staff 160 Sep 1 09:48 .. -rw-r----- 1 demo staff 3330048 Sep 1 10:03 TestDB1.bak
Let’s do something cool…
We can share that /backup volume with other containers on our system. With the container sql19dv1 still running we can start up another container, sql19dv2. We’ll need to ensure this container has a unique name, unique port to listen on and a unique Volume for the instance’s files. The only thing it’s going to share is the backup volume. This technique isn’t specific to containers macOS. This will work on Windows and Linux as well.
docker run \
--name 'sql19dv2' \
-e 'ACCEPT_EULA=Y' -e 'MSSQL_SA_PASSWORD='$PASSWORD \
-p 1432:1433 \
-v sqldata2:/var/opt/mssql \
-v /Users/demo/demos/backup:/backup \
-d mcr.microsoft.com/mssql/server:2019-latest
With this container running we can execute a RESTORE statement on the backups that are on the base OS at /Users/demo/demos/backup and mapped into the container at /backup. This technique can be effective if you’re using larger data sets avoiding having to copy the backup into the container with docker cp.
sqlcmd -S localhost,1432 -U sa -Q "RESTORE DATABASE [TestDB1] FROM DISK = '/backup/TestDB1.bak'" -P $PASSWORD -W Processed 392 pages for database 'TestDB1', file 'TestDB1' on file 1. Processed 2 pages for database 'TestDB1', file 'TestDB1_log' on file 1. RESTORE DATABASE successfully processed 394 pages in 0.026 seconds (118.239 MB/sec).
Wrapping things up
In this post, we introduced being able to map a file location from the base OS into a container and use it for reading and writing data, in our examples backup files. This could be any type of data. We also learned that for SQL Server data files we still need to use the Docker Volume that’s serviced by the Linux container. We also learned how we can share a Docker Volume between containers a quick way to move backups and other data between containers without having to use docker cp.
This technique isn’t specific to containers macOS. This will work on Windows and Linux as well.