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:
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.
- Create JSON file with the payload to insert data into the MySQL database.
- API Management accepts API calls in the form of HTTP requests with the JSON payload.
- API Management securely routes the HTTP requests to Logic Apps.
- 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.
- Data is now stored in MySQL database server and available to apps that users access.
- 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 |
|
This operation deletes a row from a table. |
|
|
This operation gets a row from a table. |
|
|
This operation gets rows from a table. |
|
|
This operation gets tables from a database. |
|
|
This operation inserts a new row into a table. |
|
|
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.
- Configure the networking access to the Azure Database for MySQL Flexible Server to restrict to certain IPs only. Get the inbound and outbound IP addresses used by the Logic Apps service in the Azure region where you create your logic app workflow.
- API Management helps to ensure that only authorized clients call your logic app. You can also take these steps:
- Since API Management is the only client that should call your logic app, consider restricting your app's inbound IP addresses. You can configure your logic app to only accept requests from the IP address of your API Management service instance.
- You can also use one of these authorization schemes to limit access to your logic app:
- Consider using Azure role-based access control (Azure RBAC) to only permit specific users or groups to manage, edit, and view your logic apps.
- Information is available on each logic app run, such as the status, duration, inputs, and outputs for each action. Use one of these methods to control who can access the inputs and outputs in the run history:
- Restrict access by IP address range.
- Use obfuscation to secure run history data.
Cost optimization
You can follow these methods to optimize costs.
- Run SQL statements in batches.
- Create stored procedures to organize database results in an efficient way.
- Specify precise trigger conditions for workflows.
- Turn off logic apps that don’t need to run constantly.
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