6

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.

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.

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

Hadi
  • 36,233
  • 13
  • 65
  • 124
Cody Brumett
  • 61
  • 2
  • 2
    You can try SSIS. How big it this table in GB? Be sure that during initial load this table can be bulk loaded. https://learn.microsoft.com/en-us/sql/relational-databases/import-export/prerequisites-for-minimal-logging-in-bulk-import?view=sql-server-2017 – Piotr Palka May 14 '19 at 23:20
  • I would suggest also trying [SSMA](https://www.microsoft.com/en-us/download/details.aspx?id=54257). Due to the size of the table, it may be one of the better options. – Matt May 15 '19 at 08:48
  • What is causing Attunity to fail? If it is due to connectivity while transporting the data, you may face the similar challenges with other tools. At that point you may be forced to dump the data to csv file(s), move the file(s) and then load them. It is not the most efficient way by any means but it will work when other techniques fail. Just be sure to bulk load the files to speed up the process. – camba1 May 15 '19 at 14:03
  • Attunity fails due to connectivity during transport. the failure has happened at 1 week, 1 month, ect, so it isn't tied to source/target transformation issues. – Cody Brumett May 15 '19 at 20:13
  • The table in question is 1.2TB in size. It also has enum columns so I don't know if SSMA will work. Has anyone had more success using ado.net vs. odbc connector through SSIS ETLing something so large? – Cody Brumett May 15 '19 at 20:34
  • Hi, you can try MapReduce using Hadoop. As a distributed system, it lets you process lots of data very fast. – Andrei Petrut May 16 '19 at 08:01
  • is your issue solved? – Yahfoufi Jul 05 '19 at 09:45

2 Answers2

1

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.

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.

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.

PausePause
  • 746
  • 2
  • 9
  • 21
0

One of the most efficient way to load huge data is to read them by chunks.

I have answered many similar question for SQLite, Oracle, Db2 and MySQL. You can refer to one of them for to get more information on how to do that using SSIS:

On the other hand there are many other suggestions such as drop indexes in destination table and recreate them after insert, Create needed indexes on source table, use fast-load option to insert data ...

Hadi
  • 36,233
  • 13
  • 65
  • 124