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:
Database Location
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:
Remove the Edge module
This will result in the removal of the Edge module locally. The SQL Server module is gone:
SQL Server
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.
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"
}
]
}
}
sqlServer2
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:
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:
PersistedEdgeDB
Let’s check the data in the table…
Check the data
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.
About the Author:
I’ve been presented with the 2017 Microsoft Most Valuable Professional (MVP) Award and I’m a member of the Microsoft Azure Advisory Board. I started as an IT consultant in 1993. I like to get my hands dirty with software innovations and I try to implement these in my daily work. Currently, I am involved in the IoT Platform part of Azure (eg. IoTHubs, StreamAnalytics, Azure Functions, Mobile Apps, EventHubs, Universal Apps) and Azure in general. For me, it is important to share knowledge. And I am committed doing so by writing blogs, articles for magazines and giving lots of presentations.
When offline, I like cutting down trees using Gränsfors Bruks axes, sailing, motorcycling or geocaching with my wife and my sons.
Website: sander@vandevelde-online.com. LinkedIn: https://nl.linkedin.com/in/sandervandevelde Twitter: @svelde
Reference:
Van de Velde, S (2018). Your Docker container takes care of persisting your SQL Server database. Available at: https://sandervandevelde.wordpress.com/2018/06/12/your-docker-container-takes-care-of-your-sql-server-database/. [Accessed 1 August 2018]
