Solved

What is the fastest SQL Server data transfer ?

Posted on 2007-12-01
8
3,384 Views
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 !
0
Comment
Question by:itproem
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 18

Accepted Solution

by:
Yveau earned 300 total points
Comment Utility
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 ...
0
 
LVL 23

Assisted Solution

by:Racim BOUDJAKDJI
Racim BOUDJAKDJI earned 100 total points
Comment Utility
<<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...

There
0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
Comment Utility
Alternatively,

Use a linked server shared view...Do not do any transfer if you do not need to...Just view it...
0
 
LVL 10

Assisted Solution

by:TAB8
TAB8 earned 100 total points
Comment Utility
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
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
Comment Utility
<<BCP would be the option to go for >>
Certainly not the fastest way but probably the best compromise...;)
0
 

Author Comment

by:itproem
Comment Utility
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  !
0
 

Author Comment

by:itproem
Comment Utility
Also, any other comment, solution is still welcome !

Thank you Experts :)
0
 
LVL 18

Expert Comment

by:Yveau
Comment Utility
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 ...
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Suggested Solutions

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

763 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now