What is the fastest SQL Server data transfer ?

Posted on 2007-12-01
Last Modified: 2013-11-30
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 !
Question by:itproem
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +1
LVL 18

Accepted Solution

Yveau earned 300 total points
ID: 20388923
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 ...
LVL 23

Assisted Solution

Racim BOUDJAKDJI earned 100 total points
ID: 20389027
<<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...

LVL 23

Expert Comment

ID: 20389037

Use a linked server shared view...Do not do any transfer if you do not need to...Just view it...
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 10

Assisted Solution

TAB8 earned 100 total points
ID: 20389147
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
LVL 23

Expert Comment

ID: 20389200
<<BCP would be the option to go for >>
Certainly not the fastest way but probably the best compromise...;)

Author Comment

ID: 20390839
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  !

Author Comment

ID: 20390856
Also, any other comment, solution is still welcome !

Thank you Experts :)
LVL 18

Expert Comment

ID: 20405463
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 ...

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Display Date and Time 7 48
SQL Server 2005 running VERY slowly on new hardware 22 62
Datatable / Dates ? 4 30
how to use ROW_NUMBER() correctly 8 44
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question