Power Automate with On-Prem SQL: Designing Reliable Hybrid Flows When Your Data Isn’t Fully in the Cloud

Scenario: Daily Order Ingestion Flow That Randomly Fails

The operations team has a flow:

1. Triggered when a CSV file is dropped into a SharePoint library.
2. Parses rows and inserts them into an on-prem Orders table via SQL connector.
3. Updates a status table and sends Teams notifications.

Symptoms:

• Some runs succeed; others fail with BadGateway or GatewayTimeout errors.
• Occasionally, only part of the file is inserted, but notifications still go out.
• Under higher volume, flow runs take much longer and hit the default 90-second SQL action timeout.

Constraints:

• SQL Server is on-prem, reachable only via the on-premises data gateway.
• No direct internet access from SQL; no Azure SQL copy yet.
• Flow must run unattended, with minimal manual retry.

We’ll fix this by focusing on four areas:

• Gateway architecture and configuration
• SQL connector behavior and transaction design
• Flow patterns for reliability and idempotency
• Monitoring, retry, and backoff

1. Get the Gateway Architecture Right First

If the gateway is unstable, no amount of clever flow logic will save you.

1.1 Use a Gateway Cluster for Availability

For production, rely on a gateway cluster, not a single gateway instance:

• Install at least two gateway instances on separate servers.
• Add them to the same gateway cluster in the Power Platform admin center.
• Point your SQL connector connection to that cluster.

Power Automate uses the cluster to distribute load and handle failover when one gateway node is unavailable. This doesn’t auto-retry a failed SQL action, but it reduces outright connection failures.

1.2 Place the Gateway Close to SQL

To reduce latency and timeouts:

• Install gateway nodes on servers with low-latency network paths to SQL.
• Avoid placing the gateway on a DMZ host that reaches SQL via multiple firewalls unless required.
• Ensure the gateway can reach SQL using the same protocol and port as your SQL client tools (typically TCP 1433 or a custom port).

High latency plus large result sets is a common cause of slow or failing SQL actions.

1.3 Configure SQL Connector Connections Carefully

When configuring the SQL connector connection for on-prem:

• Use Windows authentication or SQL authentication consistent with your security model.
• Ensure the account used has:
  • INSERT, UPDATE, DELETE as needed
  • Minimal SELECT rights (prefer stored procedures or views)
• Avoid using sa or other high-privilege accounts; this increases blast radius of mistakes.

For hybrid reliability, prefer stored procedures over ad-hoc INSERT/UPDATE actions in the flow. This lets SQL handle transactions and error handling closer to the data.

2. Understand SQL Connector Behavior and Timeouts

The SQL connector in Power Automate has specific behavior that affects reliability:

• Actions are executed over the gateway; they are synchronous per action.
• Each action has a timeout (commonly 90 seconds) and a retry policy you can configure.
• There is no transaction spanning multiple actions in a flow; each SQL action is its own database call.

2.1 Avoid Row-by-Row Inserts from the Flow

In our scenario, the original flow:

• Loops over each CSV row
• Calls Insert row for each row individually

Problems:

• Many small round trips through the gateway
• Increased chance of hitting timeout or transient network issues
• Hard to guarantee all-or-nothing behavior

Better pattern:

• Upload the file to SQL via a single stored procedure call.
• Let SQL parse, validate, and insert rows inside a transaction.

2.2 Use Stored Procedures with Transaction Logic

Create a stored procedure that:

• Accepts a structured input (e.g., table-valued parameter or JSON string)
• Inserts rows in a single transaction
• Returns a success/failure status and diagnostic info

Example: using a table-valued parameter for orders.

CREATE TYPE dbo.OrderImportType AS TABLE
(
    OrderNumber       nvarchar(50)   NOT NULL,
    CustomerCode      nvarchar(50)   NOT NULL,
    OrderDate         date           NOT NULL,
    Amount            decimal(18, 2) NOT NULL
);
GO

CREATE PROCEDURE dbo.ImportOrders
    @BatchId       uniqueidentifier,
    @Orders        dbo.OrderImportType READONLY
AS
BEGIN
    SET NOCOUNT ON;

    BEGIN TRY
        BEGIN TRANSACTION;

        INSERT INTO dbo.Orders
        (
            BatchId,
            OrderNumber,
            CustomerCode,
            OrderDate,
            Amount
        )
        SELECT
            @BatchId,
            OrderNumber,
            CustomerCode,
            OrderDate,
            Amount
        FROM @Orders;

        COMMIT TRANSACTION;

        SELECT 1 AS Success, 'OK' AS Message;
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0
            ROLLBACK TRANSACTION;

        SELECT
            0 AS Success,
            ERROR_MESSAGE() AS Message;
    END CATCH;
END;
GO

This gives you:

• Atomic inserts per batch
• A clear success/failure signal to the flow
• A natural place to add validation and duplicate detection

In Power Automate, call this procedure with the Execute stored procedure action and pass a batch ID and a table-valued parameter.

Note: the SQL connector supports table-valued parameters when the stored procedure and connection are configured appropriately; if your environment doesn’t, you can pass JSON as nvarchar(max) and parse it in SQL.

3. Design the Flow for Idempotency and Partial Failure

Even with a solid gateway and stored procedures, hybrid flows must assume:

• Some runs will fail mid-way.
• Some actions will be retried.
• Network or gateway issues will be transient.

3.1 Use a Batch ID to Make Operations Idempotent

In the scenario, one file may be processed twice if:

• The flow retries after a timeout.
• A user manually re-uploads the file.

Introduce a BatchId:

• Generate a GUID at the start of the flow.
• Pass it to SQL and store it in the Orders table.
• Use SQL logic to prevent duplicate inserts for the same batch.

