July 14, 2004 at 5:51 pm
Hello,
I have a question regarding DTS and Linked server speed and usability.
I have about 400,000 rows to transfer on a daily basis between two servers. I can set up a DTS Package to do it, or I can run a job doing the same using linked servers. Which one is the best or preferred way to do it. When do you get to the point where it becomes too 'big' for linked servers and have to use DTS, and which is faster, as far as I know they both use ODBC.
Thanks.
July 14, 2004 at 6:20 pm
Are both the DB's on SQL Server platform's? If so, there is native connectivity associated between linked servers and DTS packages if configured correctly. Personally, I'd use a DTS package to move the data instead of creating a linked server and then creating SQL statements to "insert" the data.
If the db's are on server's other than SQL Server, then you are correct, both are ODBC connections and therefore are very slow. I would still choose DTS with that option as the management of the data transfer is easier.
Let me know if you have any other questions.
July 14, 2004 at 8:05 pm
Yes both sql 2k.
July 15, 2004 at 10:05 am
If both servers are SQL2000 servers, just use the native SQL Server connections within DTS. I feel that will be your fastest option. You can then also create a job to move the data automatically and send you a status email after they are complete (that is if you have SQL Mail/SQL Agents configured).
Let me know if you have any further questions.
July 16, 2004 at 5:17 am
Even if you choose the Linked Server option you could still wrap it all up in a DTS package for ease of manageability.
Regards
Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson
July 16, 2004 at 7:44 am
I agree with Jamie, I use this type of set up en a daily basis for our data warehouse, I export date from more than one source, I use the linked server option as part of my code in the DTS packages.
July 18, 2004 at 4:02 pm
Thank you for all the replies, I think the evidince is compelling to use DTS rather than Linkes servers.
Cheers
Rykie
July 18, 2004 at 7:22 pm
Depending on how fast you want this to run, you might also consider bcp. You can wrap this in DTS also.
Derrick Leggett
Mean Old DBA
When life gives you a lemon, fire the DBA.
July 20, 2004 at 1:13 pm
Three Reasons for DTS over Linked Server:
1: The DTS Data pump Item will allow you to set commit batch size per transaction which will improve overall performance and reduce the amount of space required within the recipient system's transaction log to commit the insert.
2: Another important point to remember is transactional consistency across linked servers is an issue unless you use Xact abort. DTS itself handles everything through OLE or ODBC which ever connection driver you choose effectively bypassing this issue.
3: Security between the two servers is another issue with a linked server in place anyone on the source server can access or change according to the account used for the link with out the ability to track user modifications to data on the linked server's end.
============================
Richard S. Hale
Senior Database Marketing Developer
Business Intelligence
The Scooter Store
RHale@TheScooterStore.com
DSK: 830.627.4493
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply