Permanent volume for sql 2019 data?

General
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?

7

I saw this article. he simply used volumes_from: -mssqldata. is that a shortcut and working?

8

That is just overcomplication. I never understood why people used a container as a volume that way. You can just create a volume which is actually persistent so you can remove containers and the volumes will still exist.

The tutorial you shaared just used another container’s anonymous volumes based on the same image so he could have just use the mssql container’s anonymous volume and make sure he don’t remove the container instead of keeping another container so he can’t just run docker compose down anyway.

The image in the tutorial doesn’t even exist enymore. Maybe that version had volumes, but it seems mcr.microsoft.com/mssql/server:2019-latest doesn’t. Many image providers realize if they define an anonymous volume in the image definition, that can’t be removed and you can very easily define the volume when you start the container

volumes:
  mssqldata:

services:
  mssql:
    volumes:
      - mssqldata:/var/opt/mssql

But that is just a default local volume which could be lost when you delete other volumes and acidentally remove one you didn’t want to so I either use remote volumes making sure the actual data can’t be removed or bind mounts so then I have to set proper permissions and ownerships before mounting the folder


Well, not really. There is a bind mount and an anonymous volume as well, so I really don’t get why that extra mssqldata container was necessary and I don’t see the tutorial explaining it.

1 Like