Data sync issue in Stream Analytics job to Azure SQL database

Question

I am using the Raspberry Pi Azure IoT Online Simulator to push the data (in the interval of every 500 milli seconds) to Azure IoT hub (Basic Tier) using this link https://azure-samples.github.io/raspberry-pi-web-simulator/ I have configured Stream Analytics job and sync the data to Azure SQL database. What I have noticed, though I am sending the data every 500 milli seconds, its not reflecting consistently at regular intervals. Please refer the screen shot.

Also, I am suspecting some data loss as well. Now I am checking the feasibility of just one device, if this is success then I may have to implement it for 10,000 camera devices. But without having this issue sorted, I can't implement for real devices. Could you please suggest how to solve this. Note: Same issue noticed when I tried to stream the output to Power BI Thanks in advance

Answer 1

@Vishvanath Bhat - Thanks for the question and using MS Q&A paltform.

It seems like you are facing data sync issues in your Stream Analytics job to Azure SQL database. Based on the screenshot you provided, it looks like there are some gaps in the data and it is not reflecting consistently at regular intervals.

There could be several reasons for this issue. One possible reason could be the network latency between the Raspberry Pi Azure IoT Online Simulator and Azure IoT Hub. Another reason could be the configuration of your Stream Analytics job.

To troubleshoot this issue, I would suggest you to check the following:

• Check the network latency between the Raspberry Pi Azure IoT Online Simulator and Azure IoT Hub. You can use the Azure IoT Hub metrics to monitor the incoming messages and check if there are any spikes or drops in the message count.
• Check the configuration of your Stream Analytics job. Make sure that the input and output settings are configured correctly. Also, check if there are any errors or warnings in the job diagnostics logs.
• Check the schema of the data being sent from the Raspberry Pi Azure IoT Online Simulator. Make sure that the schema matches the schema of the target table in your SQL database.
• Check if there are any throttling limits on your Azure SQL database. If there are, you may need to increase the limits to handle the incoming data.

If you are still facing issues after checking the above, you can try increasing the batch size and the batch frequency in your Stream Analytics job. This will help in reducing the number of transactions and improve the performance of your job.

I hope this helps! Let me know if you have any further questions.