Your Docker container takes care of persisting your SQL Server database

Microsoft has a great solution for persisting your local data collected by the IoT Edge. It’s up to you to get the data in and out of your database. But you have a convenient way of storing your precious information into some kind of persistent storage.

As seen in my previous blog, it’s not that hard to deploy from the cloud and administer your SQL database locally.

But how persistent is your database actually? Can you trust Docker for taking care of your data?

In this blog, we try to answer this question.

If you followed the recipe of my previous blog, you have a running SQL Server, deployed from the cloud.

WARNING: we will try to delete stuff, please do not test this flow using a database which contains data you do not want to lose!

Your database will already contain a table which is filled with some rows.

Database location

But where is this database stored?

If we check the properties we see:

We see a pathname “/var/opt/mssql/data”. Keep in mind I deployed an SQL Server on Linux.

Does this path sound familiar? Yes.

Look at the “Container Create Options” you used when you created this module:

{
  "Env": [
    "ACCEPT_EULA=Y",
    "MSSQL_SA_PASSWORD=p@ssw0rd!Sql"
  ],
  "HostConfig": {
    "PortBindings": {
      "1433/tcp": [
        {
          "HostPort": "1401"
        }
      ]
    },
    "Mounts": [
      {
        "Type": "volume",
        "Source": "sqlVolume",
        "Target": "/var/opt/mssql"
      }
    ]
  }
}

This path was added as a Volume. So we have control of the location.

“…Volumes are the preferred mechanism for persisting data generated by and used by Docker containers…”

This sounds great. Let’s go break something!

Removing our precious module

Now remove the Edge module in the IoT Edge portal:

This will result in the removal of the Edge module locally. The SQL Server module is gone:

Even my Management studio cannot reach my SQL Server anymore:

But how about my database and the table and data within it? Is it persisted enough?

Recreating a new module

I am not able to connect to the MobyLinux VM (deployed by Docker on my device) I am using. I get some error “Video remoting was disconnected”. So I cannot check out the existance of the database files…

So let’s assume the files are still there.

But I want to be sure any Sql Server reference is removed. I executed “docker rmi microsoft/mssql-server-linux:2017-latest” so there is no knowledge about SQL Server on my machine left anymore.

Note: I am now counting on that Docker Volume behavior with the Linux VM containing my database!

So I an now ready to add a new Edge module to my IoT Edge with a new name but with the same settings:

I name it sqlServer2 for the image “microsoft/mssql-server-linux:2017-latest” with the same settings (same volume location):

{
  "Env": [
    "ACCEPT_EULA=Y",
    "MSSQL_SA_PASSWORD=p@ssw0rd!Sql"
  ],
  "HostConfig": {
    "PortBindings": {
      "1433/tcp": [
        {
          "HostPort": "1401"
        }
      ]
    },
    "Mounts": [
      {
        "Type": "volume",
        "Source": "sqlVolume",
        "Target": "/var/opt/mssql"
      }
    ]
  }
}

The gateway will now download SQL Server again on my machine.

Will the persisted data survive this harsh conditions? How do we reattach the database?

Checking out what is left behind

The image will be redeployed again in a Docker container:

Now try to connect to the server using the Management Studio.

This is actually working. And what’s even better, the database is attached automatically from the Linux Volume location:

Let’s check the data in the table…

The data is still there.

I think this is enough proof that you are covered by Docker to keep your data persisted.

Only when you actually remove the Linux VM or if you remove the actual files or if you drop the database, you will lose your database.

Conclusion

By using the standard Docker Volume behavior, Microsoft offers a good way to collect and persist your telemetry or aggregations.