> I would like some help on the following adventure.
> I have a table that has only certain rows that need to be sent to a remote
> table. This will happen on a recurring interval. Also, any emergency /
> priority record could be sent on demand.
> What is the best way accomplish this, with the least amount of work?
Least amount of work from who's perspective? And does cost have
anything to do with your decision?
Check out the other replies for suggestions on 3rd party replication
products (other than RepServer) ... nah, I don't know anything about
If you're looking to cook something up yourself ...
- set your primary dataserver up for remote access to your desired
- figure out how you want to flag your rows for replication (add a 'to
replicate' column? put the key in a 'to replicate' table?) ... and flag
- write a 'replication' stored proc which scoops up the rows to be
'replicated' and one-by-one does a remote 'insert' or stored proc call
(sp would do the insert) to the target dataserver
- verify the insert took affect and then update that 'to replicate' flag
for the given row (in case you want some sort of auditing)
- write an OS script which calls this 'replication' stored proc at
- for 'on demand' ... just mark the necessary row and call your
'replication' stored proc (maybe pass the key of the 'on demand' row
directly to the stored proc?
Issues to resolve ...
- do the rows need to be replicated in the same order in which they were
created, i.e., do you have to maintain your transactional consistency?
- if you include some sort of auditing ... will you need to periodically
'prune' your audit trail?
- how do you handle attempts to insert duplicates on the other end?
(how about calling a 'remote' proc on the target dataserver ... this
proc could check for a duplicate before inserting?)
- what happens if, somehow, multiple copies of your 'replication' stored
proc are called at the same time? (will you get duplicate inserts on the
far side? probably want to have some sort of intermediate status to show
that a row is 'being processed'?)
- if this replication system 'goes down' ... can it be started
gracefully, i.e., can it automatically figure out where it left off and
pick up from there ... or will it require human intervention?
- make sure you give yourself the ability to log and/or audit the system
... so that you can easily track down problems
- will you need the ability to re-synce from a point in the past?
(e.g., suppose the target goes down ... can you go back and re-flag
'old' rows on the primary for replication?)
- if you have a single source row going to multiple target locations ...
your 'to replicate' and auditing designs will have to address this issue
How fancy you get depends on how fancy you need to be ... or how
important it is to make sure rows get to the target end ... or how easy
(required?) it is to recover from a 'problem'?
How hard would it be to set something like this up? Shouldn't be too
hard (depends on your isql, t-sql and OS scripting abilities) if you can
satisfactorily answer all (and more?) of the issues I've mentioned.
NOTE: My comments are based on the Sybase product line ... I'm not sure
what you'd do in an IBM-DB2 environment (comp.databases.ibm-db2??) ??
Iron Horse Consulting, Ltd.