Monday, April 20, 2009

Attempt: Replicating data from MS SQL Server to MySQL

I took my first stab at getting MS SQL Server to push some data to MySQL. My approach I tried was to use an INSERT trigger that inserts data into a MySQL table using the Linked Server feature in MS SQL Server.

I did some reading on Linked Servers and read that it only supports OLE DB drivers and since the only OLE DB drivers I could find were either old or commercial, I decided to use the MySQL ODBC driver and use the OLE DB to ODBC Linked Server option. Everything went well for the most part - I was able to create the linked server, I could select and insert data into the MySQL table from within SQL Server. The part that did not work is when I created a trigger on MS SQL Server and tried to insert the data into MySQL from within the trigger. The problem is that the ODBC driver for MySQL doesn't support distributed transactions (via MS DTC).

I'm thinking perhaps I might have to perform the publisher-subscriber replication instead.

No comments:

Post a Comment