Replication from MySQL to MS SQL

Question

I'm facing a new challenge here. I can't seem to find precedence for replication from MySQL, running on a Linux box to MS SQL Server.

Has anybody done this before?

Most importantly all changes made to the MySQL database should be replicated on the MS database realtime or close. MS database are not likely to be updated in any other way, so a bidirectional facility is not required.

---

I thought one way is to read the changes out of the binary log. Has anyone parsed one before?

Thanks for your help guys.

Solution 1

Triggers in MySQL could be used to catch changes and call a UDF, which could then execute ODBC queries to MSSQL. Likely terrible for performance, though.

If immediate replication isn't required:

• Write triggers in MySQL that capture insert, update, and delete statements in a log table.
• Poll the log table from MSSQL using ODBC and execute them, then delete those log entries.

Of course, T-SQL and MySQL's variant of SQL isn't exactly the same, but it should be close for trivial CUD operations.

Solution 2

Check to see if DBSync will help you do what you want

Solution 3

I had similar task, but I had to replicate from MSSQL 2008 to Mysql in real time.

I tried this application http://enterprise.replicator.daffodilsw.com/ and it worked but it didn't look reliable. But you can check I may be wrong.

Finally I decided to use interface OLE DB and postgress instead instead of Mysql. It works properly.

Comments

[G Berdal]

Thanks. I was going to follow similar points in my method if I was to write an application. Why is the performance issue?

[G Berdal]

Yes. We just started experimenting with this tool and my IT guy who is testing it is more convinced than I am. :) Raj's DBSync looks more rubust to me and it costs only $79.

[G Berdal]

I've got DBSync, tested it, and it does work beautifully for the initial migration. However, it can only do updates by synchronising the entire databale which could take hours in case of a large datatable. So it is not an efficient solution if there are frequent regular updates to the datatable. - It is a very good value for money for simple migration though...

[G Berdal]

This certainly seems the only efficient way to do this... Synchronising large datatables could take way too long using any tool, therefore cannot achieve a near real time synchronisation. By keeping track of the updates the amount of rows that I needed to iterate through has been significantly lower so I could use it efficiently to run my updates. - Thanks Richard

[G Berdal]

...I just wanted to add that its ability to deal with invalid data is limited, too. :(