Data synchronization

[Thorsten Hohage]

Hi Ruslan,

On 2007-09-03, at 10:26, Ruslan Zasukhin wrote:
>> Small group of multiple users, most users have local network  
>> access and
>> other users on the road with laptops.
>>
>> Laptops are equipped with wireless broadband but will be required  
>> to do
>> offline changes. Changes are synced when network is available.
>> Some of these changes involve structural as well as simple data  
>> changes.
>
> And here main question we have:
>
> Does laptops should have
>     a) the whole snapshoot of server db ?

sometimes yes - it seems that especially germans tend to carry the  
whole 10GB DB with them, but ...

>     b) or its part?

yes - THIS is in most cases (system I build) used. But part often  
means depend on context i.e. a query what should be replicated (where  
staff_district = 'Hamburg') and it depends on the table, too. So  
articles complete, customers only from the district, help desk-tables  
none.

>     c) or nothing at all?

I know one single solution where NO data is replicated TO the satellite


> Case c) means that we need only one direction synch
>             Client => Server
yes


> Case a) means bi-direction synch
>             Client <=> Server
of course, yes.


> Case b) the same as a)  but also require way to specify some  
> FILTERs to load
> from server only some PART of info.
yes absolutely



Additional one further remark

>> Changes are synced when network is available.

perhaps not only when network is available. There are still  
situations where syncing should be done by media transfer, i.e. USB- 
Stick, CD, ... i.e. imagine a expedition where the entered data must  
be synced and NO or better NO cheap enough network is enable



And I want to add two more and really important topics! There must be  
a solution for generating unique-keys, too - something like satellite- 
id + table-unique-id to be able to sync data - enter new data on each  
system - re-sync - enter other new data ... could be tricky, really  
tricky.

We used a system with a central serial table with pre-generated  
serials and an id for what satellite this id is. These serials are  
replicated, too and so every system gets its set of serials. Using  
triggers to request a serial when a record is generated. But of  
course there are several more concept on how to handle serials in a  
distributed system


The same problem is regarding deletion of data. Satellite_1 and  
Satellite_2 get some contacts in the same time. Now Satellite_1  
deletes a contact and Satellite_2 changes this contact later in time,  
then Satellite_1 deletes it. Satellite_1 syncs his data to server,  
later Satellite_2 syncs too and the contact is back. Next time  
Satellite_1 syncs with the server he gets the deleted contact back.

Other scenario: Satellite_1 and Satellite_2 gets both contact data,  
but Satellite_2 get the invoices, too. Satellite_1 doesn't see any  
invoices and deletes the contact, what happened with the joined data  
that are still on Satellite_2.



Of course some of these issues must be handled by application (better  
system) logic, but there must be propitiate options in the database  
to configure these rules.

regards

Thorsten Hohage
--
objectmanufactur.com - Hamburg,Germany