What a sage 200 legacy integration is actually up against

A sage 200 legacy integration normally starts with the same description: Sage 200 has been running on a server in the office since before the current finance manager joined, the storefront was rebuilt on Shopify three years ago, and somebody spends two hours each morning re-keying orders from one into the other. The business logic is usually not complicated. What makes these projects fail, or quietly overrun, is the estate the ERP sits in — the operating system version, the network, the licensing, and the maintenance habits that have grown up around a machine nobody wants to touch.

The first question in discovery is which Sage 200 you actually have. The name has covered several quite different products. Sage 200 Professional and Sage 200 Standard in their cloud-connected forms expose a documented web API over HTTPS. Older on-premise installations — and a surprising number of them are still in daily use — expose nothing of the sort. What they offer instead is a set of .NET business object assemblies that must run on a machine with the Sage 200 client installed and correctly licensed, plus a SQL Server database that you can read from directly if you are careful. These are not interchangeable, and an estimate built on the wrong assumption will be wrong by an order of magnitude.

The second question is the network. Almost every site of this age has no inbound route to the Sage server, which is correct and should stay that way. There may be a dynamic WAN address, a consumer-grade firewall, and a VPN that was set up for one remote user in 2017. Opening a port to allow a cloud service to reach into the LAN is the wrong answer, and in our experience it is also the answer the client's own IT provider will refuse, correctly. The design has to assume outbound connectivity only.

The third question is operational. These are the details that decide whether a go-live is calm or not, and they belong in the discovery document rather than being discovered at two in the morning.

  • Sage version and edition — Record the exact build, not just "Sage 200". Whether the cloud-connected API is present, and whether the client's licence covers it, changes the whole architecture.
  • Operating system and .NET runtime — Windows Server 2012 R2 and older .NET framework versions default to TLS 1.0 and 1.1. Cloud platforms require TLS 1.2 or above. This is a registry and runtime configuration change, and it needs testing before build starts.
  • Licence seats — An integration that uses the business object layer consumes a Sage concurrent user. If the site has five seats and five users, the integration is a sixth and the client needs to know that in writing before the estimate is signed.
  • Backup and maintenance windows — Nightly backup jobs, SQL maintenance plans, patch reboots and antivirus scans all take the server away for a period. The integration must survive these without manual intervention.
  • Year-end and month-end practice — Posting into Sage during a nominal ledger update or a stock take is a reliable way to generate support calls. Find out who runs those routines and when.

The bridge service: outbound connections only, store and forward in the middle

The pattern that holds up in production is a small Windows service installed on the LAN — on the Sage application server itself, or on a separate machine with the Sage client installed — that initiates every connection outwards. It polls a cloud-side queue over HTTPS, pulls work, performs it against Sage, and reports the result back. Nothing on the internet ever needs to know the client's WAN address, and no firewall rule is created. If the office internet connection changes provider, the integration does not care.

Between the storefront and the bridge sits a durable queue. Whether orders arrive from Shopify webhooks or from WooCommerce, they land in that queue first and are acknowledged to the source immediately. The bridge then takes them one at a time, with an idempotency key derived from the storefront order identifier so that a retried message cannot produce a second sales order. The queue is what makes the ageing server tolerable: it can be offline for a scheduled reboot, a power cut or a six-hour Windows update, and the backlog simply drains when it returns. Nothing is lost and nobody keys anything by hand.

The bridge should also emit a heartbeat on a short interval — every sixty seconds is reasonable — and the cloud side should alert when two consecutive heartbeats are missed. The failure mode we see most often in homegrown versions of this design is silence: the service stopped on Tuesday, nobody noticed until Friday, and there are four days of orders to reconcile. Monitoring the absence of activity matters more than monitoring errors, because errors at least announce themselves.

On security, use mutual TLS or a signed token issued per installation, store credentials using DPAPI or Windows Credential Manager rather than in a configuration file, and give the service its own domain and SQL accounts with the minimum rights needed. A named employee's credentials in a service account is a problem waiting for that employee's leaving date. All of this is cheap to do at build time and expensive to retrofit.

Writing into Sage 200 without going around it

There are two ways to get a sales order into an on-premise Sage 200 database, and only one of them is defensible. Direct SQL inserts into the order tables appear to work — the order shows in the list, the finance team sees it — but Sage maintains a considerable amount of state alongside the visible records: audit entries, allocation records, running balances, sequence counters and analysis postings. Writing around the application layer produces a database that reconciles today and does not reconcile at the next period end, and it voids any support position with the vendor. Reads are a different matter. Querying the database directly for stock levels, price bands and customer account status is reasonable, provided the queries are read-only, indexed sensibly, and do not run during the backup window.

Writes should go through the Sage 200 business object layer, which applies the same validation a user at a keyboard would trigger. That validation is the point. It will refuse an order for an account on hold, refuse a line for a discontinued item, apply the correct tax code and nominal analysis from the customer and item records, and allocate stock according to the company's settings. Each of those refusals is a business event that needs a defined response: a storefront order for an account over its credit limit should not disappear, and it should not retry in a loop forever either.

