Mark Atwood (fallenpegasus) wrote,
Mark Atwood

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

  • Razors

    I'm getting ads for I think five different "all metal" "get the best shave of your life" "throw away the plastic" razor startups. They all seem to be…

  • Doing what needs to be done

    On May 1st, one of my co-residents found one of the feral rabbits that live in the area cuddled up against a corner of the house. It was seriously…

  • The CTO of Visa, after listening to me present

    Some years ago, I was asked to travel to the corporate meeting center to present at a presentation-fest to the CxO staff of Visa. Yes, the one with…

  • Post a new comment


    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