Before vs After: The Real Benefits of Modernising a Legacy Finance Database

1. Query and Report Performance

The most dramatic improvement was in reporting. Progress OpenEdge processes queries via its file-based access engine — it does not have a cost-based query optimiser, execution plans, or index covering strategies in the SQL Server sense. Every multi-table query becomes a nested loop over progress files. SQL Server's optimiser, with properly designed indexes, changes the arithmetic completely.

The write path improvement is modest — 1.2×. This is expected. Progress OpenEdge was always fast on single-record writes; its file format is optimised for that workload. SQL Server adds transaction log overhead and FK constraint checks (once they were added in Phase D) that OpenEdge did not have. The read-heavy workloads — reporting, search, history lookups — are where SQL Server's indexing strategy delivers transformative gains.

The Two Indexes That Drove Most of the Gain

The portfolio summary query joins CONT_HDR to CONT_SCHED and CONT_PAYMNT. In the initial SQL Server schema (Part 1) these joins used the non-clustered indexes defined there. Adding two covering indexes reduced the portfolio summary from 3 min 28 sec to 1 min 42 sec — a further 2× on top of the already 67× gain over OpenEdge.

-- Covering index 1: portfolio summary — active contracts with financial totals
-- Eliminates key lookups on CONT_HDR for the most common reporting columns
CREATE INDEX IX_CONT_HDR_PORTFOLIO
ON dbo.CONT_HDR (CH_STATUS, CH_MATURITY_DT)
INCLUDE (CH_CONT_REF, CH_PROD_CODE, CH_CUST_ID, CH_FINANCE_AMT,
         CH_OUTSTANDING, CH_TOTAL_PAID, CH_ARREARS_BAND, CH_ARREARS_AMT,
         CH_FCA_REGULATED, CH_BRANCH_CODE, CH_COUNTRY_CODE);

-- Covering index 2: schedule payment status — drives arrears recalculation
-- Filters to pending/overdue only; excluded statuses do not appear in index
CREATE INDEX IX_CONT_SCHED_ARREARS
ON dbo.CONT_SCHED (CS_STATUS, CS_DUE_DATE)
INCLUDE (CS_CONT_ID, CS_TOTAL_AMT, CS_PAID_AMT)
WHERE CS_STATUS IN ('PE','OV');  -- filtered index — paid rows excluded

2. Concurrency and Lock Contention

On Progress OpenEdge, report queries and interactive users competed for the same file locks. A running report held a share lock on the entire contract file for its duration. A user trying to update a contract during the monthly portfolio run waited for the report to finish — sometimes up to 4 hours. This was accepted as "just how the system works" for 20 years.

SQL Server with Read Committed Snapshot Isolation (RCSI) eliminated this entirely. Readers never block writers; writers never block readers.

-- Enable RCSI — done once on the DemoBankDB database
-- Requires a brief single-user window (seconds, not minutes)
ALTER DATABASE DemoBankDB SET READ_COMMITTED_SNAPSHOT ON;

-- Verify
SELECT name, is_read_committed_snapshot_on
FROM   sys.databases
WHERE  name = 'DemoBankDB';
-- Expected: is_read_committed_snapshot_on = 1

One nuance: Progress handled file deadlocks silently via automatic retry at the application layer — users never saw them, but 2–3 per day were logged. In SQL Server, deadlocks surface as explicit errors that the application must handle. This required a one-week application update to add deadlock retry logic to three high-contention stored procedures in Month 2. After that, the 0.1 average deadlock per day was inconsequential.

3. Developer Productivity

This is the hardest category to quantify, but DemoBank Finance tracked four concrete metrics across the six months before and six months after cutover.

The biggest shift was democratisation of database access. On Progress, only two developers had deep enough 4GL knowledge to write database queries — a single point of failure that had caused a 3-week backlog of "can you pull this data for me?" requests. After migration, any developer with basic T-SQL skills could write their own queries in SSMS. The data request backlog was gone within a month of go-live.

The T-SQL Skills Investment

The nine developers who gained direct database access did not arrive with T-SQL skills. Six weeks of structured T-SQL training was required before go-live — covering SELECT internals, JOINs, CTEs, window functions, and execution plan reading. This was the one cost the project team underestimated most severely: the original plan was two weeks of training. The actual need was six. Budget accordingly.

-- The query that took a Progress developer 4+ hours to write in 4GL
-- and a SQL Server developer 8 minutes to write in T-SQL:
-- "For each arrears band, show the number of contracts, total outstanding,
--  average months in arrears, and the top 3 introducers by portfolio value."

