Synchronizing SQL Server 2005 with MySQL

Question

I'm need to copy several tables wholesale from a third-party SQL Server 2000 database to a MySQL 5 database and keep them synchronized--i.e., when some CRUD happens on the SQL Server tables, I'd like it to be reflected in the MySQL versions in a timely manner. Now, I know there are ways to do this with 2000, but some time in the near future, the SQL Server database will be upgraded to 2005, which seems to not offer the same loopholes that 2000 does.

So, is there any way to replicate/synchronize data from SQL Server 2005 to MySQL that doesn't involve me comparing tables in the two databases programmatically? If so, will it also work with SQL Server 2000?

Answer 1

http://stackoverflow.com/questions/169086/suggestions-for-replication-of-data-from-ms-sql-2005-and-mysql

One person suggests not messing with such a scheme and setting up another sql server.

SQL express has some constraints on performance, but maybe that's an option for you. I can't find anything that can work, out of the box.

Answer 2

I've previously done this, although it will completly depend on your definition of timely!

I used myODBC (http://dev.mysql.com/downloads/connector/odbc/5.1.html) to setup the mySQL server as a linked server. You can then use the openquery command (http://msdn.microsoft.com/en-us/library/ms188427.aspx) to get the data from the remote table.

All you then need to do is write a stored procedure which does an insert/update where not exists the data in the remote database. The SQL is a bit nasty but it is doable! Once you have an SP that works you can set it to run as a trigger somewhere or run it as a job in MSSQL server.

Answer 3

I'm also going to suggest connecting SQL Server to MySQL via Linked Server. Then, create AFTER triggers on the related tables that makes use of INSERTED an DELETED psuedo-tables to ensure that all local operations are reflected on the remote databases. For example:

CREATE TRIGGER tr_Orders_Insert ON Orders AFTER INSERT AS
    BEGIN
        INSERT INTO MySQLServer...Orders (OrderID, OrderDate)
        SELECT OrderID, OrderDate FROM INSERTED
    END
GO