Ole Germann Kristensen

Systems, Integration & Architecture

Tag: Reliability

  • NorthGrid: A Hybrid Integration case

    NorthGrid: A Hybrid Integration case

    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.

    Hybrid integration landscape linking on-premises servers, an electricity grid and cloud data services.
    A hybrid integration platform connects operational systems, cloud services and analytics.

    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

    IDConstraint
    C1The agreement system remains on-premises for at least three years.
    C2Cloud connectivity may be unavailable for four hours.
    C3A committed agreement must never be lost.
    C4Duplicate delivery is permitted; duplicate business effects are not.
    C5Billing requires ordering per agreement, not global ordering.
    C6PII must not appear in normal telemetry.
    C7Warehouse downtime must not block billing or CRM.
    C8BizTalk must be migrated incrementally.

    System context

    Customer portal, source systems and meters connect through an integration platform to operational, analytical and external consumers.
    System context for the fictional NorthGrid integration platform.

    The platform separates three interaction modes.

    ModeMechanismReason
    Immediate query or commandAPIThe caller needs an immediate answer.
    Agreement distributionDurable message topicA business event needs independent subscribers, retry and per-key ordering.
    Meter readingsPartitioned event streamThe 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

    Agreement database and outbox publish through a local buffer and message topic to operational and analytical consumers.
    Durable agreement distribution with independent operational and analytical consumers.
    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

    Local servers buffer messages while disrupted connectivity separates them from cloud services.
    Local store-and-forward keeps business data safe during a cloud outage.
    Connectivity moves from connected to disconnected, buffering, protection, recovery and normal operation.
    The complete lifecycle of a prolonged connectivity outage.

    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

    Recovery needs spare capacity

    If catch-up capacity only equals normal traffic, the backlog never disappears. The architecture therefore reserves additional recovery throughput and defines a protection state before storage reaches its limit.

    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

    Migration progresses from behaviour inventory through contract freezing, parallel comparison, ownership transfer and retirement.
    Boundary-by-boundary modernisation replaces behaviour without a big-bang rewrite.

    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

    TestPass condition
    Four-hour network cutNo committed agreement is lost.
    Deliver each event five timesOne business effect per consumer.
    Disable the warehouse for two hoursBilling and CRM remain within SLO.
    Run contract v1 and v2 togetherBoth complete without producer rollback.
    Restore connectivityAgreement backlog clears within 60 minutes.
    Scan telemetryNo 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.

  • Introducing the integration platform

    Introducing the integration platform

    When a warehouse update fails, someone has to decide whether to retry it, correct it, or wait for another system. That decision gets awkward when the only documentation is a scheduled task on a server and the person who wrote it is on holiday. This is the work I want an integration platform to make easier.

    For the fictional seafood company in this series, I would start with one flow: an accepted order in the ERP needs a stock reservation in the warehouse. It is a useful starting point because it has an identifiable business result, a failure that people care about, and a fairly clear owner on each side.

    Give the flow a contract and an owner

    I would write down what the ERP is promising when it emits OrderAccepted.v1. Has the customer been validated? Are quantities final? Can the order still change? The warehouse team needs those answers before it can implement a consumer. A JSON schema can check that quantity is a number. The teams still have to agree whether that number represents kilograms, boxes, or individual items.

    The event needs a stable eventId, an orderId, a schema version and a source revision. I would keep customer names and addresses out of this event unless the warehouse genuinely needs them. The integration repository should include an example message, schema, producing team, consuming team and recovery instructions. An owner should be a maintained team contact that operations can actually reach.

    {
  "eventId": "9f26c80e-5b91-438b-86c9-6f27a13f2195",
  "type": "OrderAccepted.v1",
  "orderId": "SO-1042",
  "revision": 3,
  "lines": [{ "itemId": "SALMON-BOX", "quantity": 12, "unit": "box" }]
}

    A small implementation I would start with

    In a Microsoft environment, one possible implementation is an ERP adapter, Azure Service Bus and a .NET worker that calls the warehouse API. Azure API Management can sit in front of HTTP endpoints where authentication policy, quotas and API publication are needed. It has a different job from the message broker. Adding it to an asynchronous flow needs a specific reason.

    If we can change the source application, I would write the order change and an outbox record in the same SQL transaction. A background relay sends committed outbox records to Service Bus. If the ERP is a packaged application, I would first investigate its supported event or change interface. Writing an outbox alongside a database we do not control requires care; an adapter cannot make two unrelated commits atomic just by calling them consecutively.

    The warehouse consumer receives a message under a lock, validates it and calls the reservation endpoint using a stable request identity. It completes the message after the warehouse has confirmed the result. A crash between the reservation and message completion causes a repeat delivery, so the warehouse endpoint needs to recognize the same reservation request. That agreement belongs in the contract.

    graph LR
 ERP[ERP transaction and outbox] --> RELAY[Outbox relay]
 RELAY --> BUS[Service Bus]
 BUS --> WORKER[Reservation worker]
 WORKER --> WH[Warehouse API]
 WORKER --> OPS[Outcome and error telemetry]

    Make uncertain outcomes visible

    Test the uncertain outcome

    A timeout is the case I would test early. The warehouse may have created the reservation just before the connection dropped. Retrying with a new identifier could reserve the stock twice. I would reuse the original identifier and, where supported, query the reservation status before another attempt. If the target supports neither idempotency nor status lookup, the recovery procedure needs a reconciliation step and probably an operator.

    Validation failures go to a controlled error path with a useful reason. Temporary connection failures get bounded retries with increasing delays. Repeated failures need an alert and an owner. A dead-letter queue is only useful if someone can explain why a message is there and what has to happen before it is safe to replay.

    What operations should be able to see

    For this flow, I want to search by orderId and see source acceptance, publication, receipt, reservation outcome and final completion. Each attempt should record duration, eventId and a technical error code. Full payload logging would expose more customer data than most investigations need.

    I would measure the age of the oldest unprocessed message as well as queue depth. The dashboard also needs the last successful reservation, dead-letter count and a link to the runbook. An example service target might be reservation confirmation within two minutes during warehouse operating hours; that target has to be agreed with the people using the result.

    Fifty messages arriving seconds ago and fifty messages stuck since yesterday need different responses.

    Build a repeatable way to add the next flow

    Once this path works, I would package its deployment, logging fields, health checks and contract tests so the next integration can reuse them. Bicep or Terraform can describe the broker resources and access assignments. The worker and schema live in version control, and a pipeline deploys the same artifact through test and production.

    The platform team can own that common setup while the warehouse team owns reservation rules. Keeping those responsibilities explicit helps avoid a central integration team becoming the accidental owner of every business decision. Some flows can remain simple scheduled transfers if their timing and recovery requirements allow it.

    Before calling the first flow ready, I would stop the worker during processing, send the same event several times, make the target time out and replay a corrected failed message. The expected warehouse state should be written down for each test. Those results give the next team something concrete to copy.

    For the broker behaviour behind this design, see Microsoft’s documentation on duplicate detection. Broker deduplication has a configured history window; the reservation endpoint still needs its own repeat-request handling.

  • Why Point to Point Integrations Break at Scale

    Why Point to Point Integrations Break at Scale

    A direct integration is often a reasonable first choice. If one system needs a small daily extract from another, a scheduled transfer can be easy to build and easy to explain. The trouble starts when more processes begin depending on that transfer and its original assumptions stay buried in the script.

    Consider a fictional seafood processor with an ERP, warehouse, production system, transport portal and reporting database. Sales uses warehouse stock to promise deliveries. Production updates the available lots. Quality approval determines which lots can ship. A stock export now carries several decisions that matter to people outside the warehouse team.

    Follow one stock update

    Suppose a PowerShell task exports stock every fifteen minutes and uploads a CSV to the ERP. At 18:00 on Friday the export fails. Both applications remain available, and the scheduler may even report success if the script caught an exception without returning a failing exit code. Sales keeps reading the last imported quantities.

    I would give that transfer a manifest containing an exportId, source timestamp, row count and checksum. The receiving job should record when it finished validating and applying the export.

    A successful file upload tells us about transport; we also need the import outcome before treating the stock as current.

    For a full snapshot, I would load into staging, validate the rows and publish the new snapshot atomically where the database design allows it. Replacing half the stock table before discovering a malformed row leaves readers with a mixture of two exports. A staging table also makes it easier to inspect rejected data without disturbing the last accepted snapshot.

    Make freshness part of the interface

    Freshness is part of the contract

    The stock response should carry an asOf timestamp. If the business agrees that stock older than thirty minutes is unsuitable for a delivery promise, the consuming application can flag it and ask for confirmation. Thirty minutes is an example threshold, not a universal integration rule. The warehouse and sales teams need to decide what delay they can tolerate.

    I would alert on the age of the last successfully applied export, with separate alerts for transfer failure and validation failure. This catches a job that never starts as well as one that crashes. Counting exceptions alone misses both a disabled scheduler and a script that keeps producing yesterday’s file.

    Changes expose the real dependencies

    A new warehouse version might change availableStock from individual units to cartons. The CSV still parses and all HTTP calls still return success. A contract test should catch that by checking representative items against agreed units and values. The same applies to cancelled orders, deleted items and time zones.

    With incremental extracts, I would document the cursor and deletion behaviour. A query based only on LastModified greater than the previous run time can miss records when timestamps collide or commits become visible late. A supported change feed is preferable where available. Otherwise the extraction design needs a stable ordering key, an overlap strategy and deduplication, plus a way to reconcile the full source periodically.

    This is where several independent scripts become expensive. Each one can implement a slightly different definition of stock, retry policy and cursor. A warehouse change then requires finding and testing every copy. A shared, versioned contract gives consumers a common interpretation even if several runtimes continue to carry the data.

    Count the dependencies you actually have

    For n systems, n × (n − 1) / 2 is the number of possible undirected pairs. Ten systems gives 45 pairs. That is an upper-bound illustration for a fully connected graph; it is not a prediction that every estate has 45 integrations. Direction, multiple contracts between the same systems and differing business rules complicate the count further.

    I would inventory the real flows instead. For each one, record the source, consumer, contract, schedule, credentials owner, last successful run and recovery method. Then ask which source changes require coordinated releases. That list is more useful for planning than a dramatic connection-count diagram.

    Improve the existing path before replacing it

    My first change would be to put the export code and its configuration template in version control, give it a maintained service identity and make failed runs fail visibly. Then I would add staging, freshness monitoring and a replay procedure using the original exportId. These changes can improve a scheduled transfer without a platform migration.

    A durable broker becomes useful when several consumers need independent retries, when the source must keep working during a target outage, or when the delay needs to be much shorter than the schedule. Each subscriber then needs its own progress and error handling. Moving the same undocumented payload onto a topic leaves the contract problem in place.

    I would start the migration with the flow causing the most operational pain. Disable its target during a test, restore it and check the recovered stock against the source. Repeat an export and verify that quantities stay correct. That gives a concrete basis for deciding which shared platform capabilities the next integration actually needs.