I would certainly like to know at least what is the technical reason it doesn’t (yet) work.

Smart engineer on our team says...

SQL Server requires underlying filesystems to support the O_DIRECT file option so that we can use asynchronous I/O. It appears that Docker's OS X volume mapper doesn't support this.
We also require O_DIRECT because SQL Server expects unbuffered I/O to the disk.

BTW - you can use data container volumes for persistence when using Docker for Mac. That doesnt help you share files between the macOS host and the container but it does give you data file persistence for your DB files in /var/opt/mssql/data.

https://docs.docker.com/engine/tutorials/dockervolumes/#/creating-and-mounting-a-data-volume-container

On the Mac one could do all their docker work in a Linux VM and get around the limitations of the Mac file system.

Thinking out loud here: would a compliant file system using FUSE (not sure this even works on OS X) on OS X work? You could use a file system on this FUSE thing to pass the -v for mappings. Maybe ZFS on OSX? https://openzfsonosx.org/

@twright-msft Thanks for the suggestion about Data Container Volumes, and the link.

I had trouble getting this to work, as I was unclear about the correct syntax.

The following worked well:

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

docker run -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=Test@123' -p 1433:1433 --volumes-from mssql -d --name sql-server microsoft/mssql-server-linux

This worked through successive restarts of Docker, so this is a workable solution.

Thanks a lot folks! This thread is very helpful!

For those using docker-compose, I created a gist to make it work with your other services.

https://adilsoncarvalho.com/using-mssql-on-linux-using-docker-for-mac-a5d4ac81e57f

checking in ...is there any movement on this issue? Thanks.

@julielerman Sorry, no, not yet. We're focusing on some of the other issues at the moment having to do with configuration of SQL Server in containers. Targeting CTP 2.1 for those improvements and then we'll take a look at the next batch of issues. This is certainly one of the higher priority issues.

I spent hours last night (being a docker newbie and all) trying different combinations.

I can confirm that creating a "data volume container", and then using the --volumes-from parameter works fine.

I can also confirm that the (new, as of Docker 1.9+?) option of creating a plain "data volume" works. You can either pre-create it using docker volume create myvolname and then using the -v myvolname:/var/opt/mssql option when running docker run..., or you can go ahead and just use -v myvolname:/var/opt/mssql and it will create a volume "on the spot" if it doesn't exist.
Then you can re-use/share that volume with other containers.

Now, one thing that baffles me is that everybody is talking about the need to have persistent volumes because when you stop your containers everything will be lost. However, in my experiments with the mssql linux image/containers, I can stop and start and restart the container all I want, and my filesystem changes are still there (of course they won't be there if I remove the container, but nobody is expecting that.) Do I have something "different" in my Docker setup? Is this a "feature" of Docker for Mac? Has Docker changed its behaviour in the latest versions? (I'm using the latest Docker for Mac Community Edition Version 17.03.1-ce-mac5 (16048))

@barspi - you have a correct understanding. You can stop/start/restart your containers all day long and as long as you don't remove the container the changes made inside the container will remain. It's like a VHD file. You can stop/start/restart the VM all day long and all the changes will be persisted in the VHD file, but if you delete the VHD file then all is lost.

The reason that people make such a big deal about persistence is that historically a few years ago containers didnt have persistence at all. They were designed to be completely ephemeral.

It also just comes from a bit of paranoia about people that dont know what they are doing potentially losing data. Just like most things in life there are risks if you dont know what you are doing. :)

I had the same problem running a microsoft/mssql-server-linux Docker image (Sql Server 2017) container on a Ubuntu 16.06 VirtualBox VM host.

When Docker command with a volume param is run

docker run -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=<YourStrong!Passw0rd>' -p 1433:1433 -v /var/opt/mssql:/var/opt/mssql -d microsoft/mssql-server-linux

Execution will get stuck at Recovery is complete. This is an informational message only. No user action is required. and won't move forward to completion. Using data container volumes as suggested by @twright-msft solved the problem.

In summary, this problem is there in Linux environment as well.

@mysticmind - Can you please tell me more about your environment? Were you running macOS with Virtual Box on it and then an Ubuntu VM on Virtual Box with Docker Engine inside of it?

@twright-msft Windows 10 running a VirtualBox Ubuntu 16.04 VM with Docker inside it

@twright-msft Is this still an issue in GA?

@kspearrin - Yes, it is likely still a problem.

Could this be documented on Docker Hub to prevent duplicate bugs being raised?

it's still an issue in 2017-latest.

create a docker volume or data container volume works as charm, but not a final solution, is there any plan to solve it?

2018, any news at this? love feedback

Is O_DIRECT the same thing missing in ZFS which prevents using ZFS filesystems as volumes #13 ?

Based on a discussion with @julielerman, I thought I'd post some guidelines I give to my students and clients around this issue. The TL;DR is:

Don't use bind-mounts on Docker Desktop (macOS/Windows) for database persistence. It's an anti-pattern.

In essence, you're asking the database engine in the container to span operating systems to a remote file system, which at best will be much slower, and at worst just won't work. mssql isn't alone here. I've seen various disk I/O issues and container startup issues for most other sql, nosql, and key/value storage systems when trying to use bind-mounts on macOS and Windows. Sometimes it's a file permissions mis-match, sometimes it's disk driver I/O features, and others are just performance related.

If you're on macOS, I recommend you get savy on the Docker for Mac file sharing features/concerns, as well as how to performance tune it. On Windows, it's much worse because I believe it still uses SMB networking protocols to bind-mount, which almost never works with db files.

I don't think we should expect Docker Desktop to solve all those low level OS issues, and I've helped my clients ensure they don't need a workflow for local test/dev that requires mounting a db file on host into a running db container. In all cases so far it was just a workflow issue that had a different solution.

Methods I've seen used:

Yes named volumes work great, and is what should be used to store db's. The challange usually comes when people want 1. sample/seed data or 2. a large database that they don't want to re-import.

For sample/seed data, that should be part of a custom ENTRYPOINT script that you put in compose files just for local development. This way you customize the local dev startup expirence without changing the Dockerfile. The script can/should be smart enough to detect if the data needs to be re-imported on container startup. The seed data gets stored in the repo with code usually so its schema can be matched to db schema changes. The mssql image doesn't have a default ENTRYPOINT script yet, but you can see great examples on other database images, like the mysql official image, where it will create users, set passwords, and more during container startup, and then restart the database in the CMD stanza.

For large datasets where you don't want the delay of import every time, you'll need to get your pre-created files into the container. Options include:

1. Once the container is running with the named volume, you can use docker cp to copy the files from host path to container path where you've mounted the named volume. To my knowledge, this can't be automated (that I know of) for easy local development setup with docker-compose up, which is always my goal for new dev environment setup.

2. My prefered if you have multi-gig db files: You could still bind-mount the directory with data in it on host, but the goal isn't to mount those files directly in mssql, but to rather have a ENTRYPOINT script that checks for their existance in the volume location, and if missing, copy them over from bind-mount location, before passing control to CMD where they'll get mounted by mssql. Sorry I don't have an example, but it should just be a few lines of bash to get working.

Note that none of this is recommended for servers or production obviously, but then those setups won't have bind-mount issues across OS's either.

I hope that helps.

Same issue happens when running in the latest docker with kubernetes enabled and trying to run mssql through the stable helm chart at https://github.com/helm/charts/tree/master/stable/mssql-linux

Almost 2 year, can we have a solution for this issue in next few months?

Same issue happens when running in the latest docker with kubernetes enabled and trying to run mssql through the stable helm chart at https://github.com/helm/charts/tree/master/stable/mssql-linux

I met the same problem, is there any solution here?

Another issue to consider is performance for volumes in docker on Windows and Mac are significantly reduced and prone to issues for database files.

