 |
BorlandTalk.com
Borland discussion newsgroups
|
| View previous topic :: View next topic |
| Author |
Message |
Bruce Vander Werf
Guest
|
 Posted: Sat Sep 20, 2003 12:45 am Post subject: Synchronizing Databases |
 |
|
I have a need to synchronize (replicate) two databases. One will be a
local DBISAM database, and the other will be either a local or remote
MySQL (or possibly a local Access) database. I basically need to
compare two tables and update changes made in one to the other,
including inserts and deletes. Tables in both databases will have an
integer primary key, which will be assigned from different ranges on
the local and remote databases. This needs to be done as seamless as
possible as it will be a part of widely distributed package used by a
diverse group of users.
First of all, are there any components available (freeware or
commercial) that will do this?
If not, how is this typically done? I thought about maintaining an
'Updated' field in each table, as well as recording the 'Last Update'
time on the local database. Then, for each table:
1. Query all the records for D1 and D2.
2. For each record in D1:
2a. if record exists in D2, compare Updated field, and copy record
from the last one updated to the other.
2b. if record doesn't exist in D2, if D1 'Updated' is greater than
'Last Update' then insert record into D2, else delete record from D2.
3. Repeat 2b for each record in D2.
How about the case where the time on the remote server is different
than the time on the local PC?
Any other methods? Surely this has been done before. Is there
documentation (articles, books, websites) on this subject that someone
could point me to?
Thanks...
--Bruce
Bruce Vander Werf
[email]brucev2 (AT) hotmail (DOT) com[/email]
|
|
| Back to top |
|
 |
Venkat
Guest
|
| Posted: Sat Sep 20, 2003 8:46 pm Post subject: Re: Synchronizing Databases |
|
|
Bruce,
"Bruce Vander Werf" <brucev2 (AT) hotmail (DOT) com> wrote
| Quote: | I have a need to synchronize (replicate) two databases. I basically need
to
compare two tables and update changes made in one to the other,
including inserts and deletes.
|
What you are looking for is some sort of replication. I am assuming it
has to be done periodically. Not just one time job? Right? You can probably
write your own classes to do this depending on how much time it takes and
how much time you have.
Other options for you are DTS -- Data Transformation Services, or any
other data warehousing tools available.
| Quote: | Tables in both databases will have an
integer primary key, which will be assigned from different ranges on
the local and remote databases.
|
It would be better if you have some kind of natural key instead of
surrogate keys to do the comparision. Otherwise you might end up duplicating
data.
| Quote: | First of all, are there any components available (freeware or
commercial) that will do this?
|
See above. Also, there are some tools in the market, similar to Datapump
provided with Delphi. I don't think they can support customized migration
either.
| Quote: | If not, how is this typically done? I thought about maintaining an
'Updated' field in each table, as well as recording the 'Last Update'
time on the local database. Then, for each table:
1. Query all the records for D1 and D2.
2. For each record in D1:
2a. if record exists in D2, compare Updated field, and copy record
from the last one updated to the other.
2b. if record doesn't exist in D2, if D1 'Updated' is greater than
'Last Update' then insert record into D2, else delete record from D2.
3. Repeat 2b for each record in D2.
How about the case where the time on the remote server is different
than the time on the local PC?
|
You need to have a field that maintaince the Time Zone info or use a
datatype with Time Zone information included. I don't think both of the
RDBMSs mentioned have that support.
| Quote: | Any other methods? Surely this has been done before.
|
I have not seen any good documentation on the net regarding this. Did
you try google?
-- Venkat
|
|
| Back to top |
|
|
|
|
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
|
FAQ
Search
Memberlist
Usergroups
Register
Profile
Log in to check your private messages
Log in
