Conection with ERP

Question

Conection with ERP

Alexander Lopez 20

Hello, good afternoon. I would like to assess the viability of a development proposal using Azure infrastructure. Currently, my business runs locally using a series of scripts executed via Windows Task Scheduler. These scripts connect to Acumatica and ultimately store data in my SQL Server Management Studio database. I want to migrate to your cloud architecture, and my idea is as follows: rewrite my scripts as Azure Functions, handle the scheduling using a Logic Apps timer, and use an Azure SQL Database. Would this approach work? Also, what would the approximate costs be if the scheduled tasks run every hour?

Pravallika KV 13,305 Reputation points Microsoft External Staff Moderator

2026-03-26T15:03:29.0033333+00:00

Hi @Alexander Lopez , Following up to see if you have checked the above response and if it was helpful. Please let me know if you have any questions.

Answer accepted by question author

1 additional answer

Your answer

Pravallika KV 13,305 Reputation points Microsoft External Staff Moderator

2026-03-26T15:03:29.0033333+00:00

Hi @Alexander Lopez , Following up to see if you have checked the above response and if it was helpful. Please let me know if you have any questions.

Answer 1

Pravallika KV 13,305 Microsoft External Staff Moderator

Hi Alexander Lopez,

Thanks for reaching out to Microsoft Q&A.

Migrating your on-premises scripts to Azure using Azure Functions, Logic Apps, and Azure SQL Database is a solid and widely adopted approach.

You can rewrite each of your existing scripts as Azure Functions using PowerShell, C#, or Python, and handle scheduling either through a built-in Timer Trigger within the Function App using a CRON expression or by using a Logic App with a Recurrence trigger to invoke the function every hour via HTTP.

The processed data can then be stored in Azure SQL Database, with connection strings securely managed through Key Vault or application settings.

Why it works?

This design is effective because timer triggers are natively supported in Azure Functions, no extra infrastructure is needed for scheduling.
Logic Apps offers a super-simple “visual” schedule and built-in retry/durable workflows if you need more orchestration
Azure SQL is the equivalent of your SQL Server, fully managed, auto-backups, geo-replication, etc.

Rough cost estimation (in USD/month)

Azure Functions (Consumption Plan):

Executions: 720 runs/month (once per hour) well below the free grant of 1 M executions
Memory/compute: even if each run uses 512 MB for 30 s, that’s ~0.006 GB-hours per run → ~0.004 GB-hours total/month =>effectively free

Logic Apps scheduling:

Recur trigger + 1 HTTP action = ~2 actions per run
Actions cost $0.000025 each → 2 × 720 × $0.000025 ≈ $0.036/month

Azure SQL Database

Depends on performance level: Basic DTU starts around $5/month, Standard from ~$15–$75, vCore tiers ~$100+ depending on cores/storage

Networking, storage, Key Vault, monitoring: small extras, but generally low for this scale. Even with conservative sizing, you’d be in the low two-digit dollars per month range.

References:

Hope this helps!

If the resolution was helpful, kindly take a moment to click on User's image and click on Yes for was this answer helpful. And, if you have any further query do let us know.

Alexander Lopez 20 Reputation points

2026-03-26T16:49:53.8966667+00:00

I have reviewed your previous responses, and they have been extremely helpful. As I move forward, I am trying to define the best approach for my database infrastructure.

I am weighing two options: implementing a Global Database (Multi-tenant) where a specific field identifies the source ERP, versus creating a Separate Database for each client (Single-tenant). I would appreciate your insights into the trade-offs regarding scalability, security isolation, and cost-efficiency between these two models.

Additionally, considering the Colombian regulatory environment, these ERPs undergo mandatory updates almost every year. To handle this, I am planning to implement a "Normalization/Homogenization Layer" within the architecture we discussed. The goal is for this layer to interpret and transform varying data formats from different ERPs into a standardized schema before storing it in Azure SQL.

However, I have a concern regarding Idempotency and Data Duplication. If a trigger executes twice or an API call is retried, we might fetch the same information again. Does Azure offer a native way to handle record de-duplication within this flow, or should this logic be handled strictly at the Azure Function/Database level (e.g., using UPSERT logic or unique constraints)?

Thank you very much for your time and guidance. @Pravallika KV
Pravallika KV 13,305 Reputation points Microsoft External Staff Moderator

2026-03-26T17:51:27.9133333+00:00
@Alexander Lopez , Please check below insights:

