What is the fastest SQL Server data transfer ?

Dear Experts,
What is the fastest way to transfer data from tables (views) between two SQL Servers ? By using linked servers - distributed transactions, SSIS packages, replications or something else ?
The data will be transfered every night after scheduled job starts or by user request which should trigger some job or stored procedure or SSIS package. The data transfer should start only when it is scheduled or when user requests for it which could happen in the case of failed scheduled transfer. So, there is no special reason to use replications. The data validation is designed and it starts after tranfer is over and compare count and check sum on two sites tables.  The transfers are between different continents, so it's possible to have slow or failed network links sometimes.
Also, I would appreciate if you can paste a web link where I can read some smart compares, analysis or documentation about all types of SQlL Server 2000/2005 data transfers ?
Thank you !
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Replication can only be used if you want a number of tables exactly copied to the other location. It can be scheduled to synchronize only once a day and the big advantage is that it has some failure recovery in it. All other options do not have that out of the box. So replication sounds like a good option.

Linked Server is also a good option, pretty simple to set up and easy to maintain. Also creating a stored procedure to transfer the data can be scheduled to run once a day.

SSIS can be an option, if you only transfer a bunch of tables (like with the replication) don't start modifying data using the SSIS for large tables, as the approach SSIS uses is sometimes not the most efficient one and the same results can be achieved by writing some simple SQL code a lot faster ! And of course this feature can be scheduled to run once a day as well.

Then the issue of the network and the cross-continental connection. As you have to transfer a lot of data, it will pobably not matter what mechanism you would choose as the amount of data you have to transfer will be equal. There might be an alternative if the connection turns out to be the bottleneck. Try BCP to extract the data from the database (real fast !!!) then compress the file, using zip or a similar tool. Then transfer the zipped files (which can be less then 10% of the original amount of data, depending on the layout) and on the other continent, unzip them and use BCP again to import the data into the database (again, REAL fast !!!) If there wasn't a filesystem I/O bottleneck, BCP would be the option to go for ... (In good old Sybase on Unix you could do a BCP out to a so called pipe and from the pipe a BCP in to the target database. As a Unix pipe is actually just memory ... that was fast transferring ! ... but we're stuck with Windows ...)

So ... I would certainly try a few scenarios as they all have there own pros and cons ...
Last tip, try the scenario in a local test as well, so you can see what the effect of the narrow line across the continent has on that specific scenario.

Hope this helps ...

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
<<What is the fastest way to transfer data from tables (views) between two SQL Servers ?>>

> Put a dedicated fiber optics between the two servers
> Make sure you have 15rpm RAID10 disks and sufficient RAM on the source machine and destination machine

> Partition the source table into the smallest partition scheme that fits the criteria by which you isolate the data you must transfer
> Partition the destination table in a way that you minimize the number of partition reorg in case a set is included into the new partition...
> use partition SWITCH to transfer the data from the source partition into the destination partition..

That is the fastest you can achieve using SQL Server...

Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:

Use a linked server shared view...Do not do any transfer if you do not need to...Just view it...
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

BCP would be the option to go for ...  Fast  ...  Structured  ....  and you could put the data collection and load process all ina dts or ssis package
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
<<BCP would be the option to go for >>
Certainly not the fastest way but probably the best compromise...;)
itproemAuthor Commented:
Thank you all !
I agree with Yveau about replication, but they want to start manually transfer between two points in the case of the failure. That is design plan. No data synchronisation out of scheduled or user request. That means that Distribution Agent is stoped and it should start only on job or user request which will call stored procedure and start distrib agent, and data validation procedure is called after that.  So, practically replications should work only when job call stored porcedure or user request call stored procedure which will start snapshot, logreader and distrib agent and only for specific publication. That means that replication architecture will be used as any other simple data transfer though there can be problems with subscription expiration if there is no synchronisation more than 72 hours. Increasing this period is not recommendable.
So, after this further explanation do you still think that replication is the best solution ? I'm really interested in your answer because initial plan is to use replications, but I think in this situation that will just slow the process and cause some problems, because replications will act as simple data transfer on demand. And replications are probably not faster then BCP loaded in SSIS.

Also, I'm curious which type of data transfer replication use in the low level of their architecture ?

Thanks  !
itproemAuthor Commented:
Also, any other comment, solution is still welcome !

Thank you Experts :)
If you state it that way ... it's not a good thing to use replication if you want to disable all of it features. Then go back to building SSIS packages or scripting BCP transfers. I've done some extensive testing with DTS and BCP back in the old days of SQL 2000 and discovered that DTS used the BCP command under the hood. So I wouldn't be surprised if SSIS will use it as well where applicable. Just a hunch, no solid proof for that ... yet.

And also the option of a linked server ... still a valid option with all the notes you made.

... so the choice seems to be down to two ... replication is out ... if I were in your shoes ... or reconsider the design :-)

Hope this helps ...
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.