如何一次性将40亿条记录从MySQL加载到SQL Server [英] How to do a one-time load for 4 billion records from MySQL to SQL Server

问题描述

我们需要在具有4+十亿条记录的表上进行初始数据复制，以从源MySQL(5.5)定位到SQL Server(2014).有问题的表很宽，有55列，但是它们都不是LOB.我正在寻找以最有效的方式复制此数据的选项.

We have a need to do the initial data copy on a table that has 4+ billion records to target SQL Server (2014) from source MySQL (5.5). The table in question is pretty wide with 55 columns, however none of them are LOB. I'm looking for options for copying this data in the most efficient way possible.

我们已经尝试通过Attunity Replicate加载(对于不大的表，它的工作非常出色)，但是如果使用Attunity Replicate进行的初始数据复制失败，那么它将从头开始，从而浪费了复制数据所花费的所有时间.进行修补后，此表可能需要花费3个月以上的时间才能加载Attunity并不是解决方案.

We've tried loading via Attunity Replicate (which has worked wonderfully for tables not this large) but if the initial data copy with Attunity Replicate fails then it starts over from scratch ... losing whatever time was spent copying the data. With patching and the possibility of this table taking 3+ months to load Attunity wasn't the solution.

我们还尝试了使用链接服务器进行较小的批量加载.这是可行的，但似乎根本没有效率.

We've also tried smaller batch loads with a linked server. This is working but doesn't seem efficient at all.

一旦数据被复制，我们将使用Attunity Replicate处理CDC.

Once the data is copied we will be using Attunity Replicate to handle CDC.

推荐答案

对于这样的事情，我认为SSIS是最简单的.它设计用于最大1TB的大插入量.实际上，我建议此MSDN文章我们在30分钟内加载了1TB，所以您可以.

For something like this I think SSIS would be the most simple. It's designed for large inserts as big as 1TB. In fact, I'd recommend this MSDN article We loaded 1TB in 30 Minutes and so can you.

执行简单的操作(例如删除索引)和执行其他优化(例如分区)将使您的加载速度更快.虽然30分钟不是一个可行的拍摄时间，但是让SSIS程序包在工作时间之外运行将是一项非常简单的任务.

Doing simple things like dropping indexes and performing other optimizations like partitioning would make your load faster. While 30 minutes isn't a feasible time to shoot for, it would be a very straightforward task to have an SSIS package run outside of business hours.

我的公司没有像您这样规模的工作，但是即使每天进行了较差的优化，我们每天刷新数据库的数量也超过了1亿个，并且耗时不超过45分钟.

My business doesn't have a load on the scale you do, but we do refresh our databases of more than 100M nightly which doesn't take more than 45 minutes, even with it being poorly optimized.

这篇关于如何一次性将40亿条记录从MySQL加载到SQL Server的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！