I want to sync new updated data of both database SQLite (i.e. Local on mobile device) and SQL Server (i.e. Server database) data, is there any solution for synchronization.
I want to update latest record from both databases vice versa.
I think you'll probably be best to roll your own if you're working with a mobile device, using something like XML or JSON as an intermediate representation, or even generating SQL (but that's more of a security risk for injection attacks). There are big differences between SQLite and MSSQL. I've used tab files, which is also an option as both offer bulk loading systems, but that involves total rewrites of both entire databases, and that's slow and data-heavy. So I'd use timestamps or SHA digests to flag modified records, and allow a service layer to to provide an update data block since a given requested last update time. That will reduce the amount of data transferred (important for mobile) and reduce the transaction size to the changes needed. I'm not aware of any database patching systems that will do that for you (most focus on the schema, not data) as generally this will involve some awareness of the data model.
Thanks to both of you.
I have used web services to send and receive data and used time stamp with insert/update/delete flag to synchronize current updates. but I am facing problem because data stored on mobile devices (more than one) in offline mode for 1 user.
I guess I'll resolve this with the help of Device UDID/IMEI so that i'll commit at right time.
you should use a PHP script that read/write JSON code. JSON will be the main format for your request to be send and receive (i.e. for SELECT or INSERT query). i can help you more if you are using ECLIPSE; are you?
You can try with creating "provisional" records on the client, and then when you sync them to the server have the central system assign the ID. The client can update the local record to reflect that. The other is that you distribute ID creation in a way that (normally probabilistically) allows clients to create the ID without collisions.
For that, go to UUIDs - v4 are fairly unlikely to collide.
Otherwise, consider something that puts the unique mobile device ID in the record ID. So, your record ID might be ${imei}-${local sequence number} or something, where the IMEI ensures uniqueness, and the local sequence number is just a normal sequential database ID.
one possibility would be for the server to assign each client a unique client ID, and make the Client ID a part of the primary key.
you need to check the time offset between the server and the mobile device, and you need to have a method for resolving conflicts. Inserts are no big deal (they shouldn't conflict), but updates could conflict, and a delete could conflict with an update.
There are frameworks to handle this kind of thing, such as Microsoft Sync Framework.
Hi Rajat.
Thank you I have also designed same architecture. but I think it is very much heavy and slow as execution is concern. will it?
Regards,
Prakash
I am Agree with rajat. There is a whole comprehensive sync framework by Microsoft for for this kind of tasks which is better to use then to write from scratch.
No need for a single line of code to achieve synchronization. See link below:
http://sqlite-sync.com/
check out rqlite and bedrockdb (http://bedrockdb.com) which do distributed synchronization of sqllite db's
- Badges
- Science topic
- Similar topics
- Computer Science and Engineering
- Algorithms