通过拨号连接批量复制 SQL 数据的最佳方法是什么？
我有一些远程 SQL 服务器，需要定期从中提取大量数据（例如每台服务器 5,000,000 行）。 该数据还需要格式化并通过 FTP 传输到另一台服务器。 拨号部分没问题，可以与服务器连接并通信，但有时连接速度很慢，可能只有19Kbps。
连接后，我尝试使用 System.Data.SqlClient.SqlBulkCopy，但它只会引发超时错误。 我已将大容量复制的超时设置为 7200 秒，将每个数据库的连接字符串的超时设置为 900 秒，但仍然在 30 秒左右超时。
我尝试使用 DataReader 并运行 SqlCommand.ExecuteNonQuery() 来插入每一行，效果很好，但速度很慢，有时连接会丢失。
我还尝试在远程服务器上设置 DTS 包，安排它们转储我需要发送文本的数据，然后尝试下载文件。 但是，它们可能是几百 MB（在某些服务器上甚至可能是 GB），并且拨号连接通常会在某些时候断开。
I have a few remote SQL servers that I need to pull a large amount of data from regularly (say 5,000,000 rows per server). This data also needs to be formatted and FTPed to another server. The dial-up portion is fine, I can connect and communicate with the server, but sometimes the connection is slow, maybe only 19Kbps.
Once connected I tried to use System.Data.SqlClient.SqlBulkCopy, but it just throws a timeout error. I've set the timeout on the bulk copy to 7200 seconds, and the timeout on the connection strings to each database at 900 seconds, but it still times out at around 30 seconds.
I tried using a DataReader and running SqlCommand.ExecuteNonQuery() to insert each row, which works OK, but it's slow and sometimes the connection is lost.
I also tried setting up a DTS package on the remote servers, scheduling them to dump the data I need to text, and then tried downloading the files. But, they can be a few hundred MB (possibly even GB on some servers) and the dial-up connection is usually dropped at some point.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（7）
如果可以的话，将其拉上拉链，ftp 并在您这边进行批量插入。
If it is an option, zip it up, ftp and do the bulk insert on your side.
距离有多少英里？ 可以通过运动鞋网制作 DVD 吗？
How many miles away is it? Can you sneakernet a DVD?
关于该主题的一些想法：
在源代码中构建一个例程以进行提取
数据并将其分成几个
较小的文件。 通过 FTP 传输文件
单独进行一些检查
确保他们拥有一切的机制
到达。
如果您可以在源处识别已更改或新的数据，请在源处放置某种更改的数据捕获功能，并仅发送增量（如果您已经这样做了，我们深表歉意）。
如果
压缩分块文件以减少要传送的数据大小。
Some thoughts on the topic:
Build a routine at source to extract
the data and chop it into several
smaller files. FTP the files
individually and have some check
mechansim to ensure they have all
arrived.
If you can identify changed or new data at source, put some sort of changed data capture function at source and only ship the deltas (apologies if you're already doing that).
Compress the chunked files to reduce the data size to ship.
使用 ZIP 格式压缩数据，它内置于 .NET
http://www.windowsdevcenter.com/pub/a/windows/2006/09/12/using-data-compression-in-net-20.html
如果数据仍然太大，您可以尝试使用外部库/可执行文件来 RAR 或 7Zip 压缩数据，因为这将是 ZIP 副本大小的一半。
Compress the data using the ZIP format, it's built into .NET
http://www.windowsdevcenter.com/pub/a/windows/2006/09/12/using-data-compression-in-net-20.html
If the data is still too big, you can try using an external library/executable to RAR or 7Zip the data as that will be half the size of the ZIP'd copy.
bcp.exe 支持“out”参数，允许您转储SQL 语句的输出到平面文本文件。
这不是一个优雅的解决方案。 如果您想以编程方式执行此操作，您将进行流程调用。 然而，它确实完成了工作。
然而，您可能想问的问题是，围绕没有宽带连接与获得宽带连接（如果可能）的限制创建解决方案的成本效益如何。 我预计，无论您决定采用哪种解决方案，在使用拨号时都会遇到持续的维护问题。
bcp.exe supports an "out" parameter that allows you to dump the output of an SQL statement to a flat text file.
It's not an elegant solution. If you want to do it programatically, you'll be making process calls. It does, however, get the job done.
The question you might want to ask, however, is how cost effective is it to create a solution around the limitations of not having a broadband connection versus getting it (if possible). I expect that regardless of the solution you decide on, you'll have ongoing maintenance problems with using dial-up.
您可能想要使用组合方法。
BCP OUT 本机格式的表。
使用可生成一系列文件的工具压缩文件。
使用
ftp 单个文件。 您将能够继续传输。
我发现 7Zip（免费开源）具有最佳的压缩效果，并且会生成一系列您指定大小的编号文件。 我相信它可以从命令行运行。
华泰
安迪
You'll probably want to use a combination approach.
BCP OUT the table in native format.
Compress the file with a tool that will make a series of files.
ftp the individual files. You'll be able to continue the transmission.
I've found that 7Zip (free opensource) has the best compression and will make a series of numbered files of a size that you specify. I believe it can be run from the command line.
HTH
Andy
我最终做的是用 C 创建一个小应用程序（其中一些是 WINNT，这是最简单的方法，它还允许其他人在必要时手动检索数据，而无法更改源），它需要一些参数构建我需要的查询。 然后，它运行查询并将结果转储为所需的 CSV 格式。 然后它会调用最高压缩级别的 7zip 将数据压缩得尽可能小（这会将 500MB 的文件减少到大约 20MB）。
因为我必须先将数据带回给我，然后才能将其通过 FTP 传输到必要的位置，并且远程服务器没有任何互联网访问权限，所以我仍然只是将文件复制到 Windows 共享，然后在本地解压缩，然后将未压缩数据（根据请求）通过 FTP 传输到目的地。
这可能不是最好的方法，但它确实有效。 谢谢
What I ended up doing was creating a small app in C (a few of these are WINNT and that was the easiest way, it also allows other to retrieve the data manually if necessary without the ability to alter the source) that takes a few arguments to build the query I need. It then runs the query and dumps the results in the required CSV format. Then it calls 7zip with the highest compression level to compact the data as small as possible (this reduces a 500MB file to about 20MB).
Because I have to bring the data back to me before I can FTP it to the necessary place, and the remote servers don't have any internet access, I'm still just copying the file to a windows share, then decompressing it locally and FTP the uncompressed data (as requested) to it's destination.
This may not be the best approach, but it's working. Thanks