[Webinar] Streamline your web hosting managementRegister Today


What is the fastest SQL Server data transfer ?

Posted on 2007-12-01
Medium Priority
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
  • 3
  • 2
  • 2
  • +1
LVL 18

Accepted Solution

Yveau earned 1200 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 400 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...
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

LVL 10

Assisted Solution

TAB8 earned 400 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

591 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