MySQL to Azure SQL Database Migration

I’ve been experimenting lately with Azure and wanted to kick the tires of SQL Database with a real dataset that I have. It turns out that this is pretty straightforward, but I had to collect information from a number of sources. Here are my notes – hope they are helpful:

  1. You’ll need a Windows machine to run SQL Server Management Studio (SSMS) and an Azure account with your target SQL Database provisioned.  There is a great walk through on how to do that written up in the Azure documentation.  Follow the steps there to get SSMS connected to your Azure db instance.   
  2. Install the MySQL ODBC Connector if you haven’t already.
  3. Install the SQL Server Migration Assistant for MySQL (SSMA).   Note: You actually need to install both it and an extension pack that are bundled in the same zip file you download.
  4. Run SQL Server Migration Assistant for MySQL.   It’ll prompt you for a license, which requires jumping back to the Microsoft site, and moving this license file to a path that it gives you.
  5. Click “Connect to MySQL” in the top left corner.  Point SSMA to your MySQL instance and connect.   SSMA will introspect the instance and give you a list of the databases you can connect to.   Check off the database you’d like to migrate.
  6. Click “Connect to SQL Azure”.   You’ll need your SQL Database server’s instance name from the Azure Management Portal, which should look something like this:  kcpnnc4bcy.database.windows.net and your user name and password for the db instance that you created in step 1.   Check off the database you created.
  7. Ok, we’ve connected the two databases.  Expand the node for your MySQL database and check off the items that you want migrated.
  8. SSMA can do a dry run of the schema conversion and produce a report.   Do this by right clicking the “Tables” node on your MySQL connection and selecting “Create Report.”   I had 70 odd warnings to work down through – all were slight collation and schema differences that I could safely ignore – but check them all anyway.
  9. It takes three steps to get your data migrated.   Right click your MySQL “Tables” node again and click “Convert Schema.”   This will create the schema on your Azure SQL Database.  Check that over and make sure it looks good to go.
  10. Right click your new Azure SQL Database and select “Synchronize with Database”.   This force you to enter credentials again for both databases and then will synchronize schemas and indexes.
  11. Finally, right click the “Tables” node for your MySQL database and select “Migrate Database”.   Enter your credentials again and SSMA will jump into migrating the data.

Leave a comment