WITH ArrearsSummary AS (
    SELECT
        RTRIM(CH_ARREARS_BAND)              AS ArrearsBand,
        CH_INTRODUCER,
        COUNT(*)                            AS ContractCount,
        SUM(CH_OUTSTANDING)                 AS TotalOutstanding,
        AVG(DATEDIFF(MONTH,
            CONVERT(DATE, CAST(CH_START_DATE AS CHAR(8)), 112),
            GETDATE()))                     AS AvgMonthsActive
    FROM dbo.CONT_HDR
    WHERE CH_STATUS = 'AR'
    GROUP BY RTRIM(CH_ARREARS_BAND), CH_INTRODUCER
),
RankedIntroducers AS (
    SELECT
        ArrearsBand,
        CH_INTRODUCER,
        TotalOutstanding,
        ContractCount,
        AvgMonthsActive,
        RANK() OVER (
            PARTITION BY ArrearsBand
            ORDER BY TotalOutstanding DESC
        ) AS IntroducerRank
    FROM ArrearsSummary
)
SELECT
    ArrearsBand,
    CH_INTRODUCER               AS TopIntroducer,
    ContractCount,
    TotalOutstanding,
    AvgMonthsActive,
    IntroducerRank
FROM RankedIntroducers
WHERE IntroducerRank <= 3
ORDER BY ArrearsBand, IntroducerRank;

4. FCA Compliance and Audit Posture

DemoBank Finance had received two FCA audit findings related to data integrity in the three years before migration:

• Finding 1 (2021): "The firm cannot demonstrate that referential integrity between contract and customer records is maintained at the database layer. Integrity is enforced only in application code, which is not independently verifiable."
• Finding 2 (2022): "The firm's regulated contract data cannot be produced within the same business day on request. The FCA expects regulated firms to be able to respond to data requests within 4 hours."

Both findings were closed after the SQL Server migration.

The Temporal Table Addition (Phase 2 Modernisation)

Four months after go-live, as part of Phase 2 modernisation, CONT_HDR and CUST_MAST were converted to system-versioned temporal tables. This gave the FCA auditors a complete, tamper-evident history of every field change without relying on the application-written SYS_AUDIT_LOG.

-- Phase 2: convert CONT_HDR to a system-versioned temporal table
-- (requires Phase 2 modernisation — CHAR columns are acceptable in temporal tables)

-- Step 1: add the period columns
ALTER TABLE dbo.CONT_HDR
    ADD ValidFrom  DATETIME2 GENERATED ALWAYS AS ROW START
                   CONSTRAINT DF_CONT_HDR_ValidFrom  DEFAULT SYSUTCDATETIME() NOT NULL,
        ValidTo    DATETIME2 GENERATED ALWAYS AS ROW END
                   CONSTRAINT DF_CONT_HDR_ValidTo    DEFAULT CONVERT(DATETIME2,'9999-12-31 23:59:59.9999999') NOT NULL,
        PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo);

-- Step 2: enable system versioning
ALTER TABLE dbo.CONT_HDR
    SET (SYSTEM_VERSIONING = ON (
        HISTORY_TABLE = dbo.CONT_HDR_History,
        DATA_CONSISTENCY_CHECK = ON
    ));

-- Query: what was a contract's status on a specific date?
SELECT CH_CONT_ID, CH_STATUS, CH_OUTSTANDING, CH_ARREARS_AMT, ValidFrom, ValidTo
FROM   dbo.CONT_HDR
FOR SYSTEM_TIME AS OF '2024-03-15 12:00:00'
WHERE  CH_CONT_ID = 3001;

-- Query: full status change history for a contract
SELECT CH_CONT_ID, CH_STATUS, CH_OUTSTANDING, ValidFrom, ValidTo
FROM   dbo.CONT_HDR
FOR SYSTEM_TIME ALL
WHERE  CH_CONT_ID = 3001
ORDER  BY ValidFrom;

5. Infrastructure and Licensing Costs

Project cost: approximately £680,000 all-in (consultancy, internal staff time, licensing, infrastructure, training, parallel operation period overhead). At £152,000 annual saving, payback period: 4.5 years on cost alone.

Including the developer productivity gain (estimated conservatively at £80,000/year in reduced staff time on data requests, report writing, and data fix investigations) and reduced FCA audit risk (hard to quantify, but a serious finding carries a potential fine): the adjusted payback period is closer to 2.8 years.

6. What Did Not Improve (Honest Accounting)

No migration delivers on every promise. Three areas where the expected benefit did not materialise:

The Schema Is Still Legacy in Shape

The DemoBank Finance SQL Server schema is exactly the schema described in Part 1 of this series. CHAR columns, INT dates, no VARCHAR, no IDENTITY, no computed columns. The physical storage is modern; the logical design is still 1990s COBOL. Phase 2 modernisation — converting CHAR to VARCHAR, INT dates to DATE, CHAR(1) flags to BIT — was scoped but not yet funded at the time of writing. Developers writing new stored procedures must still remember to use RTRIM() on every string comparison and to convert INT dates before date arithmetic. This is a daily friction that was not eliminated by the migration.

