Data integration with Logic Apps and 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

Download a Visio file of this architecture.

Workflow

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.

Components

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.

Alternatives

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. Both services enable you to create serverless workflows. Azure Functions is a compute service that's intended for running custom code or complex processing or for integration with other services. Logic Apps is a cloud service that's intended for automating and orchestrating tasks and business workflows. It provides a visual designer and many pre-built connectors. For more information, see Compare Azure Functions and Azure Logic Apps.

• For simpler 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.

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.

Considerations

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

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

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

• 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:

    • Shared access signatures (SAS).
    • OAuth 2.0 with Microsoft Entra ID.

• 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

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:

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.

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.

Contributors

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

Principal authors:

• Beatriz Matsui | Consultant
• Shan Singh | Software Engineer

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

Next steps

• 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

Related resources

• 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 documentation

• Similar architectures:

  • 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.