Implementing Data Synchronization from Raspberry Pi to Central SQL Server

Industrial
1

Hi,

We have a use-case where we collect data from cranes and process it using Node-RED inside a Raspberry Pi, then store it in SQLite. We have an SQL server on our centralized server that communicates with the Raspberry Pi via WiFi. I now want to sync this data with the SQL server at 5-minute intervals, ensuring that only new data is copied to the SQL server. How can I achieve this

2

No doubt you can craft your SQL or the database schema so that no duplicates get into MSSQL.

I'd probably use MQTT to pass the SQL statement from the Pi to the server. It's not trivial to guarantee delivery over an unreliable network but there are posts here describing an approach.

Use the same mechanism to pass back a list of inserted records and set a "transferred" flag in SQLite for reference in 5 minutes time.

3

Thanks.
Yes, I am currently sending queries from SQLite to the SQL server. If the transfer is successful, then I write a 'transferred' flag in the SQLite transfer column

4

There is some info here: c# - Synchronize SQLite databases to SQL Server - Stack Overflow and here: sql server - Syncing SQLite with SQLServer using Sync Framework - Stack Overflow