The Write Path Did Not Get Faster

Contract origination end-to-end improved by only 1.2×. The project team had expected 3–5× on writes. The actual bottleneck was not the database — it was the application's network round-trips to the Progress 4GL origination service that was not replaced as part of this project. The SQL Server write is fast; the surrounding application plumbing is not. Fixing this requires a separate application modernisation project.

The COBOL Batch Layer Stayed on Progress

This is the single largest source of ongoing cost and technical debt. Every schema change proposal must be evaluated against VISION compatibility. Phase 2 modernisation (VARCHAR, DATE types, IDENTITY columns) cannot be completed until the batch layer is rewritten. The batch layer rewrite was descoped from the original project to stay within budget. In retrospect, it should have been in scope — the constraint it imposes on Phase 2 costs more in deferred benefit than the rewrite would have cost during the original project.

7. The Six Lessons That Apply to Every 4GL Migration

Distilled from 18 months of DemoBank Finance project work — these are the decisions that made the biggest difference, positive and negative.

The Full DemoBank Finance Case Study — What You Can Take Away

This five-article series documented every layer of the DemoBank Finance migration from Progress OpenEdge 4GL to SQL Server 2019. Here is a direct reference to each piece:

• Part 1 — Schema Design & DDL: Complete 18-table DDL with COBOL VISION rules, SYS_SEQUENCE pattern, full seed data, and sample records. Use this as a template for any 4GL migration schema.
• Part 2 — COBOL VISION Files: Three COBOL copybooks (CONT-HDR.cpy, CUST-MAST.cpy, ASST-MAST.cpy), the VISION Layout Validation procedure, and the ODBC export SELECT pattern. Use this before adding any column to a VISION-exported table.
• Part 3 — Application-Managed Relationships: All 10 orphan audit queries, four remediation strategies, and the phased FK addition script. Run the orphan audit before any legacy migration FK work.
• Part 4 — Migration Plan: SSIS staging pipeline, daily reconciliation procedure, seven parallel-run failures, 23-check cutover validation suite, and three Month-1 production incidents. A reusable migration playbook for any 4GL project.
• Part 5 — Before vs After (this article): Hard benchmarks, compliance improvements, honest cost accounting, and the six lessons that every 4GL migration team needs to know before they start.

All DDL, stored procedures, SSIS patterns, and validation scripts in this series are production-ready and can be adapted to any Progress OpenEdge, Sybase, Informix, or early Oracle migration to SQL Server. The schema decisions (CHAR fields, INT dates, app-managed sequences) are not DemoBank Finance-specific — they are present in the majority of finance and insurance systems built on these platforms between 1985 and 2005.

Frequently Asked Questions

When should you use SQL Server Standard Edition instead of Enterprise for a migration like this?

Standard Edition is sufficient if: you do not need Always On Availability Groups (Standard supports Basic AGs for one database), your largest table is under 524 PB (no limit in practice), and you do not need features like Partition Switching for the audit log, advanced columnstore indexes for analytics, or fine-grained online index operations. DemoBank Finance chose Enterprise because of the Always On requirement (they needed a read-only replica for reports without a separate reporting server) and the SYS_AUDIT_LOG partition switching for year-based archival. For a smaller portfolio (<20,000 contracts), Standard Edition would have been adequate and saved approximately £28,000/year in licensing.

Can you migrate Progress OpenEdge to Azure SQL Database instead of on-premises SQL Server?

Yes, and DemoBank Finance evaluated this. The reasons they chose on-premises SQL Server 2019 over Azure SQL Database: (1) FCA data residency requirements — confirmed UK data centres required, which Azure UK South/UK West satisfies, but the FCA audit team at the time was not familiar with cloud attestation evidence; (2) the COBOL batch layer uses the Progress ODBC driver from an on-premises server — adding a VPN or Express Route for the batch layer added latency and cost that negated the Azure savings; (3) the migration team had deep SQL Server on-premises experience and no Azure DBA resource. The Azure evaluation is being revisited in 2026 now that the batch layer is being rewritten.

What SQL Server features were not available in Progress OpenEdge that made the biggest difference?

Five features had no Progress equivalent and each delivered immediate value after migration: (1) Window functions — ROW_NUMBER(), LEAD()/LAG() for payment history analysis eliminated dozens of 4GL temp-table accumulation loops; (2) CTEs — readable recursive queries replaced multi-file 4GL hierarchy traversals; (3) Filtered indexes — the arrears-only schedule index (Part 1) has no Progress analogue; (4) SSMS and SSRS — ad-hoc querying and self-serve reporting changed the daily workflow of the entire operations team; (5) Temporal tables — once added in Phase 2, these gave FCA auditors a verifiable change history that no 4GL system can provide.