Solved

What is the fastest SQL Server data transfer ?

Posted on 2007-12-01
8
3,405 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
[X]
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
8 Comments
 
LVL 18

Accepted Solution

by:
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 ...
0
 
LVL 23

Assisted Solution

by:Racim BOUDJAKDJI
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...

There
0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 20389037
Alternatively,

Use a linked server shared view...Do not do any transfer if you do not need to...Just view it...
0
Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

 
LVL 10

Assisted Solution

by:TAB8
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
0
 
LVL 23

Expert Comment

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

Author Comment

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

Author Comment

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

Thank you Experts :)
0
 
LVL 18

Expert Comment

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

Featured Post

Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
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.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

749 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