Data integration with Logic Apps and SQL Server

Azure API Management
Azure Logic Apps
SQL Server

This solution uses Azure Logic Apps to integrate cloud data into on-premises data storage.

The architecture illustrates the use of Azure API Management, the storage of secrets and API keys in Azure Key Vault, a connection to SQL Server through an on-premises data gateway, and performance monitoring with Azure Monitor. All of these components are integrated via Azure Logic Apps orchestration.


Architecture diagram showing how to use Logic Apps to respond to API calls by updating or accessing SQL Server.

The diagram contains two boxes, one for Azure components, and one for on-premises components. Outside the Azure box is a data file labeled JSON. An arrow points from the JSON file into an API Management icon that's inside the Azure box. A second arrow points from the API 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.


  1. API Management accepts API calls in the form of HTTP requests.

  2. API Management securely routes the HTTP requests to Logic Apps.

  3. Each HTTP request triggers a run in Logic Apps:

    1. Logic Apps uses secured template parameters to retrieve database credentials from Azure Key Vault.
    2. Logic Apps uses Transport Layer Security (TLS) to send the database credentials and a database statement to the on-premises data gateway.
  4. The on-premises data gateway connects to a SQL Server database to run the statement.

  5. SQL Server stores the data and makes it available to apps that users access.

  6. 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:

Scenario details

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.


These considerations implement the pillars of the Azure Well-Architected Framework, which is a set of guiding tenets that you can use to improve the quality of a workload. For more information, see Microsoft Azure Well-Architected Framework.


Reliability ensures that your application can meet the commitments you make to your customers. For more information, see Overview of the reliability pillar.

For high availability, add the on-premises gateway to a cluster instead of installing a standalone gateway.


Security provides assurances against deliberate attacks and the abuse of your valuable data and systems. For more information, see Overview of the security pillar.

Cost optimization

Cost optimization is about reducing unnecessary expenses and improving operational efficiencies. For more information, see Overview of the cost optimization pillar.

The following table provides cost profiles that use varying levels of expected throughput:

API Management Logic Apps action executions Logic Apps connector executions Profile
Basic 1,000/day 1,000/day Basic profile
Standard 10,000/day 10,000/day Standard profile
Premium 100,000/day 100,000/day Premium 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:

Performance efficiency

Performance efficiency is the ability of your workload to scale to meet the demands placed on it by users in an efficient manner. For more information, see Performance efficiency pillar overview.

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.


This article is maintained by Microsoft. It was originally written by the following contributors.

Principal authors:

To see non-public LinkedIn profiles, sign in to LinkedIn.

Next steps