Subject RE: [firebird-support] how to transfer data between Firebird and MS SQL Server Compact 3.1 databases
Author Sasha Matijasic
> I need to transfer selected data sets between Firebird and MS SQL
> Server Compact databases (the later is to be sync'd and used on a Win
> Mobile PC PDA device).
> How to achieve this transfer (both ways) within Delphi 7 programming
> environment ?
> Regards
> Mario
In a nutshell, you select data on your source server, save it to a file,
transfer it and update on your target. The implementation is up to you. I
have done so in my project (although I'm replicating data from FB to FB, but
the algorithm is the same) where I save dataset to xml and zip it. The
result files are pretty small and easy to handle.
Although repplication part is pretty strait forward it does have some
pitfalls you will encounter, it all depends on your needs and database. The
part that requires most thinking about is unique constraints (including
primary keys). For example you have to handle the situation when you want to
insert a row to target database where that row already exists. I have chosen
uuids for my primary keys (to which half of the developers in the world
would say it's bad... ) so when I encounter that uuid in a target I do an
update instead of insert. Unique constraints are handled a little bit
different but the basic principle is the same.
I should warn you that this aproach would probably fall apart in high
traffic environment, it's simply not scalable (at least I think it's not, I
haven't really done any tests beyond what I need times 100 and results were
satisfactory for me).
You might avoid uuids by having compound primary key consisting of id and
server_id columns where you ensure server_id is unique among different
If you don't want temporary files you might consider transfering data by
direct tcp connection or something that is available to you.
I am sure there are other considerations too, I'll be hapy to discuss if you
have any.

You might also look at commercial solution
that works for FB and MSSQL. I haven't tested it so I can't say more but
they offer evaluation copy of their components (it's native VCL).