Mssql-docker: [Question] Persistent data container

[matjazmav]

This goes for microsoft/mssql-server-linux.

Can you explain why there two directories:

1. /opt/mssql/data: This one is exposed as volume, so we can share data between host and container.
2. /var/opt/mssql/data: This one actually holds database data *.mdf & .1df files.

Then how can I create persistent data container?
Lets say I'm running brand new container, no custom database yet. Then my .NET application connect to SQL Server running in container, Entity Framework will push all migrations and create database. Now we get two new files in /var/opt/mssql/data, <DBName>.mdf and <DBName>.ldf. Because new database isn't created in /opt/mssql/data database is lost if container is stopped.

Shouldn't /var/opt/mssql/data be exposed as volume?

Is storing database in *.mdf file good practice for production use?

[mystygage]

You can create a persistent data container with the image itself.
First create the data container and with volume /var/opt/mssql:

docker create -v /var/opt/mssql --name linuxsqldata microsoft/mssql-server-linux

Then mount the volumes from this container:

docker run -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=yourStrong(!)Password' \
    -p 1433:1433 -d \
    --volumes-from linuxsqldata \
    microsoft/mssql-server-linux

Now you can start and stop your container without losing your data. Additionally you can docker cp a backup or the *.mdf and *.ldf files into this volume.

[twright-msft]

You can either create a data container as @mystygage suggested or you can use docker run -v to mount a host directory to map to the /var/opt/mssql/data directory in the container.
Example:

docker run -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=yourStrong(!)Password' \
    -p 1433:1433 -d \
    -v /my/host/data/directory:/var/opt/mssql/data \
    microsoft/mssql-server-linux

More information:
https://docs.docker.com/engine/tutorials/dockervolumes/

We have removed the /opt/msssql/data volume for the next release of the mssql-server-linux image to avoid confusion.

[mystygage]

@twright-msft sorry, forget to mention this easy way, because on macOS I have to create the data container.

[twright-msft]

@mystygage - Good point! Using docker run -vdoes not currently work on Docker for Mac for the mssql-server-linux image.

[sg1970]

@twright-msft : do the same steps work for SQL Server 2016 Image for Windows Containers also ?

[twright-msft]

@sg1970 Yes, but see the special notes about Windows containers here:
https://docs.docker.com/engine/tutorials/dockervolumes/#/mount-a-host-directory-as-a-data-volume

[Gorbush]

I am struggling with the following error while trying to make persistent container when attaching database: (MacOS , Docker Toolbox with VirtualBox )
2017-02-15 17:12:28.09 spid52 FCB::Open failed: Could not open file /opt/mssql/data/bidbdat.mdf for file number 0. OS error: 87(The parameter is incorrect.).

At the same time - if i open terminal from Kitematic - no issue echoing to the file...

[Gorbush]

And if i map the whole data folder - it is failing with another error - but right on start and even fails to start:

Set up SA PASSWERD
This is an evaluation version. There are [118] days left in the evaluation period.
sqlservr: Debugger.cpp:531: static void Debugger::Print(const void , unsigned int): Assertion `m_IsAttached' failed.
Capturing core dump and information for sqlservr (pid 9)...
cat: /proc/9/sched: No such file or directory
cat: core.sqlservr.9.temp/log/debug.log: No such file or directory
No journal files were found.
No journal files were found.
ls: cannot access '/var/opt/mssql/log/log/errorlog': No such file or directory
ls: cannot access '/var/opt/mssql/log/log/exception.log': No such file or directory
ls: cannot access '/var/opt/mssql/log/log/SQLDu.txt': No such file or directory
ls: cannot access '/var/opt/mssql/log/log/SQLDu.mdmp': No such file or directory
Packaging core dump and information into compressed files.
Core dump and information will be written to /var/opt/mssql/log/core.sqlservr.02_15_2017_17_26_06.9 and logs in /var/opt/mssql/log/core.sqlservr.02_15_2017_17_26_06.9.log
ls: cannot access '/var/opt/mssql/log/log/system_health*': No such file or directory

[Gorbush]

If i map only the data folder, i got this:
et up SA PASSWERD
This is an evaluation version. There are [118] days left in the evaluation period.
2017-02-15 17:27:32.48 Server Microsoft SQL Server vNext (CTP1.1) - 14.0.100.187 (X64)
Dec 10 2016 02:51:11
Copyright (C) 2016 Microsoft Corporation. All rights reserved.
on Linux (Ubuntu 16.04.1 LTS)
2017-02-15 17:27:32.48 Server UTC adjustment: 0:00
2017-02-15 17:27:32.48 Server (c) Microsoft Corporation.
2017-02-15 17:27:32.48 Server All rights reserved.
2017-02-15 17:27:32.48 Server Server process ID is 4116.
2017-02-15 17:27:32.49 Server Logging SQL Server messages in file 'C:\var\opt\mssql\log\errorlog'.
2017-02-15 17:27:32.49 Server Registry startup parameters:
-d C:\var\opt\mssql\data\master.mdf
-l C:\var\opt\mssql\data\mastlog.ldf
-e C:\var\opt\mssql\log\errorlog
2017-02-15 17:27:32.51 Server Error: 17113, Severity: 16, State: 1.
2017-02-15 17:27:32.51 Server Error 87(The parameter is incorrect.) occurred while opening file 'C:\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.
2017-02-15 17:27:32.52 Server SQL Server shutdown has been initiated

[mystygage]

I used Docker for Mac, but I think this makes no difference with volumes. How do you create the data volume? Based on the same image like mentioned in my comment above?

[Gorbush]

No - i used the image https://github.com/moisei/mssql-server-linux - however the only difference i see is entrypoint script

[akolnati]

@Gorbush i get the same error. Currently creating a data container as @mystygage suggested

[mystygage]

@OpsSingular if you prefer a data volume instead of a data container, here is a gist for that:
https://gist.github.com/mystygage/b41bbfceeeb6dc8ab0f160485186061c