Mssql-docker: Allow data directory to be mounted on tmpfs

[andrewnicols]

This _may_ possibly be solved by #10, #12, or #34, but I'm unable to tell at this stage.
It is currently not possible to use tmpfs for the data directory.

Whilst this may seem a pretty weird thing to do, it's particularly helpful when using the docker image for continuous integration testing of a project where the database is wiped away at the start or end of every run anyway. In such a scenario one of the key desirables is the speed of the run and use of a tmpfs makes a huge difference to the speed of the runs.

I've had a look at extending the standard image to facilitate this, and while I can change the default data directory to another overlaid directory, it does not seem to support being moved to tmpfs:

2010 nicols@boysenberry:~> docker run -it -e ACCEPT_EULA=Y -e SA_PASSWORD=Passw0rd! --tmpfs /var/lib/mssql:rw,exec,suid,dev --entrypoint bash microsoft/mssql-server-linux
root@b4f910c32ee2:/# mount | grep mssql
tmpfs on /var/lib/mssql type tmpfs (rw,relatime)
root@b4f910c32ee2:/# mkdir /var/lib/mssql/data
root@b4f910c32ee2:/# ls -ld /var/lib/mssql/
drwxrwxrwt 3 root root 60 Jun 16 01:35 /var/lib/mssql/
root@b4f910c32ee2:/# ls -l /var/lib/mssql/
total 0
drwxr-xr-x 2 root root 40 Jun 16 01:35 data
root@b4f910c32ee2:/# /opt/mssql/bin/mssql-conf set filelocation.defaultdatadir /var/lib/mssql/data
SQL Server needs to be restarted in order to apply this setting. Please run
'systemctl restart mssql-server.service'.
root@b4f910c32ee2:/# /opt/mssql/bin/sqlservr
This is an evaluation version.  There are [147] days left in the evaluation period.

Opening a second terminal session and trying to create a new DB:

root@b4f910c32ee2:/# /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P 'Passw0rd!' -Q "CREATE DATABASE example"
Msg 5123, Level 16, State 1, Server b4f910c32ee2, Line 1
CREATE FILE encountered operating system error 31(A device attached to the system is not functioning.) while attempting to open or create the physical file '/var/lib/mssql/data/example.mdf'.
Msg 1802, Level 16, State 4, Server b4f910c32ee2, Line 1
CREATE DATABASE failed. Some file names listed could not be created. Check related errors.

Confirmed on:

• MacOS running docker 17.06.0-ce-rc2; and
• Linux (Ubuntu) hosts running docker 17.03.0-ce.

[jdenly]

I found a workaround for this that you might find useful - if you create a loopback filesystem on top of a file on top of a tmpfs filesystem, the database will work using it.

On your docker host:

# Create a 10GB tmpfs file system.
mount -t tmpfs -o size=10G tmpfs /ramcache

# Create a disk image inside of the tmpfs.
cd /ramcache
dd if=/dev/zero of=disk.img bs=1M count=10240

# Create a new loopback device from the disk image.
losetup /dev/loop0 disk.img

# Format as ext4.
mkfs.ext4 /dev/loop0

# Mount the loopback image as /data
mount /dev/loop0 /data

Then to use it with docker, pass it as a volume:

docker run --volume /data:/var/opt/mssql/data ...

I found this drops a snapshot restore of our test database from ~10 seconds to ~2 - makes a big difference when running a lot of tests!

[asztal]

I use the following Dockerfile for running SQL Server:

FROM debian as mssql-docker-tmpfs

    ADD nodirect_open.c /
    RUN apt update && apt install -y gcc && \
        gcc -shared -fpic -o /nodirect_open.so nodirect_open.c -ldl && \
        apt purge -y gcc

FROM mcr.microsoft.com/mssql/server:2017-latest

    COPY --from=mssql-docker-tmpfs /nodirect_open.so /nodirect_open.so
    RUN echo "/nodirect_open.so" >> /etc/ld.so.preload
    ENV LD_PRELOAD=/nodirect_open.so

It's still a hack, but it works. For me, using the loop device workaround is difficult because our CI server may run multiple SQL Server containers at once, and also the CI runner doesn't have root privileges.

The original source which I got it from is here: https://github.com/t-oster/mssql-docker-zfs/blob/master/Dockerfile (note that the licence is GNU GPLv3, if you distribute images made from modified versions of the Dockerfile you must also offer to distribute the Dockerfile itself).

For me this version of SQL server starts up in 2.49 seconds, as opposed to 4.22 seconds for the official image.

(Edit: I should probably mention that this hack only works if you are not using snapshots)

[ve2m]

Any update on this? It can greatly improve the performance of integration tests. See:
https://www.stefanproell.at/2019/02/08/lightning-fast-integration-tests-with-docker-mysql-and-tmpfs/
https://vladmihalcea.com/how-to-run-integration-tests-at-warp-speed-with-docker-and-tmpfs/

The hacks above work, but it would be better to pass this responsibility to Docker without the need to solve used/unused loopback devices, creating directories, etc.