October 25, 2017 at 4:33 am
Some advice appreciated from someone familiar with large scale migrations. Simple answers welcome as I'm no specialist on SSMA/SSIS.
I am planning a migration from mySQL -> SSIS 2016. It includes massive volumes of data (largest mySQL table =1 billion rows, for ~15 fields). The tool of choice is SSMA.
I've done some testing and, while many tables migrate well (in a few hours, no errors), the billion rows tables (1billion =10^9)) could take up to 2 weeks (not a great bandwith in my company), and tend to crash in the middle of the process, making it a no-option. I'm looking for a reliable alternative to migrate these tables separately.
My questions on SSMA & migrations are:
- Does SSMA have an option to filter what's to be migrated? E.g. split the table into 5 smaller parts (0-200M, ..., 800-1000M rows) and do it in multiple steps.
- Is there a standard/better method to migrate these volumes? Main reason to use SSMA is compatibility, it takes care of everything. Other methods like this:
mySQL > Server menu - Data export -> creating a *.SQL dump file -> running it in *.SQL Server Management Studio to insert the data
Problem? Not sure how compatible this would be. My doubt is whether a *.SQL file (maybe 200-300 GB size) can be loaded into SSIS, opened, and ran to populate the destination table.
3. I've been told the reason 10^9 rows take so long is that, maybe, the source table having indexes, when SSMA imports it needs to recreate the index for every rows subset it transfers (say, every 1000 rows). I don't really get this (might not be true), but I wonder if it would be possible to import the table w/o indexes quicker, and maybe later add the indexes in SSIS?
Any other approaches or materials on how to migrate content are welcome, as I'm blind flying here. It might be that there are much better methods out there I haven't heard about.
Thanks in advance, P.
October 25, 2017 at 6:41 am
Possibly an alternate (although likely no faster) method would be to set up a linked server on the SQL Server to the MySQL server, create the new table in SQL Server, then select into it through the linked server. I'd probably leave the indexes off the destination table until after you get the data in, although either way the indexes will also take a while (either by adding time during the insert, or time to create them at the end.)
You'll probably still be looking at a long time (no guesses on my part as to how long,) but we used a similar method here when we migrated some databases off Oracle to SQL Server.
October 25, 2017 at 7:02 am
You might also want to consider whether or not you can place the two servers side by side and have the network cards connected to the same router, and then you have at least a gigabit connection (assuming reasonably modern NICs in each server). Alternatively, maybe even consider adding a Fiber NIC to both machines and again, sitting them next to each other, with a dedicated fiber connected to both fiber NICs. This might just give you some options that would not be possible otherwise. SSIS might also be a tool you can use to load this particular table into the destination server, as it can move pretty quickly at times. EDIT: removed 180 million rows in 15 minutes claim - just remembered that the Teradata server had to process 180 million rows in a query to get a GROUP BY result, and then send a much smaller number of rows, and that took only 15 minutes, and thus the claim was bogus. The bulk load capability of SSIS is still impressive though.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
October 25, 2017 at 7:32 am
Thanks SSCoach, never heard of a linked server (only of linked tables between, say, Excel & SQLServer), but that sounds an interesting method. I'll digg down a bit.
Thanks SSCoach: the 2 servers are side by side, in the samee room. You might ask, why do the migration thorugh the network then? No idea, as I'm not an IT guy. I have no idea how to connect cards to routers, and the company has no resources to install fiber for this. I'll ask IT if that's possible with our current config.
I'm happy with any method that cuts down time for our current weeks (impracticable in a service company) to, say, 1 day or 2 (which would be fine if done on a weekend).
October 25, 2017 at 7:46 am
a_ud - Wednesday, October 25, 2017 7:32 AMThanks SSCoach, never heard of a linked server (only of linked tables between, say, Excel & SQLServer), but that sounds an interesting method. I'll digg down a bit.Thanks SSCoach: the 2 servers are side by side, in the samee room. You might ask, why do the migration thorugh the network then? No idea, as I'm not an IT guy. I have no idea how to connect cards to routers, and the company has no resources to install fiber for this. I'll ask IT if that's possible with our current config.
I'm happy with any method that cuts down time for our current weeks (impracticable in a service company) to, say, 1 day or 2 (which would be fine if done on a weekend).
Quick fyi, SSCoach is actually jasona.work, and I'm sgmunson. Take a closer look at the left side of any given post and you can see what the poster's forum nickname is. Some of us use a signature that includes a first and possibly last name. You can call me Steve...
Given that both servers ARE in the same room, you might be able to have the IT folks set up a dedicated gigabit ethernet connection between the two for the duration of the migration, without the expense of fiber. Most servers these days have multiple NICs (network interface cards), and in most cases, what used to be functionality that came with adding an expansion card (and thus the name NIC) is now directly on the motherboard, so all you see is the two RJ45 network ports somewhere on the back side of the server.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
October 25, 2017 at 7:58 am
Sorry, I just didn't check the avatars correctly sgmunson and jasona.work. A newbie, but still able to identify user names. 🙂
Yes, both servers in the same room so probably the NIC cards approach would work !! Will try next week.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply