How to create datastream from SQL server on IoT Edge device to IoT Hub?

Question

Hi everyone,

I just created a SQL Server on my UP squared running Debian 11 (Bullseye) to store data following the official tutorial (https://learn.microsoft.com/en-us/azure/iot-edge/tutorial-store-data-sql-server?view=iotedge-2020-11). The data is received via modbus TCP.

Now, I would like to stream this data to the IoT Hub, so that it is not only saved locally but also in the cloud. Also, it would be great if a loss of the internet connection is noticed and data, which has not been send because of this, is forwarded after the connection is recreated again.

What I found so far is the possibility to sync data between an Azure SQL DB and an external SQL server (https://learn.microsoft.com/en-us/azure/azure-sql/database/sql-data-sync-sql-server-configure). Unfortunately this only works on a Windows machine running the SQL server. Because of several reasons I need to run the UP squared with Linux so this is no option.

Are there any other options and tools to do so?

Best regards

Answer 1

Hello @DataSciencentist2021 ,

if you followed that official tutorial, you should now have:

1. deployed Azure IoT Edge on your device, connected to the IoT Hub
2. ingest data into the database using input1 on the custom function module
3. optional egress of data using output1 on the custom function module

If you want to send messages to the IoT hub, just route the output1 messages to $upstream. Or, use "FROM /messages/* INTO $upstream" to send ALL messages from any module to the cloud.

Once your messages arrive in the IoT Hub, you need to choose how to deliver these messages into a database (instead of that sync mechanism).

There are a few options, all starting with IoT Hub routing...

The short solution is to write an Azure Function triggered by the IoT Hub and write messages into the cloud database. Recently, there are new bindings made available for SQL Azure, these are still in preview.

A more elaborate and flexible way is making usage of an Azure Stream Analytics job. This way, you write an ASA query to eg. filter, aggregate, and transform the data without extra code. See this example on how to connect a database to Azure Stream Analytics.

Finally, because you are interested in devices being connected and disconnected, again IoT Hub routing is your friend. when a device is connected or disconnected, an event is created. Please check limitations and rules in the same documentation.

Keep in mind you need to check the message properties to find out if a device is connected or disconnected:

If this answer is helpful, please mark it as an Accepted answer. This will help others with a similar question.