Mark Atwood (fallenpegasus) wrote,
Mark Atwood
fallenpegasus

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?
Tags: mysql
Subscribe
  • Post a new comment

    Error

    Comments allowed for friends only

    Anonymous comments are disabled in this journal

    default userpic

    Your reply will be screened

    Your IP address will be recorded 

  • 4 comments