Sql-server – Replicate MySQL INSERT to SQL Server table

MySQLsql serverssms

I'm running into a challenge here trying to "sync" a MySQL table with a SQL Server table. My scenario involves my linux-based website submitting form data submissions to a MySQL database on that web server.

What I'm shooting for is to copy any inserts on the MySQL table to the SQL Server table, and I'm not finding a whole lot of support for this. I'm using SQL Server Management Studio and I have my SQL Server database and I've added the MySQL as a linked server.

I had the thought of creating a trigger on that linked server, but it doesn't look like that will work coming from the SQL Server side.

Does anyone have any experience with this? I'm looking for the "best practice" scenario for this type of situation. The syncing between these tables needs to be as close to real time as possible.

Thanks!

Best Answer

This has been asked before and there are a couple of useful posts. But the work is still being left in your hands. You might check these posts:

  1. richardtallent has a post: https://stackoverflow.com/questions/1455018/replication-from-mysql-to-ms-sql
  2. AndrewSQL offered an outlined approach that got more votes at: Replicating a remote MySQL database to MS SQL Server 2008

The bottom line is that there are hints, techniques, and some tools that you might be able to marshal into an approach that suits you. But the work is left in your hands.

In the same thread as AndrewSQL, there is a post from Gaius recommending GoldenGate for MySQL and MS SQL. But I have no experience with it.

Related Question