Introduction
Earlier this week I migrated “storage.kvaes.be” towards Azure. It was long due… Though I was determined to change the backend to Azure Table Storage. Are the better setups for this? Yes there are! Though I wanted to get myself a bit more familiar with the table storage from PHP. So I thought it was a nice test. 🙂 Anyhow, for the actual data migration I used a combination of manual mutations & data factory. I’ve already used the Azure Data Factory a few times before, and it always pleases me.
That brings me to today’s post, where I’ll do a quick run through how you can use Azure Data Factory for the migration of your MySQL database towards an Azure SQL Database (or any other support target).
Prep. : Data Factory
Pointing out the obvious… You’ll need to deploy an Azure Data Factory. 😉 As this is pretty straight forward as with any Azure service, I’m not going to cover that in this post. Just go to the marketplace and lookup “data factory”.
Prep. : Data Management Gateway
During the setup, you’ll be prompted for a gateway. This will serve as an intermediate host that will act as gateway between the data factory and the MySQL database. Be sure to have a (windows) system ready you can use for this purpose. When you see the following part, click on “Create Gateway”.
Choose a name for it, and press “Create”
Next up, download the gateway and install it ;
Afterwards you’ll see a similar screen showing that all went well…
You are now good to go!
Prep. : Provisioning Table Structure in the Azure SQL Database
When using Data Factory, the most target system (like Azure Table Storage for instance) have the option to create a structure (like a table) for you (if this does not exist). Sadly/strangely enough this does not exist for the Azure SQL Database. So be sure to create the target structure in your Azure SQL Database before setting up the copy job!
I did the similar thing here… Where I had two tables in my MySQL,
and I recreated these in the Azure SQL Database ;
Once done, then we are all set!
Data Factory – Copy Data
In your Azure Data Factory, click on “Copy Data” (currently in preview) ;
A new tab/page will open, with a nice step by step setup guide ;
Give the job a name and configure the task schedule. Next you can select the source, for this we’ll select “MySQL” ;
Now we need to configure our source. If needed, create your gateway (as mentioned in our prep section).
After the connection has been validated, you can select the tables you want to migrate. Here I selected the two tables I needed to have migrated ;
Next up we can select our target/destination. Here we’ll select “Azure SQL Database” ;
And again we’ll select the details. As this is an Azure service, the select boxes will be populated with the available systems ;
Next we can choose the mapping of the tables ;
And do a mapping of the schema ; 
Next up we can set some performance settings ;
And as a final verification step, we can see a summary of what we’ve configured ;
Press next and the “pipeline” will be created ;
Give it a bit, and the deployment will finish.
Afterwards we can monitor the results of our copy pipeline.
Outcome?
So if we click on that link, we see the jobs. Here we see a copy job per table mapping. As shown, both are in progress.
Click on it will provide is with some details.
And after a bit it will be “Ready”.
Now let’s take a look in our Azure SQL Database.
Nice! The data has been migrated! Does this always end up well? Like al IT systems… no. What I’ve noticed is that wrongly mapping data format is usually the culprit.
Compute Options?
Want more compute options? You can also “Author” a job manually ;
And here you have several compute options available ;
Alternative sources/targets?
So we saw Azure SQL Database & MySQL, are there any other options? Yes there are…
Like I mentioned at the start, I also used this flow for the migration between MySQL & Azure Table Storage.
And that had a similar outcome as just shown with the Azure SQL Database. Here is a screenshot of the Azure Table Storage as viewed via the Azure Storage Explorer ;
Karim Vaes 