How to Synchronize Android Database with an online SQL Server?

Question

I am developing an Android App that stores different types of data in the built-in SQLite provided by the Android Platform.

Inside the App I have placed a "Sync" button which is supposed to Sync the data between the local SQLite Database, with an Online SQL Server database on my server.

What is the workaround to handle this? This feature can be found in Google Calendar, where you can view the calendar's events on your mobile, and when you add a new event and Sync the data, you can view the updated data by going to your online account too.

Note: I don't want to centralize my database online, because I also want to give the mobile users the ability to use the App without internet connection.

Answer 1

You should take a look at the SampleSyncAdapter project in the Android SDK demos (Transferring Data Using Sync Adapters). It shows you how to do synchronization "Android style", which requires no user interaction by manually tapping a sync button all the time.

On top of that you need to write server software that is able to provide your SyncAdapter with a "delta" of all changes since the last commit. The most basic approach is keeping a "last synchronized" time stamp in your app which has to come from the server, otherwise you could get into trouble because of time differences between client and server. Normalize all time stamps as GMT or any timezone of your choice, but stick with that decision. If your app needs to display a time stamp in local time, then you need to use the Java Calendar and TimeZone class for converting the normalized time stamp into local time.

Your app also needs to flag changed local records as "dirty", so your SyncAdapter knows that it needs to upload these changed or new records to the server.

Following minimum features are needed for your server software:

• Update existing record(s) function
• Add new record(s) function
• Get updated records since last synchronization (app provides time stamp)
• Get new records since last synchronization (app provides time stamp)

You also may want to read through some Google API (like Google Calendar) for getting an idea of how all of this works and how to design the server API interface for the communication.