Migrate Open Source Data Workloads to Azure
Course Outline
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 Services
- Explore Azure Services here
- Deep dive through Azure Fundamentals aka.ms/HN/learnaz
- Azure Database Services
- Azure Database Migration Guide
Azure Database Services for OSS
Azure Database for MySQL
Learning Resources
Azure Database for PostgreSQL
Learning Resources
Azure Database Documentation
- To know more about Network Topology
- Migrate open-source databases to Azure Step by Step Tutorial
Lab Files
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 by Step guide MySQL to Azure DB for MySQL
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’;
FLUSH PRIVILEGES;
SELECT user,authentication_string,plugin,host FROM mysql.user;
exit
- 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.
STEP 2
Dump the DB for Offline Migration
- mysqldump -u azureuser -pPa55w.rd adventureworks > aw_mysql_backup.sql
STEP 3
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
STEP 4
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]
STEP 5
Back to Local Machine (Azure VM where MySQL has been install and Populated)
Execute this command from Lacal Machine:
- mysql DB_NAME -h AZURE_HOST -u AZURE_UNAME -p < DUMP_FILE.SQL
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
Example:
- 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
STEP 6
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
Example
- mysql -h demomysqlmigration.mysql.database.azure.com -u arifmysql@demomysqlmigration -p azureadventureworks_temp < aw_mysql_no_data.sql
STEP 7
- 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