Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Docker for Windows: SQL Server Database on persistent Volume with Windows-Container

On Docker for Windows and working with windows containers, I cannot get my persistent volume to work on the main database directory of the windows container. This would be C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA How can I get the benefits of persistent volumes for databases without having to mess with backups and restores into the mssql-server-container?

This may be because of the data directory having the master- and system-dbs stored inside this folder where I try to mount the persistent volume.

In SQL Server for linux containers this simply works, you can connect the persistent volume to /var/opt/mssql and have your database persistent.

I know I can recover a database from a backup into the container, but this has two major drawbacks: I have to have a big container size because I am working with a big database. So I extended the 20 GB limit of the container to 60 GB but... rebuilding the database each time from a backup is time consuming. The second drawback is, if the mssql-dev container is killed, the database is lost, too. Any work on this database is then gone. This would be different if the database could reside on the persistent volume.

docker run -d -e sa_password=<Password> -e ACCEPT_EULA=Y -v "C:\mylocalfolder:C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA" microsoft/mssql-server-windows-developer

The error message is 'failure in a Windows system call: the virtual computer or container was shutdown unexpectetly. (0xc0370106)

Workaround 1

connect persistent volume to another location like c:\mydata to prevent the error message from above. Then get the database connected to the server while not using the standard database folder.

Extract database .bak file, so there are mdf and log files

--Get the name of your DB
RESTORE FILELISTONLY
FROM DISK = 'c:\mydata'
GO
--do the extraction of the bak file to certain folder
RESTORE DATABASE mydatabase
FROM DISK = 'c:\mydata'
WITH REPLACE,
MOVE 'mydatabase' TO 'c:\mydata\extractedDb.mdf',
MOVE 'mydatabase_log' TO 'c:\mydata\extractedLog.ldf'
GO

With this done you should now have your database files ready on your persistent volume. Now attach the database to the server. This has to be done by creating a new db but this procedure only takes nanoseconds to complete!

CREATE DATABASE StackoverflowIsGreat
ON (FILENAME = 'c:\mydata\extractedDb.mdf'),
(FILENAME = 'c:\mydata\extractedLog.ldf')
FOR ATTACH;

Now the database is safe in a persistent volume. If the db-server container goes down or is rebuild, you simply run this last statement again (or even better implement it in your docker-compose or dockerfile):

CREATE DATABASE StackoverflowIsGreat
ON (FILENAME = 'c:\mydata\extractedDb.mdf'),
(FILENAME = 'c:\mydata\extractedLog.ldf')
FOR ATTACH;

Workaround 2

the -attach_dbs parameter seems to work the same way. Docker run:

docker run -p 1433:1433 --name mssql-dev -e sa_password=<yourpassword> -e ACCEPT_EULA=Y -e attach_dbs="[{'dbName':'PowerSlide_SQLDB','dbFiles':['C:\\your\\path\\database.mdf','C:\\sqldata\\databaselog.ldf']}]" -v "d:\sqldata:C:\sqldata" microsoft/mssql-server-windows-developer

or if you prefer Docker-Compose, it is a little bit tricky. I had to omit the leading and closing ' outside of the brackets and replace the double quotation marks inside the brackets with ' to make it work.

version: '3.2'
services:
  mssql-dev:
    container_name: mssql-dev
    image: 'microsoft/mssql-server-windows-developer'
    volumes:
      - "d:\\sqldata:C:\\sqldata"
    ports:
      - "1433:1433"
    restart: always
    environment:
      - "ACCEPT_EULA=Y"
      - "sa_password=yourpassword"
      - attach_dbs=[{"dbName":"<yourDbName>","dbFiles":["C:\\<your>\\path\\database.mdf","C:\\your\\path\\databaselog.ldf"]}]
volumes:
  mssql-dev-data:
like image 206
devEd Avatar asked Aug 28 '19 08:08

devEd


People also ask

Does SQL Server in a container support persisting data?

Persist your data. Your SQL Server configuration changes and database files are persisted in the container even if you restart the container with docker stop and docker start .

Which docker allows containers to run with persistent volumes?

Docker has an option to allow specific folders in a container to be mapped to the normal filesystem on the host. This allows us to have data in the container without making the data part of the Docker image, and without being bound to AUFS.

How do I run a SQL container in Docker?

Here are the steps you can follow to set up and deploy a SQL Server Docker Container seamlessly: SQL Server Docker Setup: Install Docker on your System. SQL Server Docker Setup: Execute and Run Docker. SQL Server Docker Setup: Pull & Run the Docker Image for SQL Server.

Do Docker containers have persistent storage?

Docker has two options for containers to store files on the host machine, so that the files are persisted even after the container stops: volumes, and bind mounts. Docker also supports containers storing files in-memory on the host machine. Such files are not persisted.

How to manage data in Docker containers for SQL Server?

For SQL Server, it is critical that you understand data persistence in Docker. In addition to the discussion in this section, see Docker's documentation on how to manage data in Docker containers. The first option is to mount a directory on your host as a data volume in your container.

How do I remove a data volume from a docker container?

Even if you stop and remove this container, the data volume persists. You can view it with the docker volume ls command. If you then create another container with the same volume name, the new container uses the same SQL Server data contained in the volume. To remove a data volume container, use the docker volume rm command.

What happens if I delete a SQL Server data volume container?

If you delete the data volume container, any SQL Server data in the container is permanently deleted. In addition to these container techniques, you can also use standard SQL Server backup and restore techniques. You can use backup files to protect your data or to move the data to another SQL Server instance.

What is persistent storage of Docker?

However, with the help of persistent storage of Docker which is commonly known as Volume, the problem is actually resolved. Let us a demonstration of the same.


1 Answers

It seems this question can be answered with workaround 1 and 2 from above.

  1. Connect persistent volume to another location like c:\mydata to prevent the error message from above. Then get the database connected to the server while not using the standard database folder.
  2. Extract database .bak file, so there are mdf and log files
--Get the name of your DB
RESTORE FILELISTONLY
FROM DISK = 'c:\mydata'
GO
--do the extraction of the bak file to certain folder
RESTORE DATABASE mydatabase
FROM DISK = 'c:\mydata'
WITH REPLACE,
MOVE 'mydatabase' TO 'c:\mydata\extractedDb.mdf',
MOVE 'mydatabase_log' TO 'c:\mydata\extractedLog.ldf'
GO
  1. Attach the database to the server in one of the following three ways:

Docker run example:

docker run -p 1433:1433 --name mssql-dev -e sa_password=<yourpassword> -e ACCEPT_EULA=Y -e attach_dbs="[{'dbName':'PowerSlide_SQLDB','dbFiles':['C:\\your\\path\\database.mdf','C:\\sqldata\\databaselog.ldf']}]" -v "d:\sqldata:C:\sqldata" microsoft/mssql-server-windows-developer

if you prefer Docker-Compose, it is a little bit tricky. I had to omit the leading and closing ' outside of the brackets and replace the double quotation marks inside the brackets with ' to make it work. Example for docker-compose:

version: '3.2'
services:
  mssql-dev:
    container_name: mssql-dev
    image: 'microsoft/mssql-server-windows-developer'
    volumes:
      - "d:\\sqldata:C:\\sqldata"
    ports:
      - "1433:1433"
    restart: always
    environment:
      - "ACCEPT_EULA=Y"
      - "sa_password=yourpassword"
      - attach_dbs=[{"dbName":"<yourDbName>","dbFiles":["C:\\<your>\\path\\database.mdf","C:\\your\\path\\databaselog.ldf"]}]
volumes:
  mssql-dev-data:

Or attach DB with SQL Command

CREATE DATABASE StackoverflowIsGreat
ON (FILENAME = 'c:\mydata\extractedDb.mdf'),
(FILENAME = 'c:\mydata\extractedLog.ldf')
FOR ATTACH;
like image 121
devEd Avatar answered Oct 01 '22 21:10

devEd