search

Azure / iotedge

The IoT Edge OSS project
MIT License
1.46k stars 458 forks source link

Best Practices/Guidance on upgrading a remote database module #7272

Closed tcochunk closed 4 months ago

tcochunk commented 5 months ago

I will be deploying a module based on a 3rd party vendors database and had questions about maintenance.

I can envision the need to change the schema, update data, even upgrade the database. The application (and database) will be installed on raspberry pi, at a remote site that i wont have access to the physical device. I will have the device connected through azure iot hub, so i can update settings and deploy modules.

I am looking for guidance for how to change the schema, data, and upgrade the database.

I could envision a few different solutions:

  1. having a separate module that could be deployed, when needed, that would connect to db and perform necessary changes (either schema or data).
  2. maybe a module that could be updated via twin settings with sql statements to perform the changes (dont like this but just brainstorming)
  3. create my own image based on vendors image and have the desired schema in the image (this does not allow me to update data if needed)

None of these feel like good solutions so any guidance would be appreciated.

nlcamp commented 5 months ago

@tcochunk - Please consider the following documented approaches for interacting with a SQL database using IoT Edge:

  1. https://learn.microsoft.com/en-us/azure/iot-edge/tutorial-store-data-sql-server?view=iotedge-1.4
  2. https://learn.microsoft.com/en-us/azure/azure-sql-edge/overview
tcochunk commented 5 months ago

@nlcamp thank you for the response. I read the articles and it seems like they are about using the database and some maintenance for a local edge device. That is not really what i am asking. I am looking for the ability to run sql commands (maybe to update the schema or update data) for a device that i dont have physical access too because it is at a remote location. I have not been able to find any guidance on this and i have to believe this is a common problem in the iot space.

imagine i develop a product running on a RPI for reading garden soil moisture and store the data in a database. Imagine the database is synced with the cloud so they can get information on their phone. now lets says i figure out how to read the temperature of the soil and need to add a temperature column to the database at all the millions of customers who have bought the product spread across the world. What is the recommended best practice for doing that?

since the data needs to survive container restarts, the container has a mounted volume where the data resides so just deploying a new container with a new schema does not seem like it would retain the data (maybe there is a trick that i dont know about)???

ryanwinter commented 4 months ago

Hi @tcochunk,

We unfortunately can't give and specific guidance on your individual scenario as this doesn't appear to be an IoT Edge product related question and more about updating distributed databases with new features/scenarios.

Closing for now, please reopen if you have an IoT Edge product related question.

Ryan