Replicating a remote MySQL database to MS SQL Server 2008

Question

I want to replicate the contents of a MySQL database to a MS SQL Server 2008 database.

Is this possible? Can anyone outline the steps required in order to achieve this?

Thanks.

Answer 1

Personally I'd pull into MS SQL method vs. the push from MySQL method. Why? Well Windows has 32bit and 64bit MySQL ODBC drivers ready to go and setting up a linked server is trivial. I have plenty of MySQL servers linked to from MS SQL. Also, connecting to MS SQL from linux/unix is not always great and you're ususally not able to use all features. FreeTDS has limitations; you may hit them sooner rather than later so why not just skip it. This all assumes you're running MySQL on *nix. If not, it gets a little closer to 50/50 but I'd still choose pulling from MS SQL as it sounds like it's not the "live" database thus putting the load on it for any ETL or processing is more ideal. The GoldenGate solution sounds interesting, but I'm sure it's not free.

Considering I've setup this sort of scenario with both MySQL and Oracle databases replicating to MS SQL I'll provide some tips that have worked best for me:

1. If you can, do your best to determine how you can ensure you're only bringing across delta changes. Merge commands can help with this. Truncating a table and then inserting it all in again bloats your log, uses network bandwidth, and just generally wastes time.
2. If dealing with lots of data be sure to break the transactions up so as not to require a massive log file. Use explicit commits or checkpoints when you've reached a step you know you won't need to roll back from.
3. If the MSSQL db will be for reporting only, do ETL work there so as not to impact the MySQL server. Use a staging database or schema + filegroup to make things easy.
4. Break the data importing into steps. This makes it easy to re-start the import where it failed and/or troubleshoot. An all or nothing approach becomes annoying quickly.
5. Use variables wherever possible to assist the remote db with query plans & index use. Also pay attention to what sort of transaction isolation you're in on the host box and what impact the "replication" queries will have. You don't want to block writers on a live db if you're just pulling data over for reporting or sandbox use.

Hope the tips help!

Answer 2

The same question was addressed on StackOverflow here: Replication from MySQL to MSSQL.

It seems that there are some workarounds, but not a very easy solution.

I believe that you should definitely try building an SSIS package to import necessary data from MySQL DB to MSSQL DB. SSIS allows one to import data from various sources. Then you should be able to schedule the package using windows task scheduler or sql jobs.

Answer 3

If by replication you mean log shipping or something like that I believe you are out of luck. However you can certainly set up a MySQL database as a linked server and roll your own replication scheme. Simplest is just to do periodic snapshots of all the tables using truncate and insert statements. Add complexity as your requirements dictate.

Answer 4

You can use GoldenGate for MySQL and MS SQL to do this. You would simply install the GoldenGate product on each side, then proceed as for a homogeneous replication.

Alternatively, for a "snapshot" replication, you can use a Python (or similar) script to simply connect to both data sources (using UnixODBC and FreeTDS to connect to MS SQL), loop through the tables doing SELECT on one side, for each row INSERT on the other. As MSSQL has transactions and is your target, you can start a transaction, DELETE everything from all tables, do the copy, then COMMIT and it will appear instantaneously as far as connected users on the target are concerned, there will be no inconsistencies (unless these exist on the source of course).