SQL Server Demo Environment on Docker Desktop (Win 10): DB storage und upgrade paths

Question

I would like to run SQL Server 2019 on my work as well as my private machine (both running Windows 10 Pro). Thus I thought Docker Desktop would be a nice option.

However I would like to keep the docker image itself minimal (just SQL Server and system databases) and store my demo databases elsewhere. I have tried to accomplish this using a data volume mounted from the host using the following command line switch.

-v C:\SQL:/sql

CREATE DATABASE does work using FILENAME "C:\sql..."

    CREATE DATABASE [test] CONTAINMENT = NONE ON  PRIMARY 
( NAME = N'test', FILENAME = N'C:\sql\test.mdf' ,...

ATTACH does work using Database File Location "/sql/..."

USE [master]
GO
CREATE DATABASE [CustomerOrders] ON 
--( FILENAME = N'/sql/mssql/data/CustomerOrders.mdf' ), --generated by ssms -> wrong
(FILENAME = N'C:\SQL\mssql\data\CustomerOrders.mdf'),
( FILENAME = N'C:\SQL\mssql\data\CustomerOrders_log.ldf' )
 FOR ATTACH
GO

However RESTORE does only work with destination /var/opt/mssql and NOT with destination /sql/ or C:\sql

Try 1

      USE [master]
    RESTORE DATABASE [Credit] FROM  DISK = N'/sql/backup/CreditBackup100.bak' 
WITH  FILE = 1,  MOVE N'CreditData' TO N'C:\SQL\CreditData.mdf',  
MOVE N'CreditLog' TO N'C:\SQL\CreditLog.ldf',  NOUNLOAD,  STATS = 5, REPLACE

Try 2

USE [master]
RESTORE DATABASE [Credit] FROM  DISK = N'/sql/backup/CreditBackup100.bak' 
WITH  FILE = 1,  MOVE N'CreditData' TO N'/sql/CreditData.mdf',  
MOVE N'CreditLog' TO N'/sql/CreditLog.ldf',  NOUNLOAD,  STATS = 5, REPLACE

GO

Error Messages

Msg 3634, Level 16, State 1, Line 2 The operating system returned the error '2(The system cannot find the file specified.)' while attempting 'RestoreContainer::ValidateTargetForCreation' on '/sql/CreditData.mdf'. Msg 3156, Level 16, State 5, Line 2 File 'CreditData' cannot be restored to '/sql/CreditData.mdf'. Use WITH MOVE to identify a valid location for the file. Msg 3634, Level 16, State 1, Line 2 The operating system returned the error '2(The system cannot find the file specified.)' while attempting 'RestoreContainer::ValidateTargetForCreation' on '/sql/CreditLog.ldf'. Msg 3156, Level 16, State 5, Line 2 File 'CreditLog' cannot be restored to '/sql/CreditLog.ldf'. Use WITH MOVE to identify a valid location for the file. Msg 3119, Level 16, State 1, Line 2 Problems were identified while planning for the RESTORE statement. Previous messages provide details. Msg 3013, Level 16, State 1, Line 2 RESTORE DATABASE is terminating abnormally.

This one works (but is not what I want, as it is stored inside the docker image then):

USE [master]
RESTORE DATABASE [Credit] FROM  DISK = N'/sql/backup/CreditBackup100.bak' 
WITH  FILE = 1,  MOVE N'CreditData' TO N'/var/opt/mssql/CreditData.mdf',  
MOVE N'CreditLog' TO N'/var/opt/mssql/CreditLog.ldf',  NOUNLOAD,  STATS = 5, REPLACE

Another big difference is file growth on database files stored within the container and on files on the volume (resp. the host). I created a test database with a 8 MB mdf file in /var/opt/mssql/data and in /sql (my data volume). Afterwards I issued the following command

ALTER DATABASE [test2] MODIFY FILE ( NAME = N'test2', SIZE = 1048576KB )

This grows the file to 1 GB. In the location /var/opt/mssql/data it finished instantely while the resize operation took 8 seconds on the data volume /sql.

I tested for Instant File Initialization with traceflags 3004 and 3605 but didn't see any output regarding nulling the mdf file out on the data volum /sql.

Thanks in advance for your help

Martin

Answer 1

Performance of SQL Server using shares to host data files is almost always poor, regardless of the underlying storage subsystem. You should be mounting a host directory as a volume in your docker container to get better performance. See this link for more information.