Here's my $0.02 on this subject:
I've been looking at an architecture to use SAP-DB for data mart
applications. To make a near-realtime operational data store
reasonably efficiently, you have to be able to get data from the
source database without expensive queries on busy tables. This
is the solution I came up with. You could possibly do something
similar for your site-site replication.
i. Create a shadow table for every one you want to replicate,
preferably in a devspace on a different spindle to spread
I/O. Add a tag column that can contain values (I, U or D)
for inserts, updates, deletes.
ii. Create an equivalent set of shadow tables on the destination
database.
iii. Write a set of triggers (or a generator script driven off
the data dictionary) that replicate changes to the source
tables to the shadow tables along with the tag that describes
what happened (inserted, updated, deleted). The shadow
tables will contain a log of changes to the main tables.
iv. Write a script that runs every few minutes, and dumps the
contents of the tables to a .csv file, erasing the contents
of the shadow tables. You could use loader and perl or python
for this.
v. The loader script ships the files across using whatever
communication mechanism and populates the shadow tables on the
destination database.
vi. Write (or write a generator for) sprocs or triggers that load
from the shadow tables and update the destination database. You
can interpret the 'I','U' or 'D' to decide what operation to
do on the table. The loader script can kick this process off if
you use sprocs (I suspect triggers on the load might be more
efficient).
This means that your replicated server would be up-to-date to within
a few minutes. It's not robust enough for disaster recovery (you
need log shipping for that - there's a HOWTO kicking about on the net
if you want to do replication for DR). It would, however, be useful
to replicate data into an ODS.
If you wanted to replicate between databases at different sites, you
could use a similar approach if you take care to set up sequences and
autoincrementing columns so that values from different sources don't
collide (e.g. site 1 gets 1 to 1 billion, site 2 gets 1,000,000,001 to
2 billion etc.)
Nigel.
_______________________________________________
sapdb.general mailing list
[EMAIL PROTECTED]
http://listserv.sap.com/mailman/listinfo/sapdb.general
