5

I have this requirement to migrate a live SQL server 2008 to MySQL server 5, I have tried the MySQL migration toolkit but its throwing lots of migration errors. Someone here in Stackoverflow suggested that I use Talend. I installed Talend Open Studio for Data Integration but its quite a general purpose tool that there's no quickstart to do database migration like what I really needed.

Where can I find a quickstart guide to use Talend for such job?

quarks
  • 33,478
  • 73
  • 290
  • 513
  • Do you have a source and a target step? Specify source as SQL Server and Target as MySQL and create a job for a each table – Raj Jul 27 '12 at 10:18
  • Related: http://stackoverflow.com/questions/11619177/sql-server-2008-r2-to-mysql-migration/12948473#12948473 – Ashwin A Oct 18 '12 at 06:33

4 Answers4

12

Talend is quite generic data integration tool, but you should get the job done by following these steps (which actually apply to any database-types: PostgreSQL, Oracle, etc.):

  1. Create a new database connection in Repository-panel, to the Metadata-"Db Connections"-node. Name it and select "DB Type" as "Microsoft SQL Server" (or whatever is your source database) and give the connection details.
  2. Create another new database connection, but this time as "MySQL" (or whatever is your target database).
  3. Create a new job to "Job designs" (in Repository).
  4. Expand the "SQL Server"-DB-connection from the Metadata, and drag the desired table (under "Table schemas") to the work-area of the job AND select tMSSqlInput.
  5. Drag the MySQL-DB-connection from the Metadata to the work-area AND select tMysqlOutput. Define the target table.
  6. Click Palette and drag tMap component to the work-area between the two previous components.
  7. Connect tMSSqlInput to tMap and tMap to tMysqlOutput with arrows. You do that by right-clicking the tMSSqlInput, selecting Row-Main and dragging the arrow to the tMap. Same from tMap to tMysqlOutput.
  8. Double-click the tMap, select all columns from the left and drag them to the right. The tMap component allows you to make data transformations. It might be that you wouldn't even need to use the tMap between the database-components. But it is good to have just in case.
  9. Run the job, and troubleshoot as necessary. If something goes wonky, restart Talend Studio or just close and open the job, in many occasions it helps.
John Mayor
  • 577
  • 6
  • 13
  • After Step:3, one might need to right click on the newly created DB connection and select Retrieve Schemas to retrieve the tables. – saravana_pc Feb 24 '17 at 11:02
  • In case if there are hundreds of tables in input database, how we should go ? – DEV Sep 15 '17 at 09:35
1

The MySQL Migration Toolkit is deprecated long time ago. You should try the new Migration Wizard that comes with MySQl Workbench 5.2.41. It will help you to migrate both the tables and their data. You can have an overview of its capabilities here:

How-To: Guide to Database Migration from Microsoft SQL Server using MySQL Workbench

Sergio
  • 4,537
  • 4
  • 33
  • 41
0

If you need to get started with Talend, I can suggest you check our documentation page http://www.talend.com/resources/documentation.php , where you will be able to find installation guides and user manuals.

Also, there's an interesting section on our website where you will be able to check out our tutorials, that should help out: http://www.talendforge.org/tutorials/menu.php

Bart
  • 19,692
  • 7
  • 68
  • 77
Talend Data
  • 113
  • 3
  • 5
    Answers like these tend not to be very helpful after a year or two, since most companies have a tendency to change their URL structure without maintaining the proper (permanent) redirects or correcting external links they post. That's why [it's suggested](http://stackoverflow.com/questions/how-to-answer) that the most relevant parts of the linked content be quoted in the answer. Also, if all you can provide is "here's a link to the documentation", then that should be a comment rather than answer. – Lèse majesté Jul 27 '12 at 13:37
  • 1
    @talend-data Your answer is promising, I can see lots of information, however can you point me to a specific resource on how to do database migration, a web link perhaps? – quarks Jul 27 '12 at 15:47
0

xybrek, unfortunately there's no tutorial that covers your specific case exactly.

Download the manuals that Bart linked to and start trying things out in Talend.

The components you'll be looking at are: tMssqlInput - reads a table in a MsSQL db tMySQLOutput - directs a flow of data, say the output of the mssqlinput component, into a mysql table tMySQLRow - lets you execute a query on a mysql table/database

And possibly others. These are just some pointers to the more obvious components.

You can read more about those and see examples in the Reference Guide manual which covers all components in Talend.

Good luck!

sdragnev
  • 468
  • 3
  • 10