Azure SQL MySQL Integration: 2 Easy Methods

Azure SQL offers managed, secure SQL Server solutions in the Azure Cloud. It is excellent for cloud architects and developers because it reduces the need to manage the OS and database with DBA-like features.

MySQL on the other hand, is a popular Relational Database Management System (RDBMS). The key benefits of integrating Azure SQL MySQL comprise streamlining data transfer, enhancing scalability, reduction of management overhead, and support to real-time analytics. 

In this article, you will learn more about Azure SQL and MySQL and their key features.

What is Azure SQL?

Microsoft offers Azure SQL, a relational platform-as-a-service built on top of SQL Server. It has features such as 99.99% availability, strong query strength performance, and AI-enabled capabilities. It supports the latest stable version of SQL Server. The support features include relational data, JSON, graphs, spatial data, and XML. For this reason, it suits modern cloud applications.

Read Azure SQL Database Simplified 101 to explore how to create and manage Azure SQL Database. 

Key Features of Azure SQL

Azure SQL Database is intelligent, scalable, and part of the Azure SQL family. Let’s take a look at some of the remarkable features it offers:

• Scalability: An Azure SQL database can be created as a Single Database to use alone or set up as part of an Elastic Pool, sharing all resources with other databases.
• Flexible Pricing: Azure SQL offers several purchasing models, including vCore-based for customizable resources, DTU-based for managing I/O across tiers, and a Serverless Model for automatic scaling.
• Security & Compliance: Azure SQL offers advanced capabilities, including risk-based sign-ins and legacy authentication protection.
• Advanced Threat Protection: It includes SQL security features like vulnerability management, anomaly detection, and malicious activity monitoring.

What is MySQL?

MySQL is an SQL-based Relational Database Management System (RDBMS) that can be used in data-intensive web applications or services. It supports multiple programming languages, such as PHP, PERL, JAVA, C++, and C. MySQL builds and manages everything, from developing customer-facing web applications to handling data-driven B2B services.

MySQL implements the client-server model and uses SQL to manage relational data. Users can efficiently access and execute different kinds of CRUD (Create, Read, Update, and Delete) operations.

Key Features of MySQL

Over 5000 companies, including Uber, Netflix, and Amazon, use MySQL. Here are the notable features of MySQL:

• High Performance: Rapid processing of data with a few of the functionalities such as Table Index Partitioning and Memory Caches.
• Scalability: Easy to scale and support for Linux, OS X, and Windows.
• Compatibility: High-speed, low-latency transactions.
• Full Transactional Support:  Ensures data is not corrupted because of ACID properties.
• Ease of Use: It has a good SQL interface, which would be very easy to use for anyone with a little knowledge of SQL.

Azure SQL V/S MySQL

Feature	Azure SQL	MySQL
Type	Microsoft managed SQL server database service.	Open-source RDMS by Oracle.
Availability	Built-in high availability for resilience.	Requires extra configuration for high availability.
Cost Structure	Consumption-based pricing.	Open-source with free options but with tradeoffs.
Scalability	It easily scales up for more performance.	Scaling requires data sharding or other complex methods.

Method 1: Set Up Azure SQL MySQL Integration using Hevo

Using Hevo, Azure SQL to MySQL migration can be done in the following two steps:

Step 1: Configure Azure SQL as the Source

• Step 1.1: Enter information to configure your Azure SQL as a source according to the image below
• Step 1.2: Click on CONTINUE to configure your source successfully.

Step 2: To set up MySQL as a destination

• Step 2.1: Select MySQL from the Add Destination page and set the parameters on Configure your MySQL Destination page according to the image.
• Step 2.2: Click Test Connection to test connectivity with the Snowflake warehouse.

• Step 2.3: Once the test is successful, click SAVE DESTINATION.

Method 2: Set Up Azure SQL MySQL Integration using SQL Server Migration Assistant

Prerequisites

To successfully set up the Azure SQL MySQL Integration you need to meet the following requirements:

• Source Environment supports MySQL 5.6 or 5.7.
• SQL Server Migration Assistant for MySQL installed. Refer to Microsoft SQL Server Migration Assistant for MySQL for installation steps.

Step 1: Create a New Project in the SSMA

• Step 1.1: Open SSMA for MySQL and navigate to File → New Project.
• Step 1.2: Enter a name and location for your project in the New Project pane, and then choose Azure SQL Database from the Migrate To drop-down list, as shown below. Select OK.

• Step 1.3: Select the Connect to MySQL tab, and then enter the information needed to connect to your MySQL server as shown below.

• Step 1.4: Now, right-click on the desired MySQL schema in the MySQL Metadata Explorer window and choose Create Report as shown below. To understand the conversion statistics, problems, and cautions, look over the HTML report.

Step 2: Configure MySQL Azure SQL Data Type Mapping

In this step, you will validate the default data type mappings and, if required, make changes depending on the needs. To do so, follow these steps:

• Step 2.1: Navigate to Tools → Project Settings and then select the Type Mappings tab as shown below.

• Step 2.2: By choosing the table name in the MySQL Metadata Explorer window, you can alter the type mapping for each table.

Step 3: Connect MySQL to Azure SQL Database

After validating all the data types, follow the steps below to connect your MySQL with Azure SQL database:

• Step 3.1: Go to the Connect to Azure SQL Database tab and enter the details in the fields as shown in the image below. Once done, click on Connect as shown below.

• Step 3.2: Select the schema you are working on and choose the Convert Schema tab in the upper right corner as shown below.

• Step 3.3: Review and compare the converted objects to the original objects when to detect any problems and rectify them using the recommendations.

Step 4: Publish & Migrate Azure SQL MySQL Databases

You may begin the migration procedure when you’ve assessed your databases and fixed any inconsistencies. There are 2 phases to migration: publishing the schema and migrating the data.

Step 4.1: Publish the Schema:

• Right-click the database in the Azure SQL Database Metadata Explorer window and choose to Synchronize with Database. This action publishes your MySQL schema to the Azure SQL Database.

Step 4.2: Migrate the Data: 

• Now, right-click the MySQL schema you wish to migrate and then select Migrate Data as shown below.

• View the Data Migration Report once the migration is finished.
• Next, connect to your SQL database with SQL Server Management Studio and validate the data and schema to ensure the migration is successful.

Step 5: Test your Azure SQL MySQL Data Migration

To ensure that everything is functioning smoothly, you should run various tests on your Azure SQL MySQL Integration.

You can:

• Develop Validation Tests: Validation queries must be executed against both the source and target databases.
• Set Up a Test Environment: A copy of the source database and the target database should be included in the test environment.
• Run Validation Tests: Validate both the source and the destination, then analyze the results.
• Run Performance Tests: Conduct performance tests on the source and destination, then compare and evaluate the findings.

Limitations of Setting Up Azure SQL MySQL Integration Manually

• You might have observed that Method 2 is quite lengthy and complex.
• You need to manually transform and map your source and target schema. However, in manual method you just need to connect your source and target databases, and the schema mapping is handled automatically.

Conclusion

In this article, you have read the characteristics of Azure SQL and MySQL, along with ways to migrate data from MySQL over to Azure SQL. First method is an easier way using Automated Data Pipelines while in the second method, the integration is covered through SQL Server Migration Assistant for MySQL. For companies who face challenges in merging various sources of data, Hevo comes in pretty handy with an easier solution.

Hevo Data is a No-Code Data Pipeline that offers a faster way to move data from 150+ Data Sources, including Azure SQL, MySQL, and other 50+ Free Sources, into your Data Warehouse to be visualized in a BI tool. Hevo is fully automated and does not require you to code.

Want to take Hevo for a spin? Get your 14-day free trial now and experience the feature-rich Hevo suite firsthand. You can also have a look at the unbeatable pricing that will help you choose the right plan for your business needs.

Share your experience with Azure SQL MySQL Integration in the comments section below!

Frequently Asked Questions