What Is The Best Practice To Transfer Data Between SQL Servers?

We have many internal SQL Servers running, and there are some daily jobs transfering data. and we also have some external database, we'll use view or txt to tranfer data. now, we a

Solution 1:

There are many methods of transferring data between servers. You could use a linked server with a OPENROWSET query, you could use the BCP utility, with a staging file, or you could use a PowerShell script to name just a few.

The most appropriate depends on your exact circumstances but for me, SSIS is often the most appropriate technique, especially if transformations are involved. SSIS holds the ETL world record (although this was a contrived experiment with massive hardware) so should defiantly meet your performance needs.

There are many things that you can do to improve the performance of SSIS data movements, and there is not enough information in your question to determin exactly what you need to do, but here are a few pointers for SSIS performance:

• Make sure the buffer size in the data flow is set appropriately, normally higher than the 10000 rows default, although the exact size depends on your memory capacity.

• Make sure you are using OLEDB Destination with Fast Load

• Download the Balanced Data Distributor component and parallel load the tables. This will also require TABLOCK on a Heap or per-ordered data and TF 610 on a Clustered Index.

• Avoid synchronous transformations such as Sort.