Suggestions for replication of data from MS Sql 2005 and MySql

Question

My company currently has a transactional db running on Sql Server 2005. We are going to add a MySql (running on linux) reporting db. We'll need to get replication running from the MS-Sql db to the MySql db. It doesn't have to be real time but should be within a few minutes.

I've got pretty good MSSql Dev skills and so-so dba skills but no MySql background. The MySql guy on our team has no MSSql experience.

I was wondering if anybody has setup anything similar and might have some suggestions. I've seen some things on migrating data between the two but not much for on-going replication. Right now my best guess is to set something up in SSIS and run it under the Sql Agent. I'm going to work on the SSIS idea for now but welcome any suggestions.

Thanks in advance

Answer 1

A third-party application claims the ability to do this: Daffodil Replicator. I think it's available both as Open Source and Enterprise.

Answer 2

Friend of mine for almost the same case (he copies some data from just a few tables from MSSQL to MySQL) built something like that:

• Added trigger to each table which will be replicated. Trigger save primary key, operation type (i)nsert/(u)pdate/(d)elete and source table name in special table (less or more).
• Small .NET app scans this special tables for new keys every few minutes and reads data from source MSSQL tables and save them in destination tables in MySQL (less or more).

This works fine because:

• Tables don't change a lot.
• He copies just a few columns.

Pros:

• Fast & easy to implement & change.

Cons:

• In house made tool is not perfect :).

Answer 3

I think it depends on what reporting software you'll be using on top of the MySQL database. If you're using Pentaho - they have software to handle this situation. If reporting is just going to be ad hoc and the structure will remain exactly the same, I would seriously consider setting up another MSSQL instance and working with that. If you already have MSSQL, don't putz around trying to make the two friendly with each other. You should be able to have the second MSSQL instance tied down to only limited resources so that the transactional db never gets impacted even if they're on the same machine.

Answer 4

SSIS ETL seems like the simplest way to go. You could actually export to a staging area (CSV files) and than import to MySQL. This would take care of different format problems. If you get creative, MySQL supports the CSV storage engine (see here), so this could save the load step in SSIS.