How do you synchronize database from different branches

[Jay]

I don't know if this is the right place to ask question regarding database
synchronization.

I have a program made in Visual Basic .NET. I have a client that have more
than 2 branches that in every week each database from other branches needs
to be updated into the server and then back again to other branches.

Can you guys tell me how do you do this? I am thinking about exporting all
records from the database (branch) that is not yet exported to the server
and copy the database again from the server back to other branch's database.
But this could take a lot of time if they have a slow internet connection.

I am using MS Access as my back end.

 

[Miro]

You can easily mark all records of "copy" with a date.

Any record you add...add the current date.

When you merge, any record with a date after the "copy date" has been
changed or new.

Just a thought.

 

[Jay]

Thanks for the reply.

How about synchronizing again the database back to different branches?

If I will just simply copy the database say data.mdb from the server to
different branches (after exporting all the records from branch's database)
by overwriting the database then I think it could take a lot of time if the
database is getting bigger.

I don't know if there's such a program with source code on the internet that
exist today that can give me a little hint or may be a reference to my own
program.

 

[Miro]

Technically if they are not sharing the same database,

Would they all not need to synchronize to the 'servers' database?

So all records would need a "last changed date".
And when synchronizing, then all dates past 'pull date' you pull and
replace?

Perhaps all your problems are gone, if you share you db through the net with
a webservice dal or something?

Everyone gets live data all the time from the 1 source. You are not keeping
'copys' of 'versions' of the same data.

It almost seems to me like you are trying to have each "branch" have its own
unique database until they merge, and then once they merge, they are "equal
to" corporates db, and then 1 minute late they are "unique" again until the
next time they merge?

Is that correct in my assumption?

Miro

 

[Jay]

Hi,

To make this clear this is what I have already done. I copied the program in
each branch together with the database. The database is the same in all
branches except that the data is different until it is merge. I created a
code that will export all the data from every branch which is not yet
exported to the main server. The exported format is xml. I have another code
that will import this xml file into the main server. What I don't have now
is on how can I update the database from every branch so it will have the
current record from the main server.

 

[Miro]

A side question...
Lets say one of your tables in your database is "Customer".

Can Branch#1 change customer 'A' and Branch#2 change customer 'A'...

So at this point and time... Customer A has actaully 3 copies of it.
Server has the original Copy.
Branch#1 has a version of it with its changed data
Branch#2 has a version of it with its own changed data.

Branch#1 and Branch#2 may have ( but not necessarily ) have changed the same
field within the database.

How are you planning to update these records in this case?

That is why I suggested 1 DB that everyone hits through the net.
Unless you have already accounted for this?

Because I still see ( if you want to stick with multiple dbs' )... you
timestamp all the server records for your solution.
Any record changed after a timestamp the branch pulls...and any record
changed since the time stamp the branch pushes.
Now, here you will have to code as well that if you are pushing the data,
and the data has been already changed on the server by a different branch,
you will have to code around this.

Example...
Branch1 & 2 pull servers data Current date time= today at 9am lets say..
<time passes>
Now the time is today at 9:30 am.
Branch 1 changes customer 'A' and customer 'B'. Pushes data, pulls new data
( which is nothing new on the server).
Here, branch 1 must "Retain" that "Originally" it pulled at 9am. So first
branch 1 pushes all data it has in every table with a datestamp greater than
9 am (the last time it pulled) (these are all the changes in the tables).
Then To Pull, Branch 1 must pull every tables data that has changed since
9am.
So...at this piont...Branch 1 now is updated. and is in Sync with the server
db at 9:30 am.
<time passes>
Time is now 9:45
Branch 2 pushes data ( in this case customer A ), but it really cant /
shouldnt ? Because the customer A has been changed at 9:30 and Branch 2
knows that customer 'A' timestamp (on the server) should be '9AM' .
See the issue you are going to run into?
If this is not a problem...and you assume that the last person who pushes is
Always correct / most up to date, then this is fine,
Branch 2 pushes all data to the server that has changed since 9AM ( the last
time it pulled ).
Then Branch 2 must pull all data that has a greater timestam since 9AM on
the server.
In this case..Customer 'B' has a timestamp of 9:30 ( that is greater than
its original timestamp of 9AM) so it pulls Customer B.
Once this is done...Branch 2's new "timestamp" is now 9:45.

The next time this happens...the start time to pull data and compare data
starts at 9:45 am for branch 2.
Branch 1's time ...is anything since 9:30am.

But 1 db on the net with a DAL class to get and set data, gets rid of most
of these issues as everyone always has the latest version on the server and
is changing the latest data on the server.

Just a thought.

It can get ugly to keep multiple db's of the same db' in sync. I understand
it is necessary sometime... but the above mentioned...is the way I had to do
it in one of my dummy apps I created here in the office.

It was 'ok' in this app because for the most part, the data stayed stagnant
and only 1 person changed the data here and there within the week. The data
wasnt constantly changing.

I hope the above helps.

What did you have in mind happening - trying to solve this issue?

Miro