SQL Server container does not persist data

In this post, I will demonstrate how by default docker container packages did not design to persist data when using SQL Server data Docker Container packages.
Be default data will never persist when using SQL Server docker containers. For instance all data, and customized SQL Server configuration setting will be lost when issued docker stop and docker start or docker rm (remove) commands.

Let’s demo that by pulling this “2019-CU4-ubuntu-16.04” image from docker hub, run the container, get the container id or name from docker ps -a output in order to connect to SQL Server.

First, Pull the image from Docker Hub.

# Pull the image from docker hub
docker pull mcr.microsoft.com/mssql/server:2019-CU4-ubuntu-16.04

Deploy the image you just pulled from Docker Hub as a Container services. Please remember to replace <container_name> with the right container name and password as well.

# Deploy image as container
docker run --name <container_name> -d `
-e 'ACCEPT_EULA=Y' `
-e 'SA_PASSWORD=yourStrong(!)Password' `
-p 1430:1433 `
mcr.microsoft.com/mssql/server:2019-CU4-ubuntu-16.04

Copy docker container id or name we just deployed from below output script.

docker ps -a

Replace <container_id_or_name> with the container id or name we just copied from above output script. Once done, run below script to connect to SQL Server, create database, table, insert data into the table, and select data from the table we just created.

docker exec -it <container_id_or_name> /opt/mssql-tools/bin/sqlcmd `
   -S localhost -U SA -P "yourStrong(!)Password" `
   -Q "SET NOCOUNT ON;
       CREATE DATABASE Demo_db;
       GO
       USE Demo_db;
       GO
       CREATE TABLE try_sql_docker(FieldName VARCHAR(50));
       INSERT INTO try_sql_docker 
       SELECT 'It works!!!';
       GO
       SELECT * FROM try_sql_docker;
      "

You will get below output once above execution script is completed.

Now, let’s stop and start the container service we just deployed to see if data will persist.
First, Run docker ps -a to get all containers within your environment. Copy docker container id we just deployed. Use the container id to stop and start SQL Server container service.

docker stop <container_id>
docker start <container_id>

Remember, we created a database named “Demo_db”, and a table named “try_sql_docker”. Now, Let’s connect to SQL Server, and query sys.databases to see if Demo_db will be part of the output.

docker exec -it ded6150c3d73 /opt/mssql-tools/bin/sqlcmd `
    -S localhost -U SA -P "yourStrong(!)Password" `
    -Q "SET NOCOUNT ON;
        SELECT name FROM sys.databases;
    "

No Demo_db returned from the above screenshot. Now we both agreed that by default SQL Server Docker Container did not design to persist data however they provide us ways to persist data which be found here.

Conclusions

We need to define our needs. Do we need to persist data or not. If the answer is yes, then click here to redirect you to my other posts where I explained basic and kind of advanced ways to persist data in SQL Server docker containers. Containers are rapidly becoming standard tooling for DevOps processes. DevOps process support for SQL Server is now available and on-par with the best available on Linux with MySQL or Postgres.

Feel free to comment, share and like this post.