December 20, 2010 at 12:26 pm
depends on how many inserts we are talking about i guess.
replication would be my last option. I would do one of two things;
as you said, use a trigger. or just create a SQL agent job to run every 5 minutes (or whatever) and insert the new data.
December 20, 2010 at 12:38 pm
In my view a trigger is out, cross server triggers are an accident waiting to happen, if for any reason the update doesn't occur it rolls back. You already incur a penalty for an intrA-server trigger, it only gets worse for an intER-server trigger.. Been there, done that, NEVER again.
If it is easy to pick out the new data from the old without having to do a bunch of lookups or transfer other data it might make more sense to build an SSIS package to handle it.
Replication can be tricky, especially if you need to know which database the record came from, it is somewhat easier in SSIS.
Transactional replication might be an option if you are doing lots of tables and you aren't likely to get key collisions, or you have mitigated key collisions with some server/database identifier.
CEWII
Viewing 2 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply