Your comments are very useful - thank you.
1) I can see a need for both live data and replication. However, my gut
says we're heavily weighted heavily toward replication. Is there inherent
value in SSIS for the replication steps?
2) Are there security issues to consider in this architectural decision?
RPC [which I know little about]? The servers are firewalled but sitting
withing 6" of each other. Both are running W2003 Server Enterprise and SQL
Server Standard 2005. I am the typical developer who is sadly also the DBA
in his free time.
When I'm deciding between SSIS and Linked Servers, the first thing
that comes to mind is whether the live data is being shared across
systems, where I'm wanting to maintain referential integrity across
databases and servers. or whether I want to replicate the data from
one system to another (in a replication model.)
If the goal is to share live data back and forth, I would lean toward
linked servers, as you can refer to the actual data on the other
server, with the caveat of having to transfer/collate(?) that data
each time it's referenced across servers. Working with the live data
is obviously a little slower than replicating it to the other database
with SSIS (or even linked servers again), but it's also the most
When I'm interfacing with Microsoft CRM for instance, I use linked
servers. I want the most current contact information available within
my other applications. Copying the data across to my other databases
might be more efficient from a usability standpoint as the records
would come up a little faster if the data was already preloaded into
the local database, but it wouldn't be the live data.
Does that help?