Example SQL constraint:

ALTER TABLE dbo.Orders
ADD CONSTRAINT UQ_Orders_Batch_OrderNumber
UNIQUE (BatchId, OrderNumber);

If the flow retries with the same BatchId and OrderNumber, the insert fails, but the stored procedure can treat this as a duplicate rather than a hard failure.

3.2 Separate “Write” and “Notify” Paths

Original flow problem:

• Notifications go out even when only part of the file made it into SQL.

Fix:

1. Step 1: Call ImportOrders stored procedure.
2. Step 2: Check the returned Success flag.
3. Step 3: Only send Teams/email when Success = 1.
4. Step 4: On failure, log and alert a support channel instead.

This ensures downstream actions reflect the actual database state as reported by SQL.

3.3 Use Retry Policies on Transient Actions Only

For SQL actions in Power Automate, you can configure the retry policy:

• Default: exponential retry with limited attempts
• Custom: specify count and interval

Use retries when:

• Failures are likely transient (gateway busy, short network blip)

Avoid retries when:

• The operation is not idempotent and may cause duplicates

In our pattern, the stored procedure with BatchId and uniqueness constraints makes the operation effectively idempotent, so retries are safer. Still, keep retry counts modest to avoid hammering the gateway during outages.

4. Control Concurrency and Load

Hybrid flows often break when multiple runs hit the gateway and SQL simultaneously.

4.1 Limit Trigger Concurrency

For the SharePoint When a file is created trigger:

• Open the trigger settings.
• Set Concurrency Control to On and choose a reasonable degree of parallelism (often 1–3 for on-prem SQL).

This prevents dozens of file-based runs from hitting SQL at once.

4.2 Use Batching Inside the Flow

If a single file contains many rows:

• Parse the file in Power Automate.
• Group rows into batches (e.g., 1000 rows per batch) if needed.
• Call the stored procedure once per batch.

This balances transaction size and lock duration on SQL:

• Too small: many calls, gateway overhead
• Too large: long-running transactions, lock contention

The right batch size depends on your SQL hardware and schema; start with a moderate size and adjust based on observed performance.

4.3 Avoid Large Result Sets Back to the Flow

Reading large datasets out of on-prem SQL through the gateway is a common source of slow runs and timeouts.

Prefer patterns where:

• SQL does the heavy aggregation/filtering.
• The flow retrieves only small, targeted result sets (e.g., a summary row or a list of IDs).

If you must move large datasets, consider:

• Writing them to a staging table and having another process move them (e.g., SSIS, Azure Data Factory).
• Using Power BI or other tools for bulk data movement rather than Power Automate.

5. Monitoring and Operational Practices

Reliable hybrid flows need good visibility when things go wrong.

5.1 Log at Both Ends: Flow and SQL

In the scenario, add logging in two places:

1. Flow-level logging

   • Use a custom table (e.g., FlowRuns) in SQL.
   • Insert a row at the start and end of each run with:
     • RunId (flow run ID)
     • BatchId
     • Status (Started, Succeeded, Failed)
     • ErrorMessage (on failure)

   Example insert in SQL (called from the flow):

   INSERT INTO dbo.FlowRuns
   (
       RunId,
       BatchId,
       Status,
       ErrorMessage,
       LoggedAt
   )
   VALUES
   (
       @RunId,
       @BatchId,
       @Status,
       @ErrorMessage,
       SYSUTCDATETIME()
   );
   

2. SQL-level logging

   • Inside ImportOrders, log failures to an OrderImportErrors table.
   • Include batch ID, error message, and any context you need.

This gives you a full picture when comparing Power Automate run history with SQL state.

5.2 Use Run-After and Scoped Error Handling

Wrap your SQL actions in a Scope and configure Run After conditions:

• Scope ImportOrdersScope containing the stored procedure call.
• Scope NotifySuccessScope that runs only if ImportOrdersScope is Succeeded.
• Scope NotifyFailureScope that runs if ImportOrdersScope is Failed or Timed out.

This avoids orphan notifications and gives a clean separation of success vs failure handling.

5.3 Set Sensible Timeouts and Alerting

For critical SQL actions:

• Adjust the timeout only if you have evidence that operations legitimately need more time.
• Keep in mind that longer timeouts tie up gateway resources.

Set alerts:

• Use Power Automate analytics or custom flows to detect repeated failures.
• Notify a support channel when the failure rate crosses a threshold.

Before/After: How the Daily Order Flow Changes

Before:

• Trigger: SharePoint file created
• Logic:
  • Parse CSV
  • For each row: Insert row to Orders
  • Send Teams notification
• Behavior:
  • Random gateway errors
  • Partial inserts with full notifications
  • Hard to debug and reconcile

After:

• Trigger: SharePoint file created (with concurrency limited)
• Logic:

1. Generate BatchId and capture RunId.
2. Log Started to FlowRuns.
3. Parse CSV into a collection of rows.
4. Convert rows to a structure suitable for ImportOrders (e.g., table-valued parameter or JSON).
5. Call ImportOrders via Execute stored procedure.
6. Based on Success:
   • On success: log Succeeded, send Teams notification.
   • On failure: log Failed, send alert to support.

Result:

• Gateway issues surface as failed runs with clear context.
• SQL inserts are atomic per batch and idempotent via BatchId.
• Notifications reflect actual database state.

One Practical Takeaway

When using Power Automate with on-prem SQL through the gateway, treat SQL as the system-of-record for transactions and idempotency, and use the flow mainly to orchestrate calls and react to the success/failure signals. Moving critical logic into stored procedures, adding a batch ID, and tightening concurrency and error handling around the SQL actions is usually the single biggest shift that turns unreliable hybrid flows into something you can trust in production.