Client-server synchronization over REST

I don't think there's a silver bullet but the pattern you're looking for is caching. In past projects I have copied items from the server to local storage (SQLite or flat file), and maintained meta-data about the entries to update/upload/purge based on simple rules. It's not a simple problem and ends up being a lot of code.

Consistency: in your example, either ensure you download the countries table first, or make the two operations atomic - e.g., make copies of the "new" tables and only replace your cached versions if both copies complete successfully (doubles up on storage).

Only download new/deleted/changed: Yep, this requires client/server integration - timestamp all the records (with GMT), ask for metadata from the server, and walk through your local metadata deciding what to do. (Hint UUIDs on the rows are helpful).

Synchronize local changes: Yes, more client/server integration. See above paragraph.

Handling all the exceptions and edge cases is challenging. Take a look at the problems with iCloud sync'ing CoreData. Perhaps not a fair comparison, as Apple is trying to solve this for a totally distributed database, but interesting reading nonetheless.

Currently I am working on the same task - building the Android app which would sync info with a central SQL database using Azure Mobile Services. The sync strategy is to support bidirectional sync from multiple clients to ensure data consistency whereas only incremental changes will be exchanged.

Let me provide my solution to your problems.I recently wrote a blog post about sync algorithm to support this scenarios.

The sync logic will be managed on the client side because of REST API communication.Each table participating in the sync process will have the corresponding REST Api methods for CRUD operations.

For your first problem (consistency) – the solution is to download data on the client in correct order. E.g. first parents, then children to avoid problems with referential integrity. In case of network problem the client would have to sync again to get the rest of data.

Second problem (download only incremental changes) – the solution is timestamp (as Kevin mentioned in his answer below). But I would suggest using globally incremented value maintained on the server side to avoid issues with timestamp differences between clients and server. The rowversion of SQL Server is quite good candidate for this.

Third problem (data integration from the client) – using Dirty flag in the client tables would help to differentiate the records required for upload.

You might also need to introduce the Delete flag on both sides to handle deletes between multiple clients.