Maintaining persistence of data after removing SQL server container

Windows Server 2016
1

Hi Everyone,

I need a few help on the below mentioned issue.

I am creating a container for SQL server image on windows. I created a sample test database on the SQL server. After I restart or quit from the docker the container gets exited and the SQL data gets lost.

How can I maintain the persistence of the SQL database and table even after restarting or removing the docker container.

Steps to reproduce the behavior

  1. Created a container and saved the backup .mdf file in local folder.

docker run --name testcontainer1 -d -p 15799:1433 -v D:\SQLServer:C:\SQLServer -e sa_password=******* -e ACCEPT_EULA=Y microsoft/mssql-server-windows-developer

  1. Created a test database and test table in the SQL container.

  2. Stopped and removed the running container with the below command

    docker stop testcontainer1
    docker rm testcontainer1

  3. Created another container,

docker run -d -p 15799:1433 -v D:\SQLServer:C:\SQLServer --env ACCEPT_EULA=Y --env sa_password=****** --name testcontainer2 microsoft/mssql-server-windows

  1. Attached the backup .mdf file inside this container and I got back the database and table.

How can I maintain the pesistence of data in SQL container when i deploy my application in a production environment even after stopping or removing the container.

2

You seemed to be doing what you need need to be doing, that is storing data outside of image and hence maintaining state outside of docker runtime. What are you asking?

3

As in production environment, its not a good practice to lose database data when the docker stops or restarts as in the current scenario I am losing the entire container and the database data created within the container image.

So is there a possibility to maintain one single SQL container with all the the database data’s and retain the same instance of the same container even after stopping or removing(i.e, using the same IP address and and the container id of the stopped or restarted container)

4

SQL 2016 on windows containers is not supported in production environment at all. There is no clean cut solution to what you are looking for without use of HAG for SQL which I saw partners demonstrated during Kubernetes demos. It’s pretty complicated setup but it worked in demos. SQL inside container is nowhere ready to be used in production in general.

5

Okay. Thank you for the help.

Regards,
Joshua.