We recommend a parking area — a review queue visible to the finance team — holding any order that Sage declined, with the actual reason in plain language and a button to retry once the underlying issue is fixed. In practice, the common reasons are a small set: unmatched customer account, item code not found, price discrepancy beyond tolerance, account on hold, and stock insufficient where the company does not permit negative allocation. Agreeing the handling for each of those five before build begins removes most of the argument from acceptance testing.

Concurrency deserves specific attention. Sage 200 takes locks during posting routines, and a bridge that attempts to create orders while a user is running a nominal ledger update will produce timeouts that look like integration faults. Serialise your own writes — one at a time, not a thread pool — and make the processing window configurable so that it can be paused during month-end. Finally, write the resulting Sage order number back against the storefront order as a metafield or custom field. It costs nothing, and it is the single most useful artefact when somebody asks six months later whether order 10482 ever reached the ledger.

Deciding which system owns which field

Most of the difficulty in this class of project is not technical, it is a question of authority over data. Sage 200 should own stock quantities, cost, price bands and the customer account record. The storefront should own the consumer-facing catalogue content, the order capture and the payment record. Once those lines are drawn, each field flows in one direction only and the whole category of two-way sync conflicts disappears. Where a field genuinely must be editable in both places — delivery addresses are the usual candidate — write down which one wins and make the losing system read-only in the user interface if the platform allows it.

Stock is the flow that gets the most attention from operations. The figure to publish is normally free stock — quantity in stock less quantity allocated — with a buffer applied per item or per category to absorb the latency between the last push and the next order. A full catalogue push on a short interval will exhaust API call allowances on any hosted storefront; send deltas, with a full reconciliation once a day at a quiet hour to catch anything the delta logic missed. If the catalogue is large, batch the updates and respect the platform's rate limiting rather than discovering it in production.

Price requires a decision about whether the storefront shows VAT-inclusive or exclusive figures, and which Sage price band applies to web customers. Rounding differences of a penny between the ERP calculation and the storefront calculation are a common source of orders that fail validation on arrival. Agree a tolerance, document it, and make the integration flag a breach rather than silently accepting whichever number arrived last.

Set a latency expectation in writing and put it in the acceptance criteria. "Stock levels in the storefront reflect Sage within fifteen minutes under normal conditions, and within two hours of the bridge returning from an outage" is a statement a finance manager can hold you to and an engineer can test. "Real-time" is not, and it invites a dispute at go-live.

Proving it works, and planning for the day the server moves

Acceptance testing for this kind of work should run against a restored copy of the live Sage database in a separate company or a separate SQL instance, never against production. A restored copy gives you real customer accounts, real item codes and real price bands, which is where the edge cases live. Test the obvious path once, then spend the time on the refusals: an account on hold, a zero-stock item, a foreign address, a discontinued product, a payment taken but an item removed afterwards. Record each test and its expected outcome in the acceptance document so that sign-off is a matter of fact rather than opinion.

For cutover, parallel running is worth the fortnight it costs. The integration posts into a test company while the staff continue to key into the live one, and the two sets of orders are compared daily. Differences surface quickly and are almost always a mapping assumption rather than a defect in the bridge. Once two consecutive days match without correction, switch the target to the live company and stop the manual keying. Keep the manual process documented and available for a month afterwards, because the first time the office loses its internet connection somebody will want it.

The final consideration is that the old server will not be there forever. Hardware fails, Windows versions fall out of support, and sooner or later the client will move to a hosted Sage 200 Professional, to Sage Intacct, or to something else entirely. Build the bridge with an internal canonical order model so that the storefront-facing half and the ERP-facing half are separable. When the ERP changes, you replace one adapter and retest, rather than rewriting the lot. That design choice costs very little during the build and saves a five-figure sum later.

On delivery, the client should receive the source code, the field mapping document, a runbook covering how to restart the service, how to replay a parked order and how to rotate credentials, and the monitoring configuration. We agree the scope in writing before any code is written — discovery and data mapping, then a fixed scope and estimate, then build and acceptance testing, then go-live and handover — because projects of this type are where unwritten assumptions do the most damage. If it helps to talk through a specific estate before committing to anything, we are on 01303 883111 or hello@api-integrations.co.uk.

Key points

  • Establish the exact Sage 200 version, edition and licence position during discovery: whether a documented web API exists at all determines the entire architecture and the estimate.
  • Use an outbound-only bridge service on the LAN with a durable cloud-side queue, so no inbound firewall rule is needed and server reboots or outages produce a backlog rather than lost orders.
  • Read from the Sage database directly if you must, but write through the business object layer, park refusals for human review, and keep an abstraction between storefront and ERP so a future migration replaces one adapter rather than the whole integration.