Data integration with Azure Logic Apps and MySQL Flexible Server
Published Oct 18 2023 10:44 AM 2,577 Views
Microsoft

Data integration allows applications to move, process, or transform data across multiple systems as part of their micro-service architecture. Data integration is the first step in supporting data collection to allow business applications to learn and build insights from that data.

 

While you can accomplish data integration in several ways, in this blog post I'll focus on how to use Azure Logic Apps to move data to an Azure Database for MySQL flexible server. This solution automates data integration tasks performed in response to API calls.

 

Consider the following architecture:

 

mk_sunitha_0-1697142208840.png

Figure: Using Azure Logic Apps to move data into Azure database for MySQL Flexible Server

 

In this architecture:

  • The JSON payload is transferred as part of the HTTP request using API App to securely route the request.
  • Logic Apps retrieves the database credentials and sends the database statement that must be run by the connector on the MySQL database server. I can easily insert data using the insert action of the Azure Database for MySQL connector. The data is now stored in the MySQL database and is available for consumption by a user with access to the database.

Architectural components

This architecture uses the following components:

  • Azure API Management, which creates consistent, modern API gateways for back-end services. Besides accepting API calls and routing them to back ends, this platform also verifies keys, tokens, certificates, and other credentials. API Management also enforces usage quotas and rate limits and logs called metadata.
  • Azure Logic Apps, which automates workflows by connecting apps and data across clouds. This service provides a way to securely access and process data in real time. Its serverless solutions take care of building, hosting, scaling, managing, maintaining, and monitoring apps.
  • Azure Key Vault, which stores and controls access to secrets such as tokens, passwords, and API keys. Key Vault also creates and controls encryption keys and manages security certificates.
  • Azure Database for MySQL - Flexible Server, which is a fully managed, production-ready database service designed for granular control and flexibility over database management functions and configuration settings. It provides high availability, and management controls ideal for production workloads.
  • Azure Monitor, which collects data about environments and Azure resources, information that’s helpful for maintaining availability and performance.

Workflow

The workflow associated with this architecture follows.

  1. Create JSON file with the payload to insert data into the MySQL database.
  2. API Management accepts API calls in the form of HTTP requests with the JSON payload.
  3. API Management securely routes the HTTP requests to Logic Apps.
  4. Each HTTP request triggers a run in Logic Apps:
    • Logic Apps uses secured template parameters to retrieve database credentials from Azure Key Vault.
    • Logic Apps uses Transport Layer Security (TLS) to send the database credentials and a database statement.
  5. Data is now stored in MySQL database server and available to apps that users access.
  6. Azure Monitor collects information on Logic Apps events and performance to monitor this workflow.

Adding data to a MySQL database using Logic Apps

A Logic App can store HTTP request data in a MySQL database. Because Logic Apps function as a secure Azure API Management endpoint, calls to your API can trigger various data-related tasks. Besides updating the database, you can also set up the logic app to send an email or teams message when the data was inserted.

 

The Azure Database for MySQL connector supports more actions than just inserting new data. A complete list of the supported actions and triggers you can use to expand this architecture appears in the following table.

 

Action name

Description

Delete row

This operation deletes a row from a table.

Get row

This operation gets a row from a table.

Get rows

This operation gets rows from a table.

Get tables

This operation gets tables from a database.

Insert row

This operation inserts a new row into a table.

Update row

This operation updates an existing row in a table.

 

When building the solution, follow the guidance for availability, scalability, security, and cost optimization, as described below.

 

Availability

For high availability on Azure Database MySQL - Flexible Server, enable the high availability feature and configure high availability on your logic apps.

 

Scalability

With the serverless model that Logic Apps uses, the service automatically scales to meet demand. But monitor the reading and writing operations, and number of executions to make sure you are not going to hit the throttling limits.

 

Security

Review the following security considerations.

 

Cost optimization

You can follow these methods to optimize costs.

 

To estimate the cost of this solution in your environment, use the Azure pricing calculator. See Azure Database for MySQL pricing and Azure Logic Apps pricing.

 

Conclusion

With the detail provided above, you should now be able to take advantage of using Azure Logic Apps to move data to an Azure Database for MySQL flexible server to automate data integration tasks performed in response to API calls.

 

If you have any questions or suggestions, please leave a comment below or reach out to us at AskAzureDBforMySQL@service.microsoft.com. Thank you!

 

Related resources

Co-Authors
Version history
Last update:
‎Oct 18 2023 10:44 AM
Updated by: