Query an MS-SQL server from MySQL server

Question

I work for a large organization that has an established and well populated MS-SQL server. However, I am not a Microsoft user, and my database of choice is MySQL. I am looking for a solution that will allow me to either...

-Directly query our MS-SQL server from my MySQL server

and/or

-Set up some sort of job that will copy data systematically from the MS-SQL server to our MySQL server.

It looks like Linked Servers may be part of the solution, however everything I have found describes scenarios where MS-SQL is accessing MySQL, not the other way around.

To be clear I want my MySQL server to talk to/query/pull data from my MS-SQL server.

Any help appreciated!

Answer 1

As far as I'm aware, you can't query any other RDBMS vendor from MySQL. MySQL's remote access feature is FEDERATED tables, which only work with other MySQL databases as far as I know.

About the simplest way you could do this would be to use SQL Server's Import/Export Wizard to create a simple package that copies the data to your MySQL server through an ODBC or ADO.NET connection to the MySQL database.

Answer 2

To be clear I want my MySQL server to talk to/query/pull data from my MS-SQL server.

I think it is hard to even assume this is the best decision. Without a TON more context of what the real problem is and/or the real "need", answers vary widely from "just use ms-sql" to other levels of ad-hoc ETL. That said, some abstract feedback.

There is nothing wrong with MS-SQL, as long as you are (a) not paying for it and (b) have a clean solution to use it from a real POSIX based system. Technically, MS-SQL is a great database, I just dislike Windows. To that end, I made sure that working with MS-SQL from Ruby was done well at both the C extension layer with TinyTDS and the ActiveRecord adapter.

Sadly, I have personally stopped maintaing the later, but the C extensions are strong and even used by great projects like Sequel which if you had to some sort of raw ETL without the overhead of ActiveRecord is a great choice since it has adapter for all DBs, TinyTDS included.