I just confused what to use SSIS or Linked Server.I have to transfer around 1000 to 2000 records into remote SQL server.Will it(SSIS) be a big hammer for 1000 to 2000 records (Also it is complex from maintenance perspective) Can you please help me to choose? My destination server is SQL server 2008 while source server is SQL server 2005.Both m/c are 32 bit Windows 2003 servers.
Asked
Active
Viewed 102 times
0
-
Will it be a problem to transfer 1000 to 2000 records over linked Server? Only if you are using string for Ethernet cable! But seriously, if the linked server is on your local network, why would it be a problem? If you said 1 few million rows, that might be different. – Mitch Wheat Dec 05 '13 at 07:48
-
@Mitch Wheat:Yes both servers are on and LAN speed is 100 Mbps – Sachin Dec 05 '13 at 07:55
1 Answers
2
SSIS uses Bulk Copy and is very limited with what it can do with existing data. E.g., inserting a million rows is easy and fast, but updates to data that already existed in a table required a cursor-like loop command.
If you truncate and delete, go ahead and use SSIS. Linked Servers will be the same efficiency if you do a truncate or delete, then reinsert data. SSIS may be a little faster due to Bulk Copy turning off/toning down the transaction log for the same records, but you may not be able to perceive the difference in speed for so few records if the data is small. BLOBs in the table would be a different story.
SSIS is a bit more difficult to maintain if you use the GUI, but it can be worth the effort in some cases.
You could also roll your own console app that uses SqkBulkCopy to have the performance of SSIS with less hassle.
ps2goat
- 8,067
- 1
- 35
- 68