Moving Data... does it really need to be so complicated?

Question

Moving Data... does it really need to be so complicated?

Jeff Moden

SSC Guru

Points: 1004439
More actions
March 31, 2011 at 6:38 pm

#234148

Quite possibly showing some extreme igorance on my part of the subject :blush:, would someone explain to me why the methods demonstrated for moving data from one instance to another at the following URL are somehow better than using either a Linked Server or maybe even OPENROWSET?
http://www.mssqltips.com/tip.asp?tip=2338
And, no... I'm not trying to be a smartguy, here. I'd really like to know if there's an advantage because it seems they've not done much more than duplicate what some of replication does.
Thanks for the help in understanding, folks. 🙂
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

WayneS SSC Guru Points: 95461 More actions · Answer 1

Just shooting from my hip here... I know that Linked Servers (and the OpenRowset/OpenDatasource) utilize RPC calls. Frequently when working with tables via Linked Servers, the local server end up pulling the remote table across the network - not sure if this applies for an update though. It would then have to pass back across the network everything to be changed. Also, doing things through a linked server is usually just a little bit slower - the local server is ensuring ACID across to the remote server, etc.

The method demonstrated establishes a direct connection, and uses a table parameter to a stored procedure to pass a table structure to the procedure, where it then applies the changes. This would require only transferring the just the columns necessary to find the proper row, and the values to be updated.

Since you're transferring the data to be updated, and then running a proc on the remote server to do the update, it does seem like it would run faster to me.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

UMG Developer SSChampion Points: 13482 More actions · Answer 2

It looks to me like they are talking about moving data from a .NET application back into the SQL Server not from one SQL Server to another. For example, you load a bunch of rows into an on screen grid, let the user update them, and then send it back to SQL Server to be persisted in the database.

Jeff Moden SSC Guru Points: 1004439 More actions · Answer 3

Thanks, folks. I'll go back and look again.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 4

I trust you're just looking for opinions and not some concrete answer...I can imagine a couple scenarios where it might be useful:

1. I could see using the approach from the article in a setting where Linked Servers were not an option, for example when operating within a Shared SQL Server Hosting environment. Say you were paying someone to host a SQL instance for you in New York and you wanted to automate the movement of some data from that NY instance to an instance you pay for in LA but you could not use a Linked Server. The scalability of the solution in the article is suspect and bcp out/bcp in may be a better option, but it could work well for small data sets and be quite convenient if the data movement functionality needed to be available from a web app.

2. Another big thing not really talked about, but that comes through in a lot of system designs I see, is the allocation of responsibility. In the article they're describing a setup where a .NET developer holds more of the cards...i.e. a .NET developer can control the movement of this data from any server to any server through a .NET configuration as opposed to having to change a Linked Server which may require a DBA.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Jeff Moden SSC Guru Points: 1004439 More actions · Answer 5

opc.three (4/6/2011)
I trust you're just looking for opinions and not some concrete answer...I can imagine a couple scenarios where it might be useful:
1. I could see using the approach from the article in a setting where Linked Servers were not an option, for example when operating within a Shared SQL Server Hosting environment. Say you were paying someone to host a SQL instance for you in New York and you wanted to automate the movement of some data from that NY instance to an instance you pay for in LA but you could not use a Linked Server. The scalability of the solution in the article is suspect and bcp out/bcp in may be a better option, but it could work well for small data sets and be quite convenient if the data movement functionality needed to be available from a web app.
2. Another big thing not really talked about, but that comes through in a lot of system designs I see, is the allocation of responsibility. In the article they're describing a setup where a .NET developer holds more of the cards...i.e. a .NET developer can control the movement of this data from any server to any server through a .NET configuration as opposed to having to change a Linked Server which may require a DBA.

Correct. Opinions and scenarios. Thanks for your ideas above. 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)