I have two servers running SQL Server 2005. I'd like to transfer data back
and forth between the two.

Aside from the UI associated with SSIS, what are the pros and cons of using
linked servers to insert/update/delete data versus using SSIS? The
approaches are clearly different, but is there a "duh, if you had a 1/2 a
brain you would use SSIS because most of the time because ...." blah, blah,
blah? Writing the logic using T-SQL to manage the data between these two
systems strikes me as very appealing from a mainteance standpoint but my gut
tells me I'm missing something.

Thanks,
Mark

On Jun 16, 9:34am, "Mark" <m...@idonotlikespam.com> wrote:
> I have two servers running SQL Server 2005. I'd like to transfer data back
> and forth between the two.

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
current data.

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 ?

Your comments are very useful - thank you.

Two questions:

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.

Thanks again,
Mark

"Eric Isaacs" <eisaacs@gmail.com> wrote in message
news:3a20daf2-1e30-42d0-bb84-5120234a2f12@w8g2000prd.googlegroups.com...
On Jun 16, 9:34 am, "Mark" <m...@idonotlikespam.com> wrote:
> I have two servers running SQL Server 2005. I'd like to transfer data back
> and forth between the two.

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
current data.

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 ?

> Two questions:
>
> 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 theDBA
> in his free time.

SSIS is definitely an option for replicating data. I find it most
useful when the data is on another database platform. I also however
find SSIS less straight forward than Linked Servers. The packages are
more difficult to create from scratch than a simple SQL statement
referencing an external linked server.

With Linked Servers, you have a little more flexibility, but also some
additional security concerns. You are linking to the other data
source as a specific user, with that user's authority to the linked
server. If all you need is Read-only access to a few views, that user
should only be granted that authority to those objects. This isn't so
bad, you have total control of the authority you grant to that user,
but you have to manage it and realize that granting too much authority
could be a security concern.

Consider using a linked server with read-only rights to copy data from
specific tables/view into permanent tables in the other database in
lieu of an SSIS package. You can execute a job periodically to copy
the appropriate data from the linked server to the local database and
reference the local tables within your application. It will likely be
easier to implement and maintain than an SSIS package, and could
potentially be updated later to make it a live solution.

Thank you for all the details Eric. Deeply appreciated.

Mark

"Eric Isaacs" <eisaacs@gmail.com> wrote in message
news:d8607257-dec6-4a48-86ca-d7f8ee34817a@u6g2000prc.googlegroups.com...
> Two questions:
>
> 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.

SSIS is definitely an option for replicating data. I find it most
useful when the data is on another database platform. I also however
find SSIS less straight forward than Linked Servers. The packages are
more difficult to create from scratch than a simple SQL statement
referencing an external linked server.

With Linked Servers, you have a little more flexibility, but also some
additional security concerns. You are linking to the other data
source as a specific user, with that user's authority to the linked
server. If all you need is Read-only access to a few views, that user
should only be granted that authority to those objects. This isn't so
bad, you have total control of the authority you grant to that user,
but you have to manage it and realize that granting too much authority
could be a security concern.

Consider using a linked server with read-only rights to copy data from
specific tables/view into permanent tables in the other database in
lieu of an SSIS package. You can execute a job periodically to copy
the appropriate data from the linked server to the local database and
reference the local tables within your application. It will likely be
easier to implement and maintain than an SSIS package, and could
potentially be updated later to make it a live solution.