Mssql-docker: Cannot create database snapshots on microsoft/mssql-server-windows-developer

[KristianFenn]

Repro Steps:

Run a new image of the docker database:
docker run -d -p 1433:1433 -e sa_password=TestDb123 -e ACCEPT_EULA=Y microsoft/mssql-server-windows-developer

Run the following commands as the sa user:
CREATE DATABASE TestDatabase

CREATE DATABASE TestDatabase_Snapshot ON ( NAME = TestDatabase, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\TestDatabase_Snapshot.ss' ) AS SNAPSHOT OF TestDatabase;

Produces the following error:
Msg 1823, Level 16, State 2, Line 3 A database snapshot cannot be created because it failed to start. Msg 5120, Level 16, State 104, Line 3 Unable to open the physical file "C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\TestDatabase_Snapshot.ss". Operating system error 50: "50(The request is not supported.)".

I've tried creating a backup into the data directory and that works fine, indicating it's not permissions. I've searched low and high to find out if this is a SQL server misconfiguration, but it doesn't appear to be.

[maliger]

Same error here.
Maybe Transacted IO which is not supported under containers?

[Rora]

Is there any update or workaround on this? This issue prevents us from running our integration- and specification tests while using windows containers

[KristianFenn]

We're currently working around this issue by using a data volume as a place to store snapshot files.

We have the following at the end of our dockerfile:

VOLUME ["C:/snapshot"]

And then you can use this directory when creating a snapshot:

CREATE DATABASE TestDatabase_Snapshot ON ( NAME = TestDatabase, FILENAME = 'C:\snapshot\TestDatabase_Snapshot.ss' ) AS SNAPSHOT OF TestDatabase

It seems whatever isn't supported by the virtual filesystem in the container can be done on the host machine via a volume, so this may be an issue in the underlying platform rather than this container.

[devnomadic]

I am experiencing the same issue and using the work around create snapshots on a data volume. I dug a bit deeper using procmon and it looks like the virtual filesystem does not support FSCTL_QUERY_ALLOCATED_RANGES when calling DeviceIoControl()

SQL Logs

03/29/2019 11:43:50,spid52,Unknown,FCB::Open failed: Could not open file C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\master.mdf_MSSQL_DBCC5 for file number 1. OS error: 50(The request is not supported.). 03/29/2019 11:43:50,spid52,Unknown,Error: 17204<c/> Severity: 16<c/> State: 1. 03/29/2019 11:43:50,spid52,Unknown,SidePageTable::Init() DeviceIoControl() : Operating system error 50(The request is not supported.) encountered. 03/29/2019 11:43:50,spid52,Unknown,Error: 17053<c/> Severity: 16<c/> State: 1.

Procmon Logs

Tested on Windows10 & Server2016 with both process & hyperv isolation modes. Same result.

[rughvi]

The container shuts down immediately. Running the command

docker run -d -p 1433:1433 -e sa_password=TestDb123 -e ACCEPT_EULA=Y microsoft/mssql-server-windows-developer

But in the event viewer Windows Logs -> Application I can see error :

hcsshim::ComputeSystem::CreateProcess - End Operation - Error [cid=f07b4d3babaf3866476633d5389790e0a643dca7c8ebf0eb8097cb97b28202fc error=CreateProcess f07b4d3babaf3866476633d5389790e0a643dca7c8ebf0eb8097cb97b28202fc: The user name or password is incorrect.
[Event Detail: Provider: 00000000-0000-0000-0000-000000000000]
(extra info: {"CommandLine":"cmd.exe /C \"ECHO 192.168.0.33 host.docker.internal \u003e\u003e %systemroot%\system32\drivers\etc\hosts \u0026 ECHO 192.168.0.33 gateway.docker.internal \u003e\u003e %systemroot%\system32\drivers\etc\hosts\"","User":"Administrator","WorkingDirectory":"C:\","Environment":{"ACCEPT_EULA":"Y","attach_dbs":"[]","sa_password":"TestDb123","sa_password_path":"C:\ProgramData\Docker\secrets\sa-password"},"CreateStdInPipe":true,"CreateStdOutPipe":true,"CreateStdErrPipe":true,"ConsoleSize":[0,0]})]

My system is Windows 10 Enterprise 18362 , Docker version 19.03.5 Windows Containers.
Any help is greatly appreciated.