Migrate from MySQL to SQL Server with Ease

To transfer a client from one host to another I needed to migrate their data from MySQL to SQL Server (or more specifically to a Microsoft SQL Database in Azure). I went down some roads trying to export from phpMyAdmin, or performing a mysqldump, to generate a script. In both cases my import into SSMS was riddled with syntax errors that would be overwhelming to correct. Once I stumbled onto the SQL Server Migration Assistant I completed my task with ease.

In my scenario I was wanting to pass from one host to another. I used my computer as a middle man and connected to both. But first you need to install some software.

MySQL ODBC Connector

This connector is utilized by SSMA. It will require you to create a free, MySQL account to download the file.
When installing I chose a Typical installation.

SQL Server Migration Assistant

To get the latest SQL Server Migration Assistant you should do a web search for "Download SSMA MySQL"
I chose SSMA_MySql\SSMA for MySql.5.3.0.exe. When installing, it couldn’t find the MySQL ODBC driver v5.1 or above. But it is there so disregard. I also chose a Typical installation.

Configuring SSMA

Microsoft SQL Server Migration Assistant for MySQL will be listed under your new programs. There may be a 32-bit and a 64-bit. Run the appropriate one for your operating system.
Next, I created a new project using File > New Project. I chose to Migrate to SQL Azure. But this technique isn’t limited to Azure. Choose the version of SQL Server running on your destination host. At the top there are options to Connect to MySql and Connect to SQL Azure.

When connecting to MySql you fill out the following dialog:
Connect to MySQL
In the dialog that appeared it had a provider preselected. It had a yield sign beside it for some reason, disregard. I entered my domain without any prefix, left the port alone, and entered the same credentials I would if logging into cpanel. You may need to allow remote access to your computer’s public IP to allow the interaction. This can likely be done in cpanel or by contacting support.

When connecting to Azure you fill out the following dialog:
Connect to SQL Azure
Fill out your azure information. You can get some of it by going to the Azure portal, selecting the sql server instance or database in your resource group, choose All Settings then Properties. The username will be something like mike@myresourcegroup

In my top pane I expanded the tree and checked the mysql database of interest, and also selected the node. In my sql server pane I expanded the tree and checked my database. Right click on that node and choose to Synchronize with Database. This will create all the tables which is needed before migrating the data.
Menu option

Finally, go back up to your selected mysql database node, right-click and choose to Migrate data.
Menu option

The data will convert without any generated scripts. Afterward, you can see the tables and data in SQL Server Management Studio.

Read More