What's the best unified way to manage both a SQL Server and a MySQL database?Posted: asked May 18 at 20:07 - Source : stackoverflow
I'm the lead of a small, 3-person team. We are responsible for data management in both our SQL Server-based ERP and in our MySQL-based Magento website. We currently use SQL Server Management Studio 2016 clients for SQL Server work, and MySQL Workbench clients for MySQL work.
However, we frequently need to write and execute queries that select against both databases. For a simple example, we may need to ensure that a part number's attribute in SQL Server is identical to that of its equivalent in MySQL. In the past, we've used Microsoft Access for those queries that must select against both databases. This proved unwise: using Microsoft Access basically introduced a third (glitchy) database, the maintenance of which has been frustrating.
Because we're a small team with multiple duties, I prefer to use one application/DBMS/IDE to manage all of our queries to both of our databases. I don't want my team juggling 2-3 different applications; I want everyone sharing identical queries, views, and best practices on one application. What I seek is a reliable application/tool/method that will allow facilitate this unified approach.
Aside from Microsoft Access (which we are offboarding), I'm aware of only two ways to query against both MySQL and SQL Server simultaneously:
- SQL Server "Linked Server". This method is the best I've been able to find. The problem is that it requires a SSH tunnel running on our SQL Server in order for a client Management Studio to successfully connect to the Linked Server. Our IT guys object to leaving a SSH tunnel running on the SQL Server.
- SQL Server Integration Services. My limited understanding is that this tool is for ETL-like processes. This would only be partially acceptable; it would not suffice for up-to-the-minute MySQL data, which is often needed.
Is there a better way to accomplish this objective? above?