Azure service guidance for an intermittent process

Question

Azure service guidance for an intermittent process

ritmo2k 951

I have a use case where I need to move a database operation out of a web application and serialize it to better cope with concurrent submissions. The code to process the operation uses the SqlBulkCopy class to stage the data in temporary tables in an Azure SQL database, then performs a merge. The entire process usually takes 10 seconds, I expect to see no more than 10 at any one time (although I don't have reliable control over that and the service could receive 75 requests resulting in ~15 minutes of execution).This sounds like an ideal use case for an Azure Service Bus with an Azure Function configured to run without concurrency. In this model, since the message processing time is 10 seconds, and the function would be configured to process a single message without concurrency, this seems like a reasonable approach.

Does this sound reasonable, or is there a better approach?

ritmo2k 951 Reputation points

2026-05-15T15:29:26.95+00:00

Hi @Pravallika KV ,
I appreciate your feedback, it was very helpful. Please convert your comment into an answer so I may mark it as the solution.

Thank you.
Pravallika KV 18,850 Reputation points Microsoft External Staff Moderator

2026-05-15T15:30:49.98+00:00

@ritmo2k ,Thanks for the confirmation, glad it was helpful.

Answer accepted by question author

Pravallika KV 18,850 Microsoft External Staff Moderator

Hi @ritmo2k ,

Thanks for reaching Microsoft Q&A.

What you’re describing, offloading those 10 second SQL bulk-copy/merge jobs onto a queue and having a single‐concurrency worker chew through them is a classic queue‐based load‐leveling pattern. It’s a solid approach, you can also check below extra considerations to make it even more robust:

Use Azure Service Bus queue (or subscription) + Azure Function (or WebJob) trigger
- Configure maxConcurrentCalls (or host.json’s batchSize/prefetchCount) to 1 if you truly want one‐at‐a‐time processing.
- If you later need to speed up catch-up, you can bump that value or spin up multiple function instances (Premium plan or App Service Plan).
Handle failures and retries
- Make your merge operation idempotent so that duplicate deliveries (at‐least‐once model) don’t corrupt data.
- Enable dead-lettering for poison messages and monitor the dead-letter queue.
Think about ordering (if it matters)
- If jobs must run strictly in the order they arrived, consider Service Bus sessions. That way, messages in the same session get locked and processed sequentially.
Monitor throughput & scale
- Track Service Bus metrics (Incoming/Outgoing messages, Throttled requests) to see if you need to scale up messaging units or move to a Premium namespace.
- Watch for TimeoutException or ServerBusyException and implement exponential back-off/retry per the transient-fault guidance.
Alternative/batch patterns
- If you find yourself with dozens of 10s jobs backing up, you could switch to a “mini‐batch” timer trigger: accumulate N payloads in a staging table or storage blob and then run a single bulk‐merge to reduce per-job overhead.
- Durable Functions can orchestrate multi-step workflows or long-running jobs if you outgrow the 5-minute function timeout (on Consumption plan).

Bottom line: Yes, Service Bus + single‐worker Function is a reasonable and well-architected approach for smoothing out spikes. You can start there, then tune concurrency, batching, and scale‐up as your load dictates.

0 comments

1 additional answer

Your answer

ritmo2k 951 Reputation points

2026-05-15T15:29:26.95+00:00

Hi @Pravallika KV ,
I appreciate your feedback, it was very helpful. Please convert your comment into an answer so I may mark it as the solution.

Thank you.
Pravallika KV 18,850 Reputation points Microsoft External Staff Moderator

2026-05-15T15:30:49.98+00:00

@ritmo2k ,Thanks for the confirmation, glad it was helpful.

Answer 1

Using Azure Service Bus with an Azure Function that processes messages one at a time is a reasonable and well-aligned approach for this scenario, provided the following design points are considered.

Decouple and serialize the database work

Moving the SqlBulkCopy + MERGE logic out of the web app into a background processor follows the recommended pattern of asynchronous processing and background jobs.
A queue (Service Bus) between the web app and the processing logic implements queue-based load leveling, smoothing bursts of up to 75 requests and preventing the web app from blocking or timing out.
The web app should enqueue a message and return quickly, keeping the UI responsive.

Use Service Bus and Functions as a background job pipeline

Service Bus provides durable, ordered messaging and built-in retry and dead-letter capabilities, which are recommended for background processing.
An Azure Function with a Service Bus trigger is a good implementation of a background job worker.
Configure the Function host to process messages with low or single concurrency when the database operation must be serialized (for example, to avoid contention on temp tables or MERGE targets).

Ensure idempotent and resilient processing

The database operation must be idempotent, because Service Bus may deliver a message more than once (for example, after retries or transient failures). Design the MERGE and staging logic so that reprocessing the same message does not corrupt data.
Use Service Bus retry and dead-letter behavior to handle poison messages. Messages that repeatedly fail should be moved to a dead-letter queue for investigation rather than blocking the main queue.

Throughput and scaling considerations

With ~10 seconds per operation and a single active message, the steady-state throughput is ~6 operations per minute. A burst of 75 requests would take about 12–13 minutes to drain, which matches the expectations.
If future requirements demand higher throughput, the same pattern can be scaled by:
- Increasing function concurrency and ensuring the database logic can safely handle parallelism (for example, partitioning work by key, or using separate staging tables per request).
- Scaling compute based on queue depth so that more workers are added when the queue grows.

Database-side performance

Using SqlBulkCopy for staging is appropriate for larger batches and is recommended for high-throughput inserts, especially when batch sizes reach hundreds or thousands of rows.
Ensure batching strategy (batch size, frequency) is tuned for Azure SQL performance and resilience, and that the MERGE logic is efficient and indexed appropriately.

User experience

Because processing is asynchronous, the web app should clearly communicate that the operation is in progress and provide a way to check status or receive a notification when the job completes.

Overall, the proposed design—web app → Service Bus queue → single-concurrency Azure Function using SqlBulkCopy and MERGE—is a sound and recommended approach for intermittent, potentially bursty database operations that need serialization and resilience.

References: