September 9th, 2008

amazon

Shadowing a MS SQL table in MySQL

So, here is a hypothetical problem:

There exists a table on a MS SQL "client" server (think, one step up from Access, or MS SQL Server stripped way down)

That machine is on the other side of a VPN link

I want to keep a table or the appearance of a table in MySQL that shadows that table, in close to real time as possible.

The MS SQL table doesnt have a "time this row was last updated" column.

The MS SQL developer cant/wont put on update triggers on his system.

So far, I've considered the following possible solutions:

* Have the PHP and Perl apps that speak to MySQL also keep a connection to the MS SQL table, and do the necessary joins and such in the app, instead of the database.

* Write a daemon that keeps a DBI::MSSQL connection and a DBI::MySQL connection open, and keeps polling the MS SQL table and writing it to MySQL. This is really slow and inefficient as the table gets large and there is no way to write a query of the form "all rows changed since time T".

* Use the ODBC storage engine. Is it stable enough?

* Maybe there is a fork of MySQL proxy that speaks ODBC or DBI on it's back end.

* Maybe MySQL proxy can have Lua scripts that do Lua's version of DBI to talk to the MS SQL table

Any other workable suggestions?