Replacing an operations system in one cutover is rare. For months (or years) an “old” database — often SQL Server, grown Access, or an AS/400 with views — coexists with a new ERP or CRM. If sync is a nightly CSV “and then we see”, you have two truths and nobody knows yesterday’s.

The problem is not “make an API”. It is who is master for which entity, what happens when a row fails, and how you resume without duplicates.

Data contract first, cable second

List objects: customers, items, orders, payments. For each: master system, required fields, frequency (event vs batch), conflict rule (timestamp, priority, human review).

Without that table every middleware becomes a graveyard of ifs.

Patterns, from simple to robust

Idempotent batch. Nightly or hourly job: reads a watermark (updated_at or an outbox), upserts on the target with a stable key (VAT id, SKU, mapped legacy id). Run it twice, same result. Fine for master data that is not real-time.

Outbox + queue. The legacy (or a trigger/CDC) writes events to an outbox table; a worker publishes to a queue; the consumer updates the ERP. Use it if an order must appear in minutes and you want retries without lost messages.

Synchronous APIs only at the edge. An operator creates the order in the new portal; the service calls the ERP and, on failure, does not fake success. Do not use sync HTTP to dump 40,000 items: timeouts and double inserts.

CDC (change data capture). If the vendor allows it (SQL log, Debezium, ERP tooling). Ops-heavy. Useful when you cannot touch legacy code.

Anti-corruption layer. Your service speaks the new ERP’s language (aggregates, UUIDs) and translates the old model (denormalised tables, 6-character codes). The ERP should not have to “understand” Access.

Keys and identity

The original sin is using the customer name as a key. You need: id in the legacy, id in the new system, and a persistent mapping table. VAT ids and SKUs help but are not 100% unique (branches, duplicate items). Dedup is a project, not a SQL one-liner.

Monday-morning failures

  • Jobs that “delete and reimport”: an id changes, history breaks.
  • No dead-letter: order 4817 fails silently on a VAT field.
  • Timezones and naive datetimes.
  • Bidirectional sync with no master: two operators edit the same customer, the last job wins.
  • Excel as the “official integration”: no version, no audit.

A realistic sequence

  1. Read-only: the new system reads the legacy, does not write.
  2. Master data via idempotent batch.
  3. Documents (orders) via outbox, one direction.
  4. Switch off the old piece by piece when reports match for N days.

DPH connects CRMs, ERPs and existing software with APIs, not manual exports. For a design on your case, book a technical session: DB engines, volumes, who is master, how stale “last night” vs “now” may be.