Synchronizing two databases!

[Inocencio Oliveira]

Hi all!

I have two databases in my application, one is a server side database, the other one is a local database.

My client must work into online mode (server db) or offline mode (local db). When offline mode is chose, some tables from server side must be downloaded to allow the client to work in offline mode. When he turn back and connects to server, the server must synchronize local data with server data.

I was thinking to put on some version control or some API that can help me to do it more fast and easily.

Someone have some tips or know some API or something like?

Thanks all!

[Paul Sturrock]

Database synchronization is non-trivial. What you are basically doing is designing a very, very, very long transaction in to your application, and so have to deal with stale data and conflicts. What do you do if the user in off-line mode makes updates that conflict with another users also in off-line mode? Who "wins"?

"Fast and easy" is probably not possible. Replication software exists for DBAs to keep hot backup, but this tends to be to keep an unused database synchronized with the one being used so they can swap them in the event of a catastrophic failure.
[ December 17, 2007: Message edited by: Paul Sturrock ]

[Inocencio Oliveira]

Yeah Paul,

I was thinking to use some SVN (or CVS) concepts into my tables and modules. Tables carrying control version and works that like that in internal way.

When someone wanna to commit some row that this row is not up-date, the conflict will raise out to the screen and users make decition, right choose to put the data overloaded.

I want to make like SVN, but for tables and rows. Is it a good idea? I think is almost impossibile to find out some API or something implemented yet.

Thanks all.

[Inocencio Oliveira]

My issue is to keep this datas under control. Because the client can be stay away from the server, in this case local database start to works.

When he comes back to server. The control version must apply changes, check all data modifications, compare versions and try to do something with or without user request.

[Ulf Dittmer]

If you can be sure that there are no other modifications during the time in which a client is offline, the problem would become a whole lot simpler. You could just keep track of which changes are made to the local DB, and then apply those to the server DB upon reconnecting. The information needed would be: 1) table name, 2) primary key ID, 3) type of change (insert, update, delete). With that information you could update the server DB pretty easily.

But as Paul pointed out, if there can be changes to the server DB in between, it's a different ball game.

[Jan Cumps]

Database synchronization mechanisms are popular with CRM applications. Several CRM vendors have built there own synchronization mechanism.
I've worked the best part of my twenties for a company that offers such a solution. What I learned there is that it's a complex matter. Not something that you can handle as 'one of the technical additional requirements'.

You might have to deal with failing synchronization, or with conflicting updates.

Your customer may (and will) ask you to 'scope' the data for each remote computer. She will want you to restrict the data that's loaded on each laptop, based on some conditions, like region.
You will have to deal with situations where users change records, and they get out of their scope. This has to result in a delete on the laptop, and an update on the server db .

Some databases (e.g. Oracle) offer synchronization mechanisms.
SourceForge has some projects (e.g. SymmetricDS) dedicated to database syncing.

Good luck, Jan
[ December 17, 2007: Message edited by: Jan Cumps ]

[Inocencio Oliveira]

Thanks Jan, great post!

When I came to home, I will translate to english the schema that I am building for a incomming solution.

I know that it is needed to handle all possible variables, each case can result in many others cases tough enought to carry on. I will try to get some API and try to build a tiny prototype or I will try to implements these concepts as well.

Thanks all for support, lets try to keep this thread on.

[Leandro Dantas]

As said before, it's not easy at all.

What you can try to implement is a local system that sync with a remote database.

What I thought about:
Develop your local system with local database.
Keep a mirror schema with one more field on each table: dml. This field is created to keep the operation done with that row on database ( insert, delete or update ). This mirror schema will store any operation done on your local database.
The same must be done on your remote server. any remote operation must be stored to YOUR local system be able to reproduce it.

Create a background service that redo all commands done on your mirror tables on the remote database and redo on your local db all remote queries.

You must beware of inconsistent operations:
- what you will do if you try to insert an already inserted registry? Update? Leave it alone?
- And what about an update on a row that was deleted? should re-insert it?

DO you REALLY need this feature? hehehe.

Good Luck. You will need.

[Inocencio Oliveira]

Unfortunately I need. I will explain why I need it.

The application system works for auditing. The auditor can install application in your workplace, and they can also install the application in your notebook. He can take notebook to any place, other city at all and work into the system (cannot connect under internet).

Because that the system must synchronize and take control version under tables and records to keep datas consistents.

So I was thinking in this solution to solve this issue, I have wrote this thread to discuss this problem, and trying to get some new idea or another solution from my mates here. I exposed my problem, and I am grateful by helping here.

Next post I will show all my notes to this problem.

[Inocencio Oliveira]

I wrote my idea for that issue.

---------------------
Suggestion for database synchronize!

Author: Inoc�ncio
Date: 13/12/2007

- A local database must make synchrony with a remote database.
- There are a control version to any record of each table that can handle the process of a local database, tables that cannot be downloaded will not handled of control version process.
- When one or more auditor are working on online mode (and there arenp�t downloaded records), the cv will not be applied for those records.
- If at least one auditor download one record, this record will be controlled by cv, both online and offline modes.
- For the cv, is only important the last record process. If the user have changed record data so many times, the last time will take permanent.
- For each download process, the local database will stored from the begin (empty base till download all marked contents), the local database only have content from the last actual database schema, not keeping cv from previous process.
- Will not have merge process between records.

Scenario 1

Auditor A will work offline, auditor B will work offline, after all, both will take to online mode, they are working in the same record, but in differents local machine, how the system will put up

Downloading contents to work in offline mode...

Server, row = 1, version = 10

Auditor A, offline, row = 1

Auditor B, offline, row = 1

A = 10, B = 10

Process (operations):

A = 11 (update)
A = 11 (update)
A = 11 (update)

B = 11 (update)
B = 11 (delete)

Momentum of Synchrony (turn to online mode):

B, deleted row number one (B last operation).

ServerListerner know that auditor B want to delete row 1.

The server must knows beforehand which auditors downloaded row 1. He knows that was B and A auditors.

The record is marked as delete operation at remote database, thus will not more longer be available for the system, merely will be useful when auditor A turn back online and take commits.

When auditor A connect to online mode. 'A' will take a warning conflict message (like CVS or SVN when souce files take conflict). Being auditor A the last user that got this record in the version 10. He will decide if our version will overriding the last posted version from auditor B (in that case, delete operation), or will not modify the 'B' decision.

In that exact moment server must do:

If 'A' accept 'B' decision
Permanently delete the record form the remote dabase.
Permanently delete the record form the remote dabase and advertise all envolved parts which occured.
If 'A' accept 'A' version
Overriding B version.
Overriding B version and advertise all envolved parts which occured.

P.S.: If 'A' overriding 'B' record version, the next version number will counted with actual version plus one. In that case was 11, now take 12. And the begin process was 10.

I hope I was clear, cause my english is not so eligible and clear.

Thanks all for your patience. I guess this take import for all that needs take internal database controlled by version.