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.
Best Answer
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.