Re: Keeping two databases in sync
From: Oliver Mathews (duel_at_telkomsa.net)
Date: 10/22/04
- Next message: Harry Bosma: "Trying to reach Addict Software"
- Previous message: Jonathan Neve: "Re: Software testing"
- In reply to: Jonathan Neve: "Re: Keeping two databases in sync"
- Next in thread: Jonathan Neve: "Re: Keeping two databases in sync"
- Reply: Jonathan Neve: "Re: Keeping two databases in sync"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]
Date: Fri, 22 Oct 2004 10:42:34 +0200
Hi Jonathan,
Hey thanks for all the effort you have put in here! Your input has been very
helpful indeed.
I would be very appreciative if you could forward that stuff - it would
certainly help us hit the
ground running in delphi.
Cheers
Steven
"Jonathan Neve" <jonathan@!nospam!microtec.fr> wrote in message
news:41765c85@newsgroups.borland.com...
> Steven Yates wrote:
>> Hi Jonathan
>>
>> TCO = Total cost of ownership (to the client)
>>
>> We will have many clients so to purchase a licence for hundreds of
>> clients would be too expensive.
> Ok. I completely agre. Which is one of the reasons I didn't choose
> IBReplicator. Rather, I wrote my own replicator, which means that my
> client simply payed my development time, there's no licencing per
> machine/server, nor any added fees every time they get a new computer,
> etc.
>
>> Fibplus generates SQL statements but just shows the parameter not the
>> acutal value of that paramater.
>> If we could get that same SQL statement with the actual value we could
>> send this SQL over to the
>> remote server.
>
> What you're trying to do here, is actually nothing other than a
> replicator... That's what a replicator is: a program for synchronizing two
> DBs.
>
> Anyway, I suppose you're talking about the SQL monitor. I also tried to
> take this approach, but it's not a good idea. The main reason for this, is
> that the SQL monitor is not reliable enough. Sometimes it gets the order
> of things wrong, or else it might at times miss something out... It
> usually works fine, but it's not designed to be used in a mission-critical
> way, it's more a debugging tool.
>
> (Incidentally, I wrote a little add-on component for FIBPlus, called
> TpFIBSQLLogger, which is going to be included (or perhaps is already),
> into their service pack. This component does exactly the same thing as
> TpFIBSQLMonitor, except that instead of using shared memory, it simply
> calls an event (if it's defined) or writes to a file. As a result, it's
> instantaneous (at any point in time, it shows exactly all the SQL that's
> been issued), and perfectly reliable. Still, even using this, I don't
> think it would be a good solution for your problem, for other reasons.)
>
> Another reason why I don't think this is a good approach is because it
> isn't fault tolerant. It _ought_ to work, if everything is the same in the
> remote database as in the local one. But it might not be. For example, it
> will fail dismally if there is a PK conflict: if a row is inserted into
> the remote and the local DBs with the same PK, the insert statement will
> not be able to be applied to the remote database. Or if another user
> deleted the record (in the remote DB) that you just updated (in the local
> DB), then your update statement will not fail, but neither will it work.
>
> In short, this approach simply isn't flexible enough.
>
> What I would do (and did :-) ) would be to create a table in your DB in
> which to log all the changes to your DB. Then, create 3 triggers for every
> table (update, insert, delete), that insert a line into the log table,
> simply giving the tablename, the date (not strictly necessary, but useful
> for debugging purposes), the PK value(s), and that's about it. (There are
> a lot of other things to be considered, but I can't get into it all right
> now!)
>
> My advice: don't log the operation type (update, insert, delete). This
> would have some of the unflexibility of the SQL Monitor approach described
> above. Rather, simply "touch" the record, as if to say "Something happened
> to this record, check it out". Then, when you synchronize this change, you
> just have to have a look at the local base to see if the record is present
> or not, and then at the remote base. That way, you can easily determine
> whether you should do an INSERT, UPDATE or DELETE. The good thing about
> this is that it's very flexible. In the local DB, it may have initially
> been an INSERT, but if the record is already present in the remote base,
> it will get turned into an UPDATE.
>
> If you like, I could send you my code (BCB 5). You may not be able to use
> it as such (if you use Delphi), but it would surely give you some ideas.
> My replicator has been in place for nearly a year, with very little
> maintenance (just the odd bugfix). Tell me if you're interested...
>
> HTH,
> Jonathan Neve.
- Next message: Harry Bosma: "Trying to reach Addict Software"
- Previous message: Jonathan Neve: "Re: Software testing"
- In reply to: Jonathan Neve: "Re: Keeping two databases in sync"
- Next in thread: Jonathan Neve: "Re: Keeping two databases in sync"
- Reply: Jonathan Neve: "Re: Keeping two databases in sync"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]
Relevant Pages
|
