Legacy System Integration Challenges: No-API Routes Ranked by Risk
Legacy system integration challenges with no API: ten routes ranked by risk, from database reads and CDC to IBM i and email, with how to detect each failure.
Luka Abramovic21 min read
The costliest legacy system integration challenges come from integrations that change the system they read from. Switch on SQL Server change data capture (CDC) for a vendor's table, and any vendor job that empties that table with TRUNCATE TABLE starts failing with error 4711. Let CDC's capture job stop, and the transaction log stops truncating, even in the simple recovery model, so it grows until the log or its disk is full (Microsoft Learn). A test that only checks whether changes arrive passes either way.
Below are the ways into a system with no API, ranked by how much each can harm it or silently lose data, with what goes wrong, how to detect it and what to ask the vendor. SQL Server and IBM i (AS/400) get the most detail. Product facts are as of September 2026.
The routes, ranked by risk
Our ranking, from least risk to most. In the middle column, anything other than "nothing" needs the vendor's agreement before anyone builds.
| Route | What it changes on the legacy side | How it fails quietly |
|---|---|---|
| 1. The vendor's scheduled export | Nothing | A partial, late or repeated file. Deleted records simply stop appearing |
| 2. Queries against a restored copy | Nothing on production | The copy is older than everyone assumes |
| 3. Read-only queries against the live database | Load and locks | NOLOCK reads skip or double-count rows |
| 4. Email in or out | Nothing, but it depends on your mail platform | Throttled sends, or a mailbox reader that stops when Microsoft retires EWS |
| 5. Change tracking | Tracking metadata on each table | Changes are cleaned up before you read them. Vendor scripts that drop a primary key fail |
| 6. Change data capture | Change tables, two SQL Server Agent jobs and a hold on log truncation | The log grows, truncates fail and new columns are ignored |
| 7. Calling the application's own programs (IBM i) | Only what the application's own program writes | The program runs with a different library list and opens other files |
| 8. Screen automation (RPA) | A user account and a machine | The bot types into the wrong field and carries on |
| 9. Writing to the vendor's tables (avoid) | Rows the application didn't create | Records that skip validation, totals and audit rows |
| 10. Triggers on the vendor's tables (avoid) | Your code runs inside their transactions | Your error rolls back their save |
Before you connect: what the vendor allows
Reading a vendor's database is technically easy and contractually uncertain. Three documents decide it:
- The license agreement, including the database engine's: if the vendor supplied the SQL Server or Oracle license, ask whether it covers other programs connecting.
- The support terms. Search for "database", "third party", "direct access", "modification" and "unsupported". Read exclusions knowing that read-only queries change nothing in the database, while change tracking, CDC and triggers add objects to it.
- The upgrade notes: do upgrades drop and recreate tables, taking anything you attached with them?
Then ask in writing, from whoever owns the vendor relationship:
Subject: Reading data from [product] outside the application
Hello [name],
We want to use data from [product] in [other system] and need your answers in writing:
1. Do our license and support agreement permit read-only access to the
database by another program, including under a database license you supplied?
2. Is there a supported way to read the data (views, a reporting database,
a scheduled export) that we should use instead of the base tables?
3. If our DBA enables SQL Server change tracking or change data capture on these
tables: [list], does that affect support? Do your upgrade scripts drop and
recreate tables or primary keys, use TRUNCATE TABLE, or switch partitions?
4. Which tables hold [customers / orders / invoices]? Which column shows when a
row last changed, and what sets it? Are rows ever physically deleted?
5. Is there a supported way to create or update [record] from outside: an
import, a program or procedure we can call, or a queue the application reads?
6. Which jobs run inside the database, and when? How much notice do you
give of schema changes before an upgrade?
Thank you,
[name, role, customer account number]Questions 3 and 5 decide the most. A "yes" to TRUNCATE TABLE rules out CDC on that table. A "yes" to rebuilding primary keys means switching change tracking or CDC off before each upgrade and reloading the table afterwards.
Whatever the answers, our default is to put views, state tables and logs in a separate database on the same server, where upgrades can't drop them and the vendor's support team never finds objects nobody explained.
Reading the vendor's database without harming it
Run the inventory first
These read-only queries show which routes a SQL Server allows. A DBA runs queries 4 and 5 in the vendor's database. Query 2 needs VIEW SERVER STATE (SQL Server 2019 and earlier) or VIEW SERVER SECURITY STATE (2022 and later).
-- 1. Edition and version: is CDC available at all?
SELECT SERVERPROPERTY('Edition') AS edition,
SERVERPROPERTY('ProductVersion') AS version;
-- 2. Is SQL Server Agent running? CDC's jobs need it
SELECT servicename, status_desc FROM sys.dm_server_services;
-- 3. Features in use, and what each log is waiting on
SELECT d.name, d.is_cdc_enabled, d.log_reuse_wait_desc,
d.snapshot_isolation_state_desc, d.is_read_committed_snapshot_on,
ct.retention_period, ct.retention_period_units_desc
FROM sys.databases AS d
LEFT JOIN sys.change_tracking_databases AS ct ON ct.database_id = d.database_id;
-- 4. In the vendor database: tables with no primary key
SELECT s.name AS schema_name, t.name AS table_name
FROM sys.tables AS t
JOIN sys.schemas AS s ON s.schema_id = t.schema_id
WHERE OBJECTPROPERTY(t.object_id, 'TableHasPrimaryKey') = 0;
-- 5. In the vendor database: tables with a rowversion column
SELECT OBJECT_SCHEMA_NAME(c.object_id) AS schema_name,
OBJECT_NAME(c.object_id) AS table_name, c.name AS column_name
FROM sys.columns AS c
JOIN sys.types AS ty ON ty.user_type_id = c.user_type_id
JOIN sys.tables AS t ON t.object_id = c.object_id
WHERE ty.name IN ('timestamp', 'rowversion');- Query 1: CDC needs Standard or Enterprise. SQL Server 2022's Web and Express editions don't include it, and Express has no SQL Server Agent. Express caps each database at 10 GB in SQL Server 2022 and 50 GB in SQL Server 2025 (2022 editions, 2025 editions). Change tracking works in every edition, but its internal tables count against the Express cap.
- Query 4: change tracking can't track a table without a primary key. CDC can, but without net changes, which need a primary key or unique index (sys.sp_cdc_enable_table).
Don't read with NOLOCK
WITH (NOLOCK) is the usual advice for reading a busy database without blocking it. Microsoft documents the cost: besides reading changes that are later rolled back, a NOLOCK scan can miss rows while another transaction splits a page. Even the default READ COMMITTED level can read a row twice, or miss it, when another transaction changes the row's index key mid-scan (locking and row versioning guide). For an integration that counts or sums, that means wrong totals with no error.
If query 3 shows snapshot isolation allowed, read inside SET TRANSACTION ISOLATION LEVEL SNAPSHOT: a consistent picture, without blocking the vendor's writes. Turning it on changes the vendor's database: row versioning adds up to 14 bytes to each row as it is modified, 14% on a 100-byte row. Ask first. Until then, keep reads short, split by date or ID range, and out of busy windows such as month-end posting.
A readable secondary replica would avoid all this, but Standard edition's basic availability groups allow no reads on the secondary (basic availability groups). On Standard, the alternative is a nightly restored copy on another server, a day old by design, so stamp every extract with the newest record date it contains.
Change tracking vs change data capture
Both let you read only what changed, and both record deletions, which polling a last-modified column can't see. They differ in what they record and what they cost the vendor's database.
| Question | Change tracking | Change data capture |
|---|---|---|
| Editions | All, including Express | Standard and Enterprise. Not Express or Web |
| Needs SQL Server Agent | No | Yes, for the capture and cleanup jobs |
| What it records | The primary key and the operation. Current values are read from the table, so intermediate values are lost | Every insert, delete and update, with values. Each update writes two rows: before and after |
| Default retention | 2 days | 3 days (4,320 minutes). The cleanup job runs daily at 2 a.m. |
| Table requirement | A primary key | None. Net changes need a primary key or unique index |
| How it is switched on | ALTER DATABASE, then ALTER TABLE for each table | sys.sp_cdc_enable_db (needs sysadmin), then sys.sp_cdc_enable_table for each table (db_owner) |
| What it blocks in the vendor's database | Dropping the table's primary key and switching partitions. TRUNCATE TABLE still works but forces every reader into a full reload | TRUNCATE TABLE fails with error 4711. The primary key can't change until CDC is disabled on the table |
| Schema changes | A dropped column can still appear in column tracking. Data type changes aren't tracked | Columns added later are ignored until you create a second capture instance (two per table at most) |
| How changes are captured | Synchronously, as part of each insert, update or delete | Asynchronously, by a job reading the transaction log. The log can't truncate past changes the job hasn't read, in any recovery model |
| How you know you missed changes | CHANGE_TRACKING_MIN_VALID_VERSION is higher than your last version | Your start point is below sys.fn_cdc_get_min_lsn, and the query functions fail |
Sources: Manage change tracking, enable and disable CDC, error 4711 and the CDC overview. One more CDC trap: it doesn't work properly when the Database Engine or Agent service runs as NETWORK SERVICE (error 22832).
Our default is change tracking whenever each changed record's current state is enough, as in most syncs. Choose CDC when you need every state a record passed through (each status an order held, for time-in-status reporting or audit) or the table has no primary key. CDC-based streaming tools such as Debezium's SQL Server connector need a running Agent too (Debezium documentation), so the CDC column applies to them.
Size retention for your longest outage, not your sync interval
An illustrative example. The integration's server fails at 18:00 on the Friday before a holiday weekend and is restarted at 09:00 on Tuesday: 87 hours. Change tracking's default keeps 48 hours and CDC's keeps 72, so both have cleaned up Friday evening's changes before the integration asks. Either way, you reload every table in full.
Our default is seven days: CHANGE_RETENTION = 7 DAYS, or 10,080 minutes for CDC. The cost is storage. A table with 5,000 inserts and 20,000 updates a day adds 45,000 CDC rows a day, because each update writes two: 315,000 rows at seven days, against 135,000 at three. A CDC retention change takes effect only after the cleanup job restarts (sys.sp_cdc_change_job):
EXECUTE sys.sp_cdc_change_job @job_type = N'cleanup', @retention = 10080;
EXECUTE sys.sp_cdc_stop_job @job_type = N'cleanup';
EXECUTE sys.sp_cdc_start_job @job_type = N'cleanup';Read change tracking so a cleanup can't fool you
Microsoft recommends reading changes inside a snapshot transaction, because the cleanup can otherwise run between your version check and your read and return incomplete results (Work with change tracking). The integration's state lives in its own database and is read before the transaction starts:
-- Once, by the DBA, after the vendor agrees:
ALTER DATABASE VendorDb SET ALLOW_SNAPSHOT_ISOLATION ON;
ALTER DATABASE VendorDb SET CHANGE_TRACKING = ON (CHANGE_RETENTION = 7 DAYS, AUTO_CLEANUP = ON);
ALTER TABLE dbo.OrderHeader ENABLE CHANGE_TRACKING;
-- Each run, in VendorDb. @last is the version saved by the previous run
-- (after the first full load, the CHANGE_TRACKING_CURRENT_VERSION() taken before it):
DECLARE @last bigint = (SELECT last_version FROM IntegrationDb.dbo.SyncState
WHERE table_name = 'OrderHeader');
DECLARE @next bigint;
SET XACT_ABORT ON;
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
BEGIN TRANSACTION;
IF @last < CHANGE_TRACKING_MIN_VALID_VERSION(OBJECT_ID('dbo.OrderHeader'))
THROW 50001, 'Changes were cleaned up before we read them. Full reload needed.', 1;
SET @next = CHANGE_TRACKING_CURRENT_VERSION();
SELECT ct.OrderId, ct.SYS_CHANGE_OPERATION, o.Status, o.TotalAmount
FROM CHANGETABLE(CHANGES dbo.OrderHeader, @last) AS ct
LEFT JOIN dbo.OrderHeader AS o ON o.OrderId = ct.OrderId;
COMMIT TRANSACTION;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- Only after the rows are safely stored downstream:
UPDATE IntegrationDb.dbo.SyncState SET last_version = @next WHERE table_name = 'OrderHeader';Deleted orders come back with SYS_CHANGE_OPERATION = 'D' and empty columns. Change tracking records every update, even one that sets a column to its current value, so compare values before sending anything downstream.
Watch CDC where it breaks
Four read-only checks for the DBA or a scheduled job:
-- REPLICATION here, on a database with no replication, means the capture job is behind or stopped
SELECT name, log_reuse_wait_desc FROM sys.databases WHERE is_cdc_enabled = 1;
-- Recent capture errors
SELECT TOP (20) entry_time, error_number, error_message
FROM sys.dm_cdc_errors ORDER BY entry_time DESC;
-- Capture latency in seconds (row 0 holds the last nonzero value recorded)
SELECT latency FROM sys.dm_cdc_log_scan_sessions WHERE session_id = 0;
-- Schema changes to a captured table: new columns here are not being captured
EXECUTE sys.sp_cdc_get_ddl_history @capture_instance = N'dbo_OrderHeader';Microsoft's full-log troubleshooting script also blames replication or CDC agents for a REPLICATION wait (error 9002 guide). Put the first query on an alert.
When you can't enable either: rowversion polling
If query 5 found a rowversion column (older scripts call it timestamp), you can find changed rows without changing the vendor's database. SQL Server stamps a per-database counter on every row inserted or updated in that table (rowversion). Two traps:
- In-flight transactions. Save
@@DBTSas your high-water mark and a transaction that took a lower number but hadn't committed when you read is skipped for good. Microsoft providesMIN_ACTIVE_ROWVERSION()for exactly this case (MIN_ACTIVE_ROWVERSION). - Updates that change nothing still increment it. A vendor job that touches every row overnight makes the whole table look changed.
DECLARE @from binary(8) = (SELECT last_rowversion FROM IntegrationDb.dbo.SyncState
WHERE table_name = 'OrderHeader'); -- 0x0000000000000000 on the first run
DECLARE @to binary(8) = MIN_ACTIVE_ROWVERSION();
SELECT OrderId, Status, TotalAmount
FROM dbo.OrderHeader
WHERE RowVer >= @from AND RowVer < @to;
-- Only after the rows are safely stored downstream:
UPDATE IntegrationDb.dbo.SyncState SET last_rowversion = @to WHERE table_name = 'OrderHeader';Deletions stay invisible to this method, so pair it with a weekly key comparison like the one in the exports section.
Triggers: why not
A trigger looks like the neat answer: every change to the vendor's table writes a row to your queue. But a trigger and the statement that fires it are treated as a single transaction (DML triggers). When your trigger fails (a full queue table, a missing permission, a linked server that is down), the vendor's own save rolls back, and the user sees an error nobody at the vendor can reproduce. Three more reasons:
- One firing, many rows. A statement changing 500 rows fires the trigger once, with 500 rows in
inserted(multirow triggers). A trigger written for one row processes one and drops the rest. - Identity values. If your trigger inserts into a table with an IDENTITY column, a vendor program that reads
@@IDENTITYafter saving an order gets your queue row's number instead of the new order's (@@IDENTITY). - Upgrades. An upgrade script that drops and recreates the table silently takes the trigger with it. One that rewrites every row floods your queue.
If the vendor refuses change tracking and CDC but agrees to a trigger in writing, use the least-bad shape: set-based, writing only keys to a queue table with no IDENTITY column, in the same database, calling nothing outside. A queue in another database would roll back the vendor's save whenever that database is unavailable:
CREATE TRIGGER dbo.trg_OrderHeader_queue ON dbo.OrderHeader
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO dbo.IntegrationQueue (table_name, key_value, queued_at)
SELECT 'OrderHeader', k.OrderId, SYSUTCDATETIME()
FROM (SELECT OrderId FROM inserted
UNION
SELECT OrderId FROM deleted) AS k;
END;The integration reads the queue, then fetches current values. Detect: after every vendor upgrade, list the database's triggers with the query in our legacy system examples and confirm yours is still there and enabled.
Vendor exports: what the file leaves out
The vendor's own export is the lowest-risk route, because the vendor maintains it. Defenses for the file itself (temporary names, control totals, hashes, an alert when nothing arrives) are in our guide to legacy system challenges. The traps below are in what the file contains.
- Full or changes-only. Unless a changes-only export carries a delete flag, a deleted record simply stops appearing and you keep it forever. With full exports, find deletions by comparing keys, but check the row count first, or a half-written export deletes half your records. Our default: apply no deletions when today's file has more than 5% fewer rows than yesterday's, and alert instead.
- "Changed since" by which clock. A changes-only export usually selects on a last-modified column the application sets. A fix made directly in the database, a bulk correction by the vendor's support team or a back-dated entry may not update it, and the row never appears. Ask what sets it.
- Consistency. An export that reads orders, then order lines, while people work can include lines for an order created between the two reads, with no header in the file. Check for orphans before loading.
- Timing. An export scheduled inside the hour when clocks change can run twice or not at all that night, so schedule outside it and carry timestamps in UTC.
-- Deletions: keys in yesterday's full export that are missing today.
-- Apply them only if today's row count is at least 95% of yesterday's.
SELECT customer_no FROM stage.customers_yesterday
EXCEPT
SELECT customer_no FROM stage.customers_today;
-- Orphans: order lines whose order header is not in the same export
SELECT l.order_no, COUNT(*) AS line_count
FROM stage.order_lines AS l
LEFT JOIN stage.orders AS o ON o.order_no = l.order_no
WHERE o.order_no IS NULL
GROUP BY l.order_no;Ask the vendor about all four points before you design the load.
IBM i (AS/400): ODBC, JDBC and calling the program
Db2 for i is a full SQL database, reachable through IBM's own drivers: the IBM i Access ODBC Driver, and JTOpen, the open-source IBM Toolbox for Java with its JDBC driver (JTOpen on GitHub). Connecting is easy. The defaults are where the data goes wrong.
Ask the IBM i administrator first
- Which library holds production data, and which hold copies (test, last year, a backup)? The same file names often exist in several, and your connection settings decide which you read.
- Which job description, and so which library list, do the application's users run with?
- Does any file you need have more than one member? A member is a separate set of rows inside one file (
/QSYS.LIB/ORDLIB.LIB/ORDHIST.FILE/Y2025.MBR). JTOpen's record-level access reads the first member when you don't name one (AS400File). Test that your query returns what the application shows for a period stored in a later member. - Which character fields are tagged CCSID 65535 (binary), and do any hold real binary data? How are dates stored: DATE columns, or numbers in a format someone must tell you? Does the application use commitment control?
Connection settings that change the data you get
These are JTOpen's JDBC defaults as of September 2026 (JDBC properties). The ODBC driver has over 70 connection options of its own (IBM's ODBC guide), including naming, default libraries and commit mode, so check those there.
| Property | Default | What goes wrong | Set it to |
|---|---|---|---|
translate binary | false | Text fields tagged CCSID 65535 arrive as bytes | true, unless some of those fields hold real binary data |
trim char fields | false | CHAR values arrive padded, so C1001 elsewhere doesn't match C1001 plus five spaces | true, or trim before matching |
transaction isolation | read uncommitted. With auto-commit on (also the default), the driver runs under *NONE unless true autocommit is set | You read rows another job hasn't committed and may roll back | read committed plus true autocommit=true, if the application uses commitment control |
naming | sql | With no default schema or libraries set, unqualified names resolve to a schema named after your user profile | system, with the application's library list |
libraries | The server job's library list | Under system naming, you read whichever library comes first | The production list from question 1 |
date format | The server job's | Date literals in your SQL are read in the job's format | iso |
jdbc:as400://ibmi.example.local;naming=system;libraries=*LIBL,ORDLIB;translate binary=true;trim char fields=true;transaction isolation=read committed;true autocommit=true;date format=isoWith *LIBL first, the libraries after it are added to the server job's library list rather than replacing it.
Writes: call the program, not the file
The application's rules live in its RPG or COBOL programs, not its files, so inserting rows into a physical file skips them. If the screen that creates an order calls a program, ask whether it can be called without the screen. JTOpen's ProgramCall runs it (ProgramCall), and its documentation notes the detail that matters: in a host server job, the library list is the initial one from the job description in the calling user profile. Give the integration's profile the job description the application's users have, or the program can open another library's files.
If the application already reads a data queue for incoming work, JTOpen's DataQueue class can write to it. Ask which programs are safe to call outside their screens, because some rely on setup the screen does first. Detect: read each record back and compare every field you sent.
Email as the integration
Old systems often already email invoices, statements and reports, so email is a tempting integration in both directions. Microsoft 365 is changing underneath both.
When the legacy system sends through Microsoft 365
Many older applications send through SMTP AUTH, signing in to a mailbox with a username and password. As of September 2026, Microsoft documents these per-mailbox limits for that route (SMTP submission throttling):
- Three concurrent connections. More are refused with
432 4.3.2 Concurrent connections limit exceeded. - 30 messages per minute. The excess is delayed into the following minutes.
- 10,000 recipients per day (Exchange Online limits). After that, sends fail with
554 5.2.0 ... SubmissionQuotaExceededException. - Every message sent is saved in the mailbox's Sent Items, so an application mailbox grows toward its storage limit. Our default is a retention policy that empties Sent Items.
An illustrative month end: 2,400 invoices and 3,000 statements, each to two recipients (the customer and the account manager). That is 10,800 recipients, so the last 800, about 400 messages, fail with 554 5.2.0, and at 30 a minute the run takes at least 180 minutes. The limits apply per mailbox, so our default is one mailbox per sending job: split across two, invoices (4,800 recipients) and statements (6,000) both fit, and the run takes 100 minutes. A relay connector that authenticates your server by public IP address or TLS certificate is another route (Exchange Online relay options).
The stored password has a deadline too. At the end of December 2026, Microsoft turns off Basic authentication for SMTP AUTH by default in existing tenants. Admins can turn it back on, but only until a final removal date that Microsoft will announce in the second half of 2027 (Exchange Team), so plan OAuth or a relay now. Our list of software end-of-support dates has the commands that list the mailboxes with SMTP AUTH switched on.
Ask: does the application authenticate with a password, OAuth or a relay? Does it retry on a 4xx reply, and log each message? Detect: compare messages sent each day with documents generated.
When your integration reads a mailbox
If your integration collects the legacy system's emailed reports from a mailbox, check how it connects. Microsoft is retiring Exchange Web Services (EWS) from Exchange Online. From October 2026 it is switched off tenant by tenant wherever an admin hasn't explicitly enabled it for named apps, and it is retired completely in April 2027 (Microsoft Learn). Microsoft 365 admin center > Reports > Usage > Exchange, then the EWS usage tab, lists each application ID still calling it, with its SOAP actions, call volume and last activity date (EWS usage report). Move those readers to Microsoft Graph.
Our default: a shared mailbox, with each processed message moved to a Processed folder and each failure to an Exceptions folder, so the inbox holds only unread work. Detect: alert when the report hasn't arrived by its expected time.
Screen automation, last
When a write must go through the screens and there is no import, callable program or queue, a bot that drives the screen is what remains. It changes nothing in the database, which is why it ranks above direct writes and triggers, but it breaks on a changed window title or a moved field. Our guide to workflow automation vs RPA covers selectors, unattended bots and silent failures. Two rules for legacy systems: every bot write gets a read-back through a read route above, and on IBM i green screens, look for the program behind the screen first.
What to check once it runs
The minimum we would schedule. Thresholds are our defaults.
| Route | Check | Alert when |
|---|---|---|
| Every route | Daily count and one amount total, against the legacy system's own report. Program calls and bot writes also get a read-back | Any difference nobody has explained by the next day |
| Change tracking | Hours since the last successful run, against the retention period | More than half the retention period has passed |
| CDC | log_reuse_wait_desc, sys.dm_cdc_errors and latency | REPLICATION persists, any new error appears, or latency passes your freshness target |
| CDC | sys.sp_cdc_get_ddl_history for each capture instance | Any new row: the vendor changed a captured table |
| Rowversion polling | Rows returned per run | A run returns several times its usual count |
| Exports, restored copies and email | Arrival by the deadline, and the newest record date | Nothing by the deadline, a stale copy, or 432 and 554 replies in the sending log |
| After every vendor upgrade | Rerun the inventory queries. Check is_tracked_by_cdc in sys.tables, sys.change_tracking_tables and your triggers | Anything you enabled is gone or disabled |
If you need data out of a system with no API and aren't sure which of these routes it allows, bring the vendor's answers to the email above (or the lack of them) and the output of the inventory queries. We'd start with the edition, the Agent status and whether the tables you need have primary keys, because those three decide which routes exist. Our work on improving and rebuilding existing systems starts from that assessment.