Permanent volume for sql 2019 data?

General Discussions
#1

Hello,

I have a windows container with sql 2019. At the moment the data is lost when the container is removed.
How can i make a permanent volume and tell SQL2019 to save data on this volume?

Johan

#2

Hello Johan,
my docker-compose.yml looks like this

version: '3.2'

services:
  mssql:
    image: mcr.microsoft.com/mssql/server:2019-latest
    restart: unless-stopped
    ports:
      - "1433:1433"
    environment:
      - ACCEPT_EULA=Y
      - SA_PASSWORD=secretpassword
      - TZ=Europe/Berlin
      - MSSQL_PID=Express
    volumes:
      - "./data/:/var/opt/mssql/data/"
      - "./log/:/var/opt/mssql/log/"
      - "./secrets/:/var/opt/mssql/secrets/"

which stores all data outside the container on the docker-server’s local harddrive.

The docker run-command looks like this:
docker run -e 'ACCEPT_EULA=Y' -e 'MSSQL_SA_PASSWORD=<YourStrong!Passw0rd>' -p 1433:1433 -v <host directory>/data:/var/opt/mssql/data -v <host directory>/log:/var/opt/mssql/log -v <host directory>/secrets:/var/opt/mssql/secrets -d mcr.microsoft.com/mssql/server:2019-latest

On Configure and customize SQL Server Docker containers - SQL Server | Microsoft Docs you can also find an example for using a volume for the container’s persistent data.

#3

Hello Matthias,

Thanks for your reply. Your image is based on a Linux Container and not Windows Container.
I am running a Windows 2019 Host server. As there is no ready made sqlserver image for windows i had to create one myself. The questions i am having now is:

  1. How can make a permanent volume to my Windows 2019?
  2. And how can i tell MSSQL to save data on this permanent volume?

See below my current dockerfile

# Specifying Container Image
FROM mcr.microsoft.com/windows/servercore:ltsc2019

EXPOSE 1433

#Copy MSSQL SETUP FILES INTO THE CONTAINE
COPY ./setup/SQL2019STDx64 c:/Setup 
COPY ./mssql2019/configurationfile.ini c:/Setup

# Install DOTNET AND MSSQL 2019

RUN powershell.exe -command \
install-windowsfeature -name NET-Framework-Features -IncludeAllSubFeature -Source c:/Setup/sxs; \
c:/Setup/setup.exe  /configurationfile=c:/Setup/configurationfile.ini; \  
sc.exe config MSSQLSERVER obj=LocalSystem
#4

It is true, but the answer would be the same for Windows containers too. Did you have any error messag when you tried that?

You can uses volumes or “bind mount” (I am not sure if this is how you call it on Windows) a host folder to the container. The only difference I can think of is how you refer to a folder. On Linux, you use Linux paths, on Windows, you use Windows paths like this running in PowerShell:

docker run --rm -it "$(pwd)/data:C:/test" IMAGENAME

I am also not sure about the valid reference in Docker compose but I guess it would work:

- ./data:/C:/test

If your target folder is the one where MSSQL stores its data, you don’t need to tell anything to MSSQL. If it is an other folder for some reason, then it is not something that Docker will do for you. You need to know how to configure MSSQL server to change the default data folder.

#5

Sorry guys, have been a little busy these days. Matthias i tried your script on my WIndows 2019 to get the following error:

Error response from daemon: failed to start service utility VM (createreadwrite): hcsshim::CreateComputeSystem f836c97c17906140f99ac55634f36e9847f19cf71864cb4563acc346ce0fdb43_svm: The virtual machine could not start because a required component is not installed.

#6

Checked in my Win10-machine running Docker with WSL2-integration and my docker-compose.yml was working as expected. In the BIOS the Virtualization-flag is enabled. As I used the Hyper-V-Backend (without WSL2) in earlier tests this feature is installed on my machine, too.

Maybe you can check if virtualization is enabled within your computer’s BIOS and if other (linux-based) images can run on your Win2019-machine?