General workflow, tools and methods for syncing remote and local database?


I could create temporary database loaded with remote data, compare it row by row with local data and execute updates according to some rules.

However, that doesnt sound like something I’d like to even attempt doing after any or some database modification, but perhaps rather in a batch at the end or beginning of the day.

And of course downloading whole of remote data and then keeping it in memory along local data for the cmparison and sync procedure seems could be done with much less data tranfer and processing.

So please, point me if there exist some general wisdom on how to optimally perform this task.


Why are you trying to sync a local database to a remote one rather than just writing to the remote database directly?


Standard sync reasons, to make possible for multiple users on different locations to work on the same database without overwriting each other, and to allow any of those clients to work offline and then sync with the server when connection is available.

But also, I wish to avoid sending whole 3mb of data to server after any database operation, as it is wasteful and preparing the whole database to be saved takes processing time needed for smooth user interface.


In the meantime I found this great article which answers most of the questions I had: