December 20, 2010 at 11:49 am
Hi Everyone,
Here is my current setup (All DB servers are SQL 2008)
RHServer - Contains one DB for a application
DB_RH
BPServer - Contains one DB for a application
DB_BP
LocalServer - Contains several DB's for different apps.
DB_IND
DB_ATP
DB_Freedom (Needs to collect data from other DB's/Servers)
Now. In the LocalServer I have a application that needs to get Updates(New Inserts) from 2 db in the local server and from the RH/BP servers (Linked Servers). In other words, anytime a New Client is added to the Remote servers (DB_RH, DB_BP, DB_IND or DB_ATP), I would like to have a table in the Local server (DB_Freedom) updated with the new changes. Now I know I could use a trigger to send an update to the DB_Freedom db, but I was hoping to find a more efficient was if possible. Hence My question about Integrated Services and Replication. Would either one of these be useful in this effort, or are these two overkill? What would be your suggestions. THanks for any help.
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 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply