Hi,

> Hello

> I'm developing an application that will be used in five different
> locations and each night the databases should be synchronised to
> contain identical data ready for work the following day.

> At present, I'm tending towards having all the satellite offices
> sending the data to the head office, where the data gets synchronised
> and then sent back to each office. How do people do this - dial up
> using a modem / as an email attachment(!) / posting to a website?

Use TCP/IP. Either by LAN, WAN, Dial-up, ADSL, ISDN, etc...

Dial-up can be a slow process.

> Can a process connect to another Firebird server via dialup and fire
> SQL over the line to get any changes?

Yes you can, if you use TCP/IP as the protocol, but it can be slow via
dial-up.

> I think I need to give each row a branch ID and possibly
> a 'LastChanged' timestamp, so that only changed records can be sent
> to the headoffice, but what about deleted rows and rows where
> multiple offices have made changes to the same row?

Keep a log of deleted records. But, what happens if another branch
office makes changes to that same record and post the changes to the
head office after the branch office that deleted it posted its
deletion to the head office? Do you recreate the record or have a
table at the head office level that keeps track of the records that
have been deleted, or simply have a flag on the record mentioning that
it is marked for deletion at a later date and the date that the flag
was set.

> Or, is it best
> to have duplicate tables which initially start the day empty, and
> gradually fill up during the day (via triggers) with changed entries?

This is pretty much how IB LogManager and IBO Replication Module
works.

> I'm looking for any tips/pointers on how to achieve this.

I'm evaluating the same type of scenario myself.

The best suggestion is to layout the different scenarios(even diagrams
to better visualize the concept). Look at what information is needed
to make the synchronization. You can also perform some simulations on
paper(or with a small data sample and test applications for the
different scenarios) to see which scenario might be easier and most
effective to implement.

We were looking at IB LogManager to help us accomplish the task, since
it logs all the changes made to the database(data changed, date
modified and probably keeps track of other changes as well). The other
possible solution was using IBO Replication module, which seems to
keep track of the same type of information for replication. With the
IBO Replication module, the replication is only one way for now, but
that doesn't mean that you can't use the data generated to help you
synchronize your head office and then back to the branch offices(once
all the data has been transferred to the head office).

Also, you'll have to figure out how you want to manage your conflict
resolution. And, if you are using generators, make sure you have a
plan that manages the next value for them.

Daniel Rail
Senior System Engineer
ACCRA Group Inc. (www.accra.ca)
ACCRA Med Software Inc. (www.accramed.ca)