1. Database Tenancy Model

Global (Multi-tenant) Pros:

Lowest cost per tenant you run one Azure SQL instance (or elastic pool) and slice it by a TenantID column.

Simplified patching, backup, and monitoring.

Cons:

Noisy-neighbor risk one busy tenant can impact all.

More complex security rules and row-level security (RLS) if you need strict isolation.

Harder to meet per-tenant compliance (e.g., keeping data in specific regions).

Separate DB per Client (Single-tenant) Pros:

Strong isolation each customer’s data lives in its own database.

Compute and storage can be tailored per customer, with individual backup/restore and geo-replication.

Simplifies regulatory compliance for data sovereignty.

Cons:

Higher management overhead more databases to patch, monitor, and secure.

Higher cost if you provision a full Azure SQL tier per small customer (though Elastic Pools can help).

Cost Optimization Tip:

Place many small single-tenant databases into an Elastic Pool so they share DTUs/vCores, paying only for the resources actually used.

Idempotency & Deduplication:

Azure timer triggers and Logic Apps are “at least once”, so duplicates can occur if an invocation retries.

Azure does not provide native SQL-level deduplication in Functions or Logic Apps. This should be handled in your code or database schema:

You can use below recommended approaches:

Use UPSERT/MERGE logic in your Azure Function to insert new rows and update existing records on conflict.

Enforce UNIQUE constraints or primary keys in your Azure SQL table on the business key that represents a “duplicate” record.

Optionally maintain a tracking table of processed message IDs or hashes. If a function sees an ID already in the table, it can skip processing.

For higher-level orchestration, consider Durable Functions, which support patterns for exactly-once workflows, but your business logic should still be idempotent.

Answer 2

The proposed architecture is viable and aligns with recommended Azure patterns for background jobs and scheduled tasks.

Azure Functions for the scripts
- Background jobs that don’t run for a long time are a good fit for Azure Functions.
- Functions can be triggered on a schedule directly with a Timer trigger using a CRON expression, so in many cases a separate Logic Apps timer isn’t required.
- Example C# timer trigger (runs every 15 minutes in the sample, but can be adjusted to every hour):
```
     [FunctionName("TimerTriggerCSharp")]
     public static void Run([TimerTrigger("0 */15 * * * *")]TimerInfo myTimer, ILogger log)
     {
         if (myTimer.IsPastDue)
         {
             log.LogInformation("Timer is running late!");
         }
         log.LogInformation($"C# Timer trigger function executed at: {DateTime.Now}");
     
         // Call Acumatica and write to Azure SQL
     }
```
- For an hourly schedule, the CRON expression in the Timer trigger can be changed accordingly (for example, 0 0 * * * * for once per hour).
Logic Apps vs. Functions for scheduling
- Logic Apps are well-suited for orchestrating workflows across multiple services and systems, especially when many connectors or complex orchestration are needed.
- If the requirement is primarily “run code on a schedule,” Azure Functions with a Timer trigger is simpler and avoids the extra Logic Apps cost and complexity.
- Logic Apps can still be used if there is a need to visually orchestrate multiple steps, call other SaaS systems, or combine several functions.
Azure SQL Database
- Replacing the on-premises SQL Server with Azure SQL Database fits the pattern of moving background jobs to Azure.
- Functions can connect to Azure SQL Database using standard connection strings and client libraries.
Cost considerations (qualitative)
- Azure Functions: billed mainly on execution count, execution time, and memory consumption in the Consumption plan. Running a short function once per hour is typically low cost, but exact cost depends on execution duration, memory usage, and number of functions.
- Logic Apps: pay-as-you-go based on the number of actions and connectors executed in the workflow. Adding a Logic App timer plus actions to call Functions or APIs adds per-action costs.
- Azure SQL Database: charged per chosen service tier (DTU/vCore, storage, etc.), independent of how often the job runs.
- Because pricing depends on region, chosen tiers, and actual usage, exact cost cannot be derived from the provided context. However, architecturally, running hourly scheduled tasks with Functions and Azure SQL is a standard, cost-efficient pattern when the workload is modest.
Alternative: Functions-only scheduling
- To simplify and minimize Logic Apps charges, consider:
  - Use Azure Functions with a Timer trigger for hourly scheduling.
  - Use Azure SQL Database for storage.
  - Introduce Logic Apps only if workflow orchestration or many external connectors are required.

References:

Share via

Conection with ERP

1 additional answer

Your answer