Migrate Open Source Data Workloads to Azure

Module 1: Introduction to open source database migration on Azure

  • MySQL and PostgreSQL database services in Azure
  • Considerations for migration
  • Approaches to migration

Module 2: Migrate on-premises MySQL to Azure Database for MySQL

  • Introduction to Azure Database for MySQL
  • Migrate on-premises MySQL database to Azure
  • Application Migration

Module 3: Migrate on-premises PostgreSQL to Azure Database for PostgreSQL

  • Introduction to Azure Database for PostgreSQL
  • Migrate on-premises PostgreSQL database to Azure
  • Application Migration

Module 4: Protecting monitoring and tuning

  • Protecting a database and server
  • Monitoring and tuning

Azure Database for MySQL

Learning Resources

Azure Database for PostgreSQL

Learning Resources

Azure Database Documentation

You can find all Lab Files and Instructions here

Download as ZIP.

Important: Remember to stop any virtual machines used in these labs when you no longer need them – this will minimize the Azure credit incurred for these services. When you have completed all of the labs, consider deleting the resource group you created if you don’t plan to experiment with it any further.

Step 1 (Create/Setup Local Machine With MySQL Database )

  • Create a VM from Azure Portal
  • Connect with RDP
  • Download Chrome
  • Download and Install fom https://www.microsoft.com/en-sg/download/details.aspx?id=40784
  • Download and Install from https://support.microsoft.com/en-us/help/2977003/the-latest-supported-visual-c-downloads
  • Download and Install MySQL 5.7 Community Server (https://dev.mysql.com/downloads/windows/installer/5.7.html) and Workbench 
  • Setup Environment Variable; so that mysql command can execute from CLI
  • Download Azure Lab File (https://github.com/MicrosoftLearning/DP-070-Migrate-Open-Source-Workloads-to-Azure)
  • Go to migration_samples/setup/mysql/adventureworks

MySQL Tweaks (If Needed)

SELECT user,authentication_string,plugin,host FROM mysql.user;

ALTER USER ‘root’@’localhost’ IDENTIFIED WITH mysql_native_password BY ‘password’;


SELECT user,authentication_string,plugin,host FROM mysql.user;


  • mysql -u root -p CREATE USER ‘azureuser’@’localhost’ IDENTIFIED BY ‘Pa55w.rd’;
  • GRANT ALL PRIVILEGES ON *.* TO ‘azureuser’@’localhost’ WITH GRANT OPTION;

Populate/Create On-prem Databases and Tables

  • GOTO /migration_samples/setup/mysql/adventureworks

  • mysql –user=azureuser –password=Pa55w.rd < adventureworks.sql

Review the on-premises database

  • In the MySQLWorkbench window, Expand adventureworks, and then expand Tables.
  • Right-click the contact table, click Select Rows – Limit 1000, and then, in the contact query window, click Limit to 1000 rows  and click Don’t Limit.
  • Click Execute to run the query. It should return 19972 rows.
  • In the list of tables, right-click the Employee table, and click Select rows.
  • Click Execute to run the query. It should return 290 rows.


Dump the DB for Offline Migration

  • mysqldump -u azureuser -pPa55w.rd adventureworks > aw_mysql_backup.sql


Migrate the on-premises database to an Azure SQL Database for MySQL

  • Create Azure Database for MySQL (Azure Portal)
  • Connection Security > Add Firewall Rules > Add Local Machine IP
  • Allow Access to Azure Services’ > Yes


Open Cloud Shell prompt from Azure Portal, run the following command to create a new database in your Azure Database for MySQL service.

az MySQL db create \
–name adventureworksoffline \

–server-name adventureworks[nnn] \

–resource-group [resource group]


Back to Local Machine (Azure VM where MySQL has been install and Populated)

Execute this command from Lacal Machine:


Example : 

  • mysql adventureworks_offline -h demomysql001.mysql.database.azure.com -u mysql@demomysql001 -p < aw_mysql_backup.sql

Review Azure database

Connect to Azure DB for MySQL (From Local Machine)

  • mysql -h AZURE_HOST -u AZURE_UNAME -pAZURE_PASS adventureworks_offline


  • mysql -h demomysqlmigration.mysql.database.azure.com -u arifmysql@demomysqlmigration -p adventureworks_offline
  • SELECT COUNT(*) FROM specialoffer;
  • Verify that this query returns 16 rows.
  • SELECT COUNT(*) FROM vendor;
  • This table should contain 104 rows.

This is how we can do Offline Migration


Perform an online migration to Azure Database for MySQL

  • Switch back to the Azure portal.
  • Click All services, click Subscriptions, and then click your subscription.
  • On your subscription page, under Settings, click Resource providers.
  • In the Filter by name box, type DataMigration, and then click Microsoft.DataMigration.
  • If the Microsoft.DataMigration isn’t registered, click Register, and wait for the Status to change to Registered. It might be necessary to click Refresh to see the status change.
  • Click Create a resource, in the Search the Marketplace box type Azure Database Migration Service, and then press Enter.
  • On the Azure Database Migration Service page, click Create.
  • Create Premium Tier

Migrate Sample Schema & Upload to a New Database on Azure DB for MySQL

  • mysqldump -u azureuser -pPa55w.rd adventureworks –no-data > aw_mysql_no_data.sql

  • mysql -h AZURE_HOST -u AZURE_UNAME -p AZURE_NEWDBNAME < aw_mysql_no_data.sql


  • mysql -h demomysqlmigration.mysql.database.azure.com -u arifmysql@demomysqlmigration -p azureadventureworks_temp < aw_mysql_no_data.sql


  • Use Azure Database Migration Service and Create New Project 
  • New migration project page, set the source server type to MySQL, set the target server type to Azure Database for MySQL, and select Online data migration.
  • Click Create and Run Activity
  • On the Add Source Details page, add the address of the source server, the source database, and an account that can connect to this database to retrieve the data. The account must have SUPERUSER
    privileges to perform migration.
  • On the Target details page, specify the address of your Azure Database for MySQL service, the database into which you want to migrate the data, and the details of an account that has administrative rights
  • On the Map to target databases page, select the source database and target database. You can migrate a single database or multiple databases
  • On the Migration settings page, specify any additional settings that you want to configure, such as the maximum number of tables to load in parallel.
  • On the Migration summary page, enter a name for the activity, and then select Run migration.
  • If you’re doing an online migration, the status changes to Ready to cutover when the existing data has been transferred. However, the activity continues running, to transfer any additional changes that appear while applications are still actively using the original database.

This is how we can do Online Migration