在 SQL 服务器之间传输数据的最佳做法是什么? [英] What is the best practice to transfer data between SQL servers?

问题描述

我们有许多内部 SQL Server 正在运行，并且有一些日常作业传输数据.并且我们还有一些外部数据库，我们将使用视图或txt来传输数据.

现在，我们正在使用 DTS &安全情报局.但是由于一些原因，我们的经理不喜欢DTS/SSIS，所以我们要从DTS/SSIS搬出去，但我们仍然需要传输数据.他们建议开发一些应用程序，用于加载数据/文件，然后插入到目的地.

这是一个好的解决方案吗?有什么建议吗?

我认为唯一合理的原因是:如果出现性能问题，很难在 DTS/SSIS 中找到原因

非常感谢！

解决方案

在服务器之间传输数据的方法有很多种.您可以将链接服务器与 OPENROWSET 查询一起使用，您可以使用 BCP 实用程序和临时文件，或者您可以使用 PowerShell 脚本仅举几例.

最合适的方法取决于您的具体情况，但对我而言，SSIS 通常是最合适的技术，尤其是在涉及转换的情况下.SSIS 保持着 ETL 世界纪录(尽管这是对大量硬件的人为实验)，因此应该能够满足您的性能需求.

您可以做很多事情来提高 SSIS 数据移动的性能，但您的问题中没有足够的信息来确定您需要做什么，但这里有一些关于 SSIS 性能的提示:

• 确保数据流中的缓冲区大小设置适当，通常高于 10000 行默认值，但确切大小取决于您的内存容量.

• 确保您使用的是快速加载的 OLEDB 目标

• 下载平衡数据分发器组件并并行加载表.这还需要在堆或按顺序的数据上使用 TABLOCK，在聚集索引上使用 TF 610.

• 避免同步转换，例如 Sort.

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 are using DTS & SSIS. but because of some reasones, our manager do not like DTS/SSIS, so we are moving out from DTS/SSIS, but we still need to tranfer data. they suggest to develop some application, for load data/file, and then insert into the destination.

is this a good solution? any suggestion?

I think the only reasonable reason is: if there's a performance issue, it's hard to find the reason in DTS/SSIS

Thanks very much!

解决方案

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.

这篇关于在 SQL 服务器之间传输数据的最佳做法是什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！