0

We are working on setting up a Linux server with MySQL on it as a testing server.

I got a linked server set up on our current MSSql server so that we can query the MySQL server.

I would also like to be able to go the other way, ask the MSSQL server from the MySQL server.

So far, I'm not finding much information on it, as everything seems to be about going the other way.

Łukasz D. Tulikowski
  • 155
  • 4
AndyD273
  • 207
  • 1
  • 11
  • There's the FEDERATED storage engine that lets you link MySQL->MySQL, but nothing that I've ever seen to link MySQL->MSSQL. – squillman Aug 31 '11 at 21:26

1 Answers1

1

This idea is something I have been casually looking around for out of curiosity and while the closest thing I have seen like a linked server for MySql is the federated storage engine but I haven't seen any examples of anyone being able to use that to communicate with MSSQL.

With that being said an idea for this that I was going to play around with is using triggers to insert/update/delete the data in MySql through the linked server connection. With that idea you could create a replica database in MySQL of the one you want to query on MSSQL and from there create triggers on the MSSQL side to keep the MySQL side up to date. So then when needed MySQL will have an up to date copy. This method of doing things will likely keep your writes slow as now you have added a much more complicated layer on top of things and have to go out on the network to the MySQL box for every write.

Alternatively you could use a job to sync the two copies of data and have it run every few minutes. Although I haven't really played around with the idea of using a job to update MySQL yet so I haven't thought much on what might be the best way to implement it - the challenge would be to find a way to detect and only apply the differences. For larger tables it will be cumbersome to delete all the data on the MySQL side and re-apply all of the data from MSSQL.

Ian Chamberland
  • 680
  • 4
  • 8
  • Yeah, this is how we used to do it when we were on MySQL a while back. Our hosting has provided MSSQL for a while, but we're exploring cutting some costs. The down sides are of course the burden of keeping things synchronized without it taking hours each time it needs to update, esp when you are dealing with huge data sets, and with the small differences of datatypes. I had kinda hoped that MySQL had a new option in the last 3 years. The only other possibility would be to do some kind of flat file data transfer, which would get around the linked server BLOB issue. – AndyD273 Sep 01 '11 at 15:48