1

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

O. Jones
  • 103,626
  • 17
  • 118
  • 172
CarlsWhishde
  • 13
  • 3
  • By `my database` do you mean the `few MySQL servers`? and `the master one` do you mean the `Azure SQL server`? If I understand your question properly you want to migrate the data from the `few MySQL servers` to one `Azure SQL server`, then prehaps https://datamigration.microsoft.com/scenario/mysql-to-azuresqldb?step=1 will help? – AmyChodorowski Feb 10 '21 at 17:00
  • not migrate, but connect, I want to load some data from the MySQLs to AzureSQL each day while the WP and the MySQLs keeps working – CarlsWhishde Feb 10 '21 at 17:20
  • In Azure you have created an entirely new database, with a physical location at one of Azure's data centres. You can't connect to another database via Azure as it is not a database manager. Therefore if you are wanting to consolidate different databases to one Azure database you will have to copy and paste (migrate) that data to the Azure database. – AmyChodorowski Feb 10 '21 at 17:36
  • @AmyChodorowski, hooou, that a really nasty limitation for Azure DBs... , it use to be so simple for locally manages SQLservers time ago (it is like few years since I manage them) – CarlsWhishde Feb 11 '21 at 05:09

1 Answers1

1

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.

O. Jones
  • 103,626
  • 17
  • 118
  • 172
  • @o-jones Thanks a lot for the explanations! 1) it is really sad this path is not possible in AzureDB, really sad indeed – CarlsWhishde Feb 11 '21 at 05:11
  • 2) I will try this path, one way I guess will be to make some jobs or triggers that send the data upstream (to AzureSQL) if DB allows this type of connection. – CarlsWhishde Feb 11 '21 at 05:12
  • 3) due to the technical debts (most are on cloud servers where we do not have so many rights) would like to avoid more external tools, and servers running around. Anyhow, any recommendation tool or approach?, something that could be done in bash and programed with cron? – CarlsWhishde Feb 11 '21 at 05:14
  • For what it's worth, and with respect, an ETL program in bash is the very definition of technical debt. Still, see my edit. – O. Jones Feb 11 '21 at 12:15
  • 4) may I give you a super plus !!! thaks a lot for the tools !! Job to be done !!! thanks a lot – CarlsWhishde Feb 11 '21 at 15:07