Best way to track changes and make changes from Mysql -> MSSQL

Question

So I need to track changes that happen on a Mysql table. I was thinking of using triggers to log all the changes made to it and then save these changes in another table. Then I will have a cron script get all these changes and propagate the changes into the Mssql database.

I really dont expect a lot of information to be proporgated, but the data is very time sensitive. Ideally the MSSQL will see these changes within a minute, but I know that this requirement may be too high.

I was wondering if anyone had a better solution.

I have the bulk of the site written in .net but use vbulletin as the forums (sorry but there are no .net forums as powerful or feature rich like vbulletin)

Answer 1

Hi!

The majority of the replicator tools use this technique. Fill another table on insert/update/delete triggers that containt the tablename and the PK or a unique key.

Then a reader reads this table, do the proper "select" if insert/update to get the data, then updates the other database.

HTH

Answer 2

you might look into Daffodil and these other (I bookmarked them, but haven't used them)

http://symmetricds.org/

http://dbreplicator.org/

http://sourceforge.net/projects/daffodilreplica