I'd suggest docker cp or a volume mount for backup/restore only.

Any status update about this?
As mentioned above people are paranoia want to place data files out of container I really disagree. It is a design of a container to not have any persistent data and creating any new version by kick and update it.
As it seems this won't be solved soon I think it would be much help for backup/restore and automate test scenarios if we have a /data/backup folder inside the docker container we can mount then.

We know that container store mdf and ldf files in /var/opt/mssql.
So how about have a /var/opt/mssql_backup folder that we can use to mount it outside the VM.

Then we could easy use

• copy file to target
• sqlcmd usage to restore db
• backup database and save to file location of our choice

Or is this problem solved soon?

Doesn't this workaround for o_direct work on osx too? https://github.com/t-oster/mssql-docker-zfs no guarantees about side effects through, but I used this successfully on zfs/Linux until o_direct was officially supported.

Doesn't this workaround for o_direct work on osx too? https://github.com/t-oster/mssql-docker-zfs no guarantees about side effects through, but I used this successfully on zfs/Linux until o_direct was officially supported.

Didn't try so far, you use this more or less stable :)?

I did use it in production without problems. But since zfs now works without this workaround, I don't need it anymore

still happening in 2020 on macos mojave with docker v2.0.0.3 when trying to create a sql server 2019 container. log extract is as follows:
This container is running as user mssql.
Your master database file is owned by mssql.
To learn more visit https://go.microsoft.com/fwlink/?linkid=2099216.
2020-06-23 19:32:06.01 Server Setup step is FORCE copying system data file 'C:\templatedata\model_replicatedmaster.mdf' to '/var/opt/mssql/data/model_replicatedmaster.mdf'.
2020-06-23 19:32:06.83 Server Setup step is FORCE copying system data file 'C:\templatedata\model_replicatedmaster.ldf' to '/var/opt/mssql/data/model_replicatedmaster.ldf'.
2020-06-23 19:32:07.01 Server Setup step is FORCE copying system data file 'C:\templatedata\model_msdbdata.mdf' to '/var/opt/mssql/data/model_msdbdata.mdf'.
2020-06-23 19:32:07.37 Server Setup step is FORCE copying system data file 'C:\templatedata\model_msdblog.ldf' to '/var/opt/mssql/data/model_msdblog.ldf'.
2020-06-23 19:32:08.72 Server Microsoft SQL Server 2019 (RTM-CU5) (KB4552255) - 15.0.4043.16 (X64)
Jun 10 2020 18:25:25
Copyright (C) 2019 Microsoft Corporation
Developer Edition (64-bit) on Linux (Ubuntu 18.04.4 LTS)
2020-06-23 19:32:08.72 Server UTC adjustment: 0:00
2020-06-23 19:32:08.72 Server (c) Microsoft Corporation.
2020-06-23 19:32:08.72 Server All rights reserved.
2020-06-23 19:32:08.72 Server Server process ID is 40.
2020-06-23 19:32:08.72 Server Logging SQL Server messages in file '/var/opt/mssql/log/errorlog'.
2020-06-23 19:32:08.73 Server Registry startup parameters:
-d /var/opt/mssql/data/master.mdf
-l /var/opt/mssql/data/mastlog.ldf
-e /var/opt/mssql/log/errorlog
2020-06-23 19:32:08.74 Server Error: 17113, Severity: 16, State: 1.
2020-06-23 19:32:08.74 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.

I recently had a similar problem with 2019 container started on an empty volume. But then I started 2017, which created the database in the mapped volume and after it started up successfully, I used the 2019 container with the same volume.This did then update the existing files and is running well since then. I thought they changed the way you have to start the container the first time, but I didn't investigate further

I guess they now have prepared files in the /var/opt/mssql/data folder which are copied by docker if you create a named volume but not if you use a mapped folder. Try to run a container and map a folder in /tmp/data, docker exec bash and copy the folder...