This solution uses Azure Logic Apps to integrate cloud data into on-premises data storage.
The diagram contains two boxes, one for Azure components, and one for on-premises components. Outside the Azure box is a data file labeled J S O N. An arrow points from the J S O N file into an A P I Management icon that's inside the Azure box. A second arrow points from the A P I Management icon to a Logic Apps icon that's also inside the Azure box. Three arrows point away from the Logic Apps icon. One leads to a Key Vault icon that's inside the Azure box. One leads to an on-premises data gateway icon that's between the two boxes. And the third leads to an Azure Monitor icon that's inside the Azure box. Another arrow points from the gateway to a SQL Server icon that's inside the on-premises box. A final arrow points from the SQL Server icon to a person outside the on-premises box.
Download a Visio file of this architecture.
API Management accepts API calls in the form of HTTP requests.
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 to the on-premises data gateway.
The on-premises data gateway connects to a SQL Server database to run the statement.
SQL Server stores the data and makes it available to apps that users access.
Azure Monitor collects information on Logic Apps events and performance.
This architecture uses the following components:
Azure API Management 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 call metadata.
Azure Logic Apps 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.
An on-premises data gateway acts as a bridge that connects on-premises data with cloud services like Logic Apps. Typically, you install the gateway on a dedicated on-premises virtual machine. The cloud services can then securely use on-premises data.
Azure Key Vault 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.
SQL Server provides a solution for storing and querying structured and unstructured data. This database engine features industry-leading performance and security.
Azure Monitor collects data on environments and Azure resources. This information is helpful for maintaining availability and performance. Other Azure services, such as Azure Storage and Azure Event Hubs, can also use this diagnostics data. Two data platforms make up Monitor:
- Azure Monitor Logs records and stores log and performance data. For Logic Apps, this data includes information on trigger events, run events, and action events.
- Azure Monitor Metrics collects numerical values at regular intervals. For Logic Apps, this data includes the run latency, rate, and success percentage.
A few alternatives exist for this solution:
Instead of using an on-premises instance of SQL Server, consider migrating to an up-to-date, fully managed Azure database service. The SQL Server connector that Logic Apps uses also works for Azure SQL Database and Azure SQL Managed Instance. For more information, see Automate workflows for a SQL database by using Azure Logic Apps. To get started with migration, see Azure Database Migration Service.
For complex automation tasks, consider using Azure Functions instead of Logic Apps. For more information, see Compare Azure Functions and Azure Logic Apps.
For simple integrations, consider using Power Automate instead of Logic Apps. For more information, see Compare Microsoft Power Automate and Azure Logic Apps.
Power Apps also provides solutions for automating workflows that involve connecting to on-premises data sources.
A logic app can store HTTP request data in a SQL Server database. Because Logic Apps functions as a secure Azure API Management endpoint, calls to your API can trigger various data-related tasks. Besides updating on-premises databases, you can also send Teams or email messages.
Potential use cases
Use this solution to automate data integration tasks that you perform in response to API calls.
Keep these points in mind when considering this architecture.
For high availability, add the on-premises gateway to a cluster instead of installing a standalone gateway.
With the serverless model that Logic Apps uses, the service automatically scales to meet demand. But be aware of limits on read and write operations with the on-premises data gateway.
The on-premises data gateway uses credential encryption and user authentication to protect data during transfers between on-premises and Azure systems.
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:
The following table provides cost profiles that use varying levels of expected throughput:
|API Management||Logic Apps action executions||Logic Apps connector executions||Profile|
The profiles don't include the costs of a SQL Server database. To adjust the parameters and explore the cost of running this solution in your environment, use the Azure pricing calculator.
Explore these strategies for minimizing Logic Apps 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 have to run constantly.
This article is maintained by Microsoft. It was originally written by the following contributors.
- Shan Singh | Software Engineer
- Beatriz Matsui | Consultant – Azure Cloud & AI
- Import a Logic App as an API
- Install an on-premises data gateway for Azure Logic Apps
- Connect to on-premises data sources from Azure Logic Apps
Azure Serverless: Overview for building cloud-based apps and solutions with Azure Logic Apps and Azure Functions
Automate workflows for a SQL database by using Azure Logic Apps
- On-premises data gateway for Azure Logic Apps: A logic app triggered by Azure Spring Apps that connects to an on-premises SQL Server instance.
- Enterprise integration using queues and events: Logic apps that respond to API calls by integrating backend systems.
- Serverless web application: A serverless web app that uses Azure Functions to read database data.