?

Log in

No account? Create an account
entries friends calendar profile My Website Previous Previous Next Next
Shadowing a MS SQL table in MySQL - Mark Atwood
fallenpegasus
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:

4 comments or Leave a comment
Comments
also_huey From: also_huey Date: September 10th, 2008 03:48 am (UTC) (Link)
The MS SQL table doesnt have a "time this row was last updated" column.

Fix the table.

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

Fix the developer. Carrot or stick, your call.
From: datacharmer Date: September 10th, 2008 05:41 am (UTC) (Link)

Use DBIx:MyServer

Use DBIx::MyServer, as shown in this article.
The article features an example of a MySQL client querying SQLite and PostgreSQL tables.

Giuseppe
From: mattwills Date: September 10th, 2008 03:22 pm (UTC) (Link)
I've had this exact situation to deal with in several projects. In these cases, I had a PHP script (or .NET app) poll the MS SQL table for changes every 15 minutes and update MySQL. For the specific application though, that lag was acceptable. I had originally tried to use ODBC but it kept choking.
awfief From: awfief Date: September 10th, 2008 03:43 pm (UTC) (Link)
If the tables are myisam, the server .MYI and .MYD files will change. If innodb and set to innodb_file_per_table, ditto for the .ibd files. If not innodb_file_per_table, the ibdata files do change when data's written.

There might be some batch scripting you can do. :)
4 comments or Leave a comment