For this case I wanted to work through a fairly awkward situation: an agreement system has to stay on-premises, cloud connectivity can disappear for four hours, and billing still needs every committed change. Those requirements give the design something concrete to answer.

This fictional case shows how I would structure a hybrid integration platform serving both operational and analytical workloads.

The case

NorthGrid is a fictional electricity supplier. Its agreement system remains on-premises, while billing, CRM, notifications, a market partner and analytics consume its data. Meter readings arrive continuously and feed operational monitoring and a warehouse.

The existing estate contains direct database reads, scheduled files, point-to-point APIs and BizTalk flows. Downstream teams often discover failures before the integration team does.

Governing constraints

ID	Constraint
C1	The agreement system remains on-premises for at least three years.
C2	Cloud connectivity may be unavailable for four hours.
C3	A committed agreement must never be lost.
C4	Duplicate delivery is permitted; duplicate business effects are not.
C5	Billing requires ordering per agreement, not global ordering.
C6	PII must not appear in normal telemetry.
C7	Warehouse downtime must not block billing or CRM.
C8	BizTalk must be migrated incrementally.

System context

The platform separates three interaction modes.

Mode	Mechanism	Reason
Immediate query or command	API	The caller needs an immediate answer.
Agreement distribution	Durable message topic	A business event needs independent subscribers, retry and per-key ordering.
Meter readings	Partitioned event stream	The workload is high-volume, time-oriented and replayable.

Five decisions

• D1: Commit business state and an outbox row atomically.
• D2: Store and forward locally during cloud disconnection.
• D3: Use at-least-once delivery with idempotent consumers.
• D4: Keep operational consumers independent of analytical availability.
• D5: Migrate BizTalk one contract boundary at a time.

Agreement distribution

AgreementDB → Topic : AgreementChanged.v1
[at-least-once, ordered by agreementId]

The agreement transaction updates its business tables and inserts an outbox row in one local transaction. A relay publishes the event asynchronously. Cloud availability is therefore removed from the source transaction.

No component claims exactly-once transport. Each consumer enforces uniqueness on the original eventId in the same transaction as its business effect. Delivery may be repeated; the business outcome may not.

Operational and analytical separation

Billing processes committed agreement events through its own subscription. Analytics advances an independent checkpoint and owns its own recovery and backfill.

Operational correctness must not depend on warehouse availability.

Billing and analytics can therefore be looking at different revisions for a while. Each consumer needs a visible last-processed revision and a recovery plan. Support should be able to tell whether a difference is expected lag or a missing event.

Four hours without cloud connectivity

An outage design requires three capacities:

1. Retention: how much data can be preserved?
2. Catch-up: how quickly can the backlog be drained while live traffic continues?
3. Protection: what happens before storage is exhausted?

buffer ≥ peak_rate × outage_window × payload_size × safety_factor

catch_up_rate > live_rate

Security and telemetry

Telemetry may contain technical identifiers, approved business keys, contract version, stage, outcome, latency, retry count and classification.

It must not contain full customer payloads, credentials, access tokens, unrestricted headers or raw failed messages outside controlled quarantine.

traceparent connects one technical execution. correlationId connects related business work. Neither replaces audit records.

Incremental BizTalk modernisation

I would migrate one externally observable behaviour at a time, such as delivery of an accepted agreement to billing. Several orchestrations, maps and pipeline components may contribute to that behaviour. Before moving it, inventory ordering, retry, context properties, correlations, maps, custom components, operator actions, throughput and known failure behaviour.

The replacement may run in shadow mode to compare outputs, but only one path may own each external side effect. Ownership moves once, after the acceptance baseline is met.

The Azure and SQL pieces I would use

For agreement distribution, I would use a SQL Server outbox in the source transaction, an on-premises .NET relay and an Azure Service Bus topic with separate subscriptions for billing, CRM and analytics. The durable local buffer in the diagram can initially be the outbox itself. A separate local broker would need a reason, such as several local producers or a different operational ownership boundary.

The relay marks a row as published after the broker acknowledges it. A crash between acknowledgement and that update will publish it again. The eventId therefore stays unchanged across attempts. If several relay instances run, their claim and lease mechanism must prevent them from casually overtaking revisions for the same agreement.

I would set SessionId to agreementId where ordered processing is required and include the source revision in the payload. Service Bus sessions provide ordered handling for related messages; they cannot repair a producer that publishes source revisions out of order. A consumer should detect a revision gap and hold dependent work for reconciliation. Dead-lettering an earlier revision also needs an explicit policy for the later ones.

For a consumer whose business effect is a local database update, I would store the processed eventId and that update in the same transaction, backed by a unique constraint on consumer identity and eventId. For an external billing API, that transaction cannot include the remote effect. The billing contract needs a stable idempotency key or a status query and reconciliation path. This distinction matters when an HTTP response is lost.

Put numbers on the four-hour buffer

As an illustrative sizing exercise, assume a sustained outage arrival rate of 20 agreement events per second with 4 KiB per event. Four hours produces 288,000 events, about 1.10 GiB of payload. A factor of three gives roughly 3.30 GiB for this payload budget. SQL row overhead, indexes, transaction logs, backups and other database activity require additional capacity; this calculation is only a starting point.

To drain those 288,000 events in one hour while 20 new events per second continue arriving, the complete downstream path must sustain at least 100 events per second: 20 live plus 80 recovery. I would benchmark the target writes as well as the relay. One heavily used agreement session can limit progress even if the broker has spare aggregate throughput.

Alerts should include the oldest unpublished outbox row, database free space and estimated time to exhaustion. Before storage fills, the application needs a defined response: throttle or reject new work visibly, or enter an agreed degraded mode. Quietly dropping committed events would violate the case requirement. Database durability, backup and restore testing are also required; the outbox only addresses the handoff between the transaction and messaging.

Keep meter history recoverable

For the meter stream I would evaluate Event Hubs with a partition key based on meter identity, separate consumer groups and retained raw readings in storage for longer recovery. Partition and retention settings need measured arrival volume and a backfill requirement. Agreement messages and meter readings have different payload sizes and processing costs, so I would size and load-test them separately.

Verification

Test	Pass condition
Four-hour network cut	No committed agreement is lost.
Deliver each event five times	One business effect per consumer.
Disable the warehouse for two hours	Billing and CRM remain within SLO.
Run contract v1 and v2 together	Both complete without producer rollback.
Restore connectivity	Agreement backlog clears within 60 minutes.
Scan telemetry	No restricted payload field or secret is present.

The first implementation I would verify

I would implement the agreement outbox, one relay and a test billing consumer first. The first demonstration should include a disconnected network, a relay crash after publication and a duplicate delivery. The database state and the billing result should be inspected after each test.

The figures and pass conditions here are design assumptions for a fictional case. They give the team a way to test the proposal before extending it to every consumer. I would keep the test results beside the configuration and runbook so the next change can be checked against the same expectations.