Search code examples
mysqlazuressmsetlazure-sql-server

How to connect to a remote MySQL from an Azure SQL server/database

sorry if title is not so clear, probably I am not finding what I need due I do not know how to search

I have few MySQL servers is separated online servers (from different wordpress) and I want to load some of the data on those databases/tablets into a SQL database located on Azure.

inside Azure portal itself I do not see where to establish external connections, neither at server level, neither at database level

I download and install Microsoft SQL Server Management Studio, connect to the server, I can see my databse and the master one, Security with logins, and Integration Service Catalog, nothing else.

I was looking for something like: https://www.jetbrains.com/help/go/db-tutorial-connecting-to-ms-sql-server.html#step-3-connect-to-microsoft-sql-server-with-datagrip but nowhere ...

maybe something like this: https://www.devart.com/odbc/mysql/docs/microsoft_sql_server_manager_s.htm but no Servers objects option available on my SSMS

Can be this done?

Note: Azure database is a basic wfor now, if that is a limitation

Solution

  • Some choices.

    1. In your SQL Server Management Studio create a linked server pointing to each MySQL instance. You found the instructions for that. https://www.devart.com/odbc/mysql/docs/microsoft_sql_server_manager_s.htm But it probably will not work in Azure SQL Server; you don't have access to the underlying Windows OS to install stuff like MySQL ODBC drivers, which you need. (You could ask Azure techsupport if they can help.)

    2. In each MySQL instance, try creating a federated table connection to appropriate table in SQL Server. That cross-vendor federation stuff only works in MariaDB, however; MySQL's federation only goes MySQL <--> MySQL.

    3. Write yourself a purpose-built extract / transform / load (ETL) program, and arrange to run it every so often. Program it to connect to all the servers involved, retrieve the data needing to be transferred from your MySQL servers, and update / insert that data on the SQL server.

    4. (edit) You may be able to use command-line SQL client programs. mysqldump, with its --compatible option, may generate usable INSERT statements in a file. You then may be able to use sqlcmd to run those INSERTs on your Azure server. It's going to take some hacking, and may take using sed(1) or awk(1) to make the MySQL output compatible with SQL Server.

    I believe the third option is the most robust one for production use.