Migrating DemoBank Finance: Legacy Progress 4GL to Modern SQL Server

The Five-Phase Migration Plan

Phase 2: The SSIS Pipeline Architecture

The initial data load and the daily parallel-operation sync used the same SSIS package architecture. Four packages handled the full migration, each responsible for one domain.

The Staging Schema Pattern

No package wrote directly to the live dbo schema. Every table had a stg counterpart (e.g. stg.CONT_HDR) with identical column definitions but no constraints and no indexes. The SSIS package truncated the staging table, bulk-loaded from OpenEdge via ODBC, then a T-SQL post-load script ran the MERGE into dbo. This kept the live tables available throughout the load — no locking, no blocking.

-- ============================================================
-- Post-load MERGE: stg.CONT_HDR → dbo.CONT_HDR
-- Runs after PKG_CONTRACT_LOAD finishes the staging load.
-- ============================================================

MERGE dbo.CONT_HDR AS target
USING stg.CONT_HDR AS source
    ON target.CH_CONT_ID = source.CH_CONT_ID
WHEN MATCHED
    AND (   target.CH_STATUS       <> source.CH_STATUS
         OR target.CH_ARREARS_AMT  <> source.CH_ARREARS_AMT
         OR target.CH_TOTAL_PAID   <> source.CH_TOTAL_PAID
         OR target.CH_OUTSTANDING  <> source.CH_OUTSTANDING
         OR target.CH_MODIFIED_DATE <> source.CH_MODIFIED_DATE )
THEN UPDATE SET
    target.CH_STATUS        = source.CH_STATUS,
    target.CH_ARREARS_BAND  = source.CH_ARREARS_BAND,
    target.CH_ARREARS_AMT   = source.CH_ARREARS_AMT,
    target.CH_TOTAL_PAID    = source.CH_TOTAL_PAID,
    target.CH_OUTSTANDING   = source.CH_OUTSTANDING,
    target.CH_EARLY_SETT    = source.CH_EARLY_SETT,
    target.CH_SETT_METHOD   = source.CH_SETT_METHOD,
    target.CH_MODIFIED_DATE = source.CH_MODIFIED_DATE,
    target.CH_MODIFIED_BY   = source.CH_MODIFIED_BY
WHEN NOT MATCHED BY TARGET
THEN INSERT (
    CH_CONT_ID, CH_CONT_REF, CH_PROD_CODE, CH_CUST_ID, CH_ASSET_ID,
    CH_STATUS, CH_CURRENCY, CH_FINANCE_AMT, CH_DEPOSIT, CH_BALLOON_AMT,
    CH_RATE_TYPE, CH_INTEREST_RT, CH_APR, CH_TERM_MONTHS, CH_PAYMENT_FREQ,
    CH_PAYMENT_DAY, CH_FIRST_PMT_DT, CH_LAST_PMT_DT, CH_MATURITY_DT,
    CH_START_DATE, CH_ARREARS_BAND, CH_ARREARS_AMT, CH_TOTAL_PAID,
    CH_OUTSTANDING, CH_EARLY_SETT, CH_SETT_METHOD, CH_FCA_REGULATED,
    CH_INTRODUCER, CH_BRANCH_CODE, CH_COUNTRY_CODE,
    CH_CREATED_DATE, CH_CREATED_BY, CH_MODIFIED_DATE, CH_MODIFIED_BY
)
VALUES (
    source.CH_CONT_ID, source.CH_CONT_REF, source.CH_PROD_CODE,
    source.CH_CUST_ID, source.CH_ASSET_ID, source.CH_STATUS,
    source.CH_CURRENCY, source.CH_FINANCE_AMT, source.CH_DEPOSIT,
    source.CH_BALLOON_AMT, source.CH_RATE_TYPE, source.CH_INTEREST_RT,
    source.CH_APR, source.CH_TERM_MONTHS, source.CH_PAYMENT_FREQ,
    source.CH_PAYMENT_DAY, source.CH_FIRST_PMT_DT, source.CH_LAST_PMT_DT,
    source.CH_MATURITY_DT, source.CH_START_DATE, source.CH_ARREARS_BAND,
    source.CH_ARREARS_AMT, source.CH_TOTAL_PAID, source.CH_OUTSTANDING,
    source.CH_EARLY_SETT, source.CH_SETT_METHOD, source.CH_FCA_REGULATED,
    source.CH_INTRODUCER, source.CH_BRANCH_CODE, source.CH_COUNTRY_CODE,
    source.CH_CREATED_DATE, source.CH_CREATED_BY,
    source.CH_MODIFIED_DATE, source.CH_MODIFIED_BY
);

-- Log the merge result
INSERT INTO dbo.SYS_AUDIT_LOG
    (AL_LOG_ID, AL_TABLE_NAME, AL_ROW_ID, AL_ACTION,
     AL_FIELD_NAME, AL_OLD_VALUE, AL_NEW_VALUE,
     AL_CHANGED_BY, AL_CHANGE_DATE, AL_CHANGE_TIME, AL_SESSION_ID, AL_YEAR)
SELECT
    NEXT VALUE FOR dbo.sq_audit,  -- replaced SYS_SEQUENCE in Phase 2 for audit table
    'CONT_HDR           ',
    0, 'I',
    'MERGE_STATS        ',
    '                   ',
    'Rows merged: ' + CAST(@@ROWCOUNT AS VARCHAR(10)),
    'SSIS_PKG_CONT      ',
    CAST(FORMAT(GETDATE(),'yyyyMMdd') AS INT),
    FORMAT(GETDATE(),'HH:mm:ss'),
    'PARALLEL_RUN       ',
    YEAR(GETDATE());
GO

The ODBC Source Configuration

The SSIS ODBC source component connected to OpenEdge using the Progress DataDirect ODBC driver. Three settings were critical:

; Progress ODBC DSN settings (odbc.ini excerpt)
[DemoBank FinanceOpenEdge]
Driver       = /opt/progress/odbc/lib/ddprog11.so
Host         = oe-prod-01.internal
Port         = 5162
Database     = DemoBank Finance
LogonID      = ssis_reader
Password     = *****
ArraySize    = 5000          ; fetch 5000 rows per round-trip (critical for performance)
WorkArounds  = 536870912     ; Progress-specific: disable cursor sensitivity
ProcedureParameters = 0      ; do not enumerate stored procedure parameters on connect
FetchTSWTZasTimestamp = 1    ; treat timestamp with timezone as plain timestamp

The ArraySize = 5000 setting was the single biggest performance tuning win. The default array size of 1 caused SSIS to make one network round-trip per row — at 85,000 contract rows that meant 85,000 individual ODBC fetches. With ArraySize = 5000 the contract header extract dropped from 14 minutes to 45 seconds.

Phase 3: The Daily Reconciliation Script

Every morning during the 90-day parallel run, a SQL Agent job ran the reconciliation procedure at 06:00 — after the nightly SSIS sync completed at ~05:30 and before the first users logged in at 08:00. Any difference above the defined tolerance triggered a Severity 1 alert to the on-call DBA.

-- ============================================================
-- Daily parallel-run reconciliation
-- Compares SQL Server totals against OpenEdge shadow table
-- populated by the SSIS sync job.
-- ============================================================

-- The SSIS package also populates a reconciliation summary table
-- from the OpenEdge source query before the MERGE runs:
CREATE TABLE dbo.RECON_SHADOW (
    RS_RUN_DATE       DATE         NOT NULL,
    RS_TABLE_NAME     CHAR(20)     NOT NULL,
    RS_OE_ROW_COUNT   INT          NOT NULL DEFAULT 0,
    RS_OE_TOTAL_1     DECIMAL(18,2)NOT NULL DEFAULT 0, -- primary financial total
    RS_OE_TOTAL_2     DECIMAL(18,2)NOT NULL DEFAULT 0, -- secondary financial total
    RS_NOTES          VARCHAR(100) NOT NULL DEFAULT '',
    CONSTRAINT PK_RECON_SHADOW PRIMARY KEY (RS_RUN_DATE, RS_TABLE_NAME)
);

GO

CREATE OR ALTER PROCEDURE dbo.usp_DailyReconciliation
    @RunDate DATE = NULL
AS
BEGIN
    SET NOCOUNT ON;
    IF @RunDate IS NULL SET @RunDate = CAST(GETDATE() AS DATE);

    DECLARE @Failures INT = 0;

    -- ── Contract header reconciliation ──────────────────────
    DECLARE @SS_ContCount   INT,          @OE_ContCount   INT;
    DECLARE @SS_Outstanding DECIMAL(18,2),@OE_Outstanding DECIMAL(18,2);
    DECLARE @SS_TotalPaid   DECIMAL(18,2),@OE_TotalPaid   DECIMAL(18,2);

    SELECT @SS_ContCount   = COUNT(*),
           @SS_Outstanding = SUM(CH_OUTSTANDING),
           @SS_TotalPaid   = SUM(CH_TOTAL_PAID)
    FROM   dbo.CONT_HDR
    WHERE  CH_STATUS IN ('AC','AR','SU');  -- active portfolio only

    SELECT @OE_ContCount   = RS_OE_ROW_COUNT,
           @OE_Outstanding = RS_OE_TOTAL_1,
           @OE_TotalPaid   = RS_OE_TOTAL_2
    FROM   dbo.RECON_SHADOW
    WHERE  RS_RUN_DATE   = @RunDate
      AND  RS_TABLE_NAME = 'CONT_HDR';

    IF ABS(@SS_ContCount - @OE_ContCount) > 0
    BEGIN
        PRINT 'FAIL: CONT_HDR active row count: SS=' + CAST(@SS_ContCount AS VARCHAR)
            + ' OE=' + CAST(@OE_ContCount AS VARCHAR);
        SET @Failures += 1;
    END

    -- Tolerance: outstanding balance within £100 (rounding differences in COMP-3 decode)
    IF ABS(@SS_Outstanding - @OE_Outstanding) > 100.00
    BEGIN
        PRINT 'FAIL: CONT_HDR outstanding balance: SS=' + CAST(@SS_Outstanding AS VARCHAR)
            + ' OE=' + CAST(@OE_Outstanding AS VARCHAR)
            + ' Diff=' + CAST(ABS(@SS_Outstanding - @OE_Outstanding) AS VARCHAR);
        SET @Failures += 1;
    END

    -- ── Payment reconciliation ───────────────────────────────
    DECLARE @SS_PmtCount INT, @OE_PmtCount INT;
    DECLARE @SS_PmtTotal DECIMAL(18,2), @OE_PmtTotal DECIMAL(18,2);

    SELECT @SS_PmtCount = COUNT(*), @SS_PmtTotal = SUM(CP_AMOUNT)
    FROM   dbo.CONT_PAYMNT
    WHERE  CP_PMT_DATE >= CAST(FORMAT(DATEADD(DAY,-7,@RunDate),'yyyyMMdd') AS INT)
      AND  CP_REVERSAL_FLG = 'N';

    SELECT @OE_PmtCount = RS_OE_ROW_COUNT, @OE_PmtTotal = RS_OE_TOTAL_1
    FROM   dbo.RECON_SHADOW
    WHERE  RS_RUN_DATE = @RunDate AND RS_TABLE_NAME = 'CONT_PAYMNT_7D';

    IF ABS(@SS_PmtCount - @OE_PmtCount) > 0
    BEGIN
        PRINT 'FAIL: CONT_PAYMNT 7-day count: SS=' + CAST(@SS_PmtCount AS VARCHAR)
            + ' OE=' + CAST(@OE_PmtCount AS VARCHAR);
        SET @Failures += 1;
    END

    IF ABS(@SS_PmtTotal - @OE_PmtTotal) > 0.01  -- zero tolerance on payment amounts
    BEGIN
        PRINT 'FAIL: CONT_PAYMNT 7-day total: SS=' + CAST(@SS_PmtTotal AS VARCHAR)
            + ' OE=' + CAST(@OE_PmtTotal AS VARCHAR);
        SET @Failures += 1;
    END

    -- ── Customer count ───────────────────────────────────────
    DECLARE @SS_CustCount INT, @OE_CustCount INT;
    SELECT @SS_CustCount = COUNT(*) FROM dbo.CUST_MAST WHERE CM_ACTIVE_FLAG = 'Y';
    SELECT @OE_CustCount = RS_OE_ROW_COUNT FROM dbo.RECON_SHADOW
    WHERE RS_RUN_DATE = @RunDate AND RS_TABLE_NAME = 'CUST_MAST';

    IF ABS(@SS_CustCount - @OE_CustCount) > 0
    BEGIN
        PRINT 'FAIL: CUST_MAST active count: SS=' + CAST(@SS_CustCount AS VARCHAR)
            + ' OE=' + CAST(@OE_CustCount AS VARCHAR);
        SET @Failures += 1;
    END

    -- ── Summary ──────────────────────────────────────────────
    IF @Failures = 0
        PRINT 'RECONCILIATION PASSED — ' + CAST(@RunDate AS VARCHAR) + ' — all checks clean.';
    ELSE
        PRINT 'RECONCILIATION FAILED — ' + CAST(@RunDate AS VARCHAR)
            + ' — ' + CAST(@Failures AS VARCHAR) + ' check(s) failed. Escalate to on-call DBA.';

    RETURN @Failures;
END;
GO

The Seven Reconciliation Failures During Parallel Operation

Over 90 days of parallel operation, the reconciliation script failed on seven separate days. Each failure was resolved within the same business day. The causes are worth documenting because they are representative of what every 4GL-to-SQL migration team encounters.

Failures 3 and 6 were the most significant: they revealed gaps in the dual-write implementation and an unhandled timeout scenario. Both required application code changes — the only application changes made during the entire parallel operation period. Identifying them in parallel operation, not in production, was the entire point of the 90-day run.

Phase 4: The Cutover Weekend Playbook

The cutover ran Friday 7 June 2024 at 21:00 to Sunday 9 June 2024 at 21:00. The window was chosen to avoid the mid-month direct debit run (15th) and the month-end batch (30th). Every step had a named owner, a start time, an expected duration, and a go/no-go decision point.

-- ============================================================
-- Cutover Validation Script Suite — 23 checks
-- All must return PASS before OpenEdge is decommissioned.
-- ============================================================

-- Check 1: Final row count reconciliation
SELECT
    'CONT_HDR'     AS TableName, COUNT(*) AS SqlServerCount FROM dbo.CONT_HDR   UNION ALL
SELECT 'CUST_MAST',  COUNT(*) FROM dbo.CUST_MAST   UNION ALL
SELECT 'ASST_MAST',  COUNT(*) FROM dbo.ASST_MAST   UNION ALL
SELECT 'CONT_SCHED', COUNT(*) FROM dbo.CONT_SCHED  UNION ALL
SELECT 'CONT_PAYMNT',COUNT(*) FROM dbo.CONT_PAYMNT;
-- DBA manually compares against OpenEdge counts printed in the go/no-go pack

-- Check 2: No active contracts with zero outstanding balance
SELECT COUNT(*) AS BadContracts
FROM   dbo.CONT_HDR
WHERE  CH_STATUS = 'AC' AND CH_OUTSTANDING = 0 AND CH_MATURITY_DT > 20240607;
-- Expected: 0

-- Check 3: No schedule rows in PENDING status past their due date
DECLARE @CutoverDateInt INT = 20240607;
SELECT COUNT(*) AS OverdueSchedules
FROM   dbo.CONT_SCHED
WHERE  CS_STATUS = 'PE' AND CS_DUE_DATE < @CutoverDateInt;
-- Expected: 0 (all should be OV=Overdue, not PE=Pending)

-- Check 4: SYS_SEQUENCE values are ahead of all current max IDs
SELECT
    sq.SQ_TABLE_NAME,
    sq.SQ_NEXT_VAL          AS SequenceNextVal,
    mx.MaxCurrentID,
    CASE WHEN sq.SQ_NEXT_VAL > mx.MaxCurrentID THEN 'PASS' ELSE 'FAIL' END AS Check
FROM dbo.SYS_SEQUENCE sq
JOIN (
    SELECT 'CONT_HDR'    t, MAX(CH_CONT_ID)   v FROM dbo.CONT_HDR   UNION ALL
    SELECT 'CONT_SCHED',    MAX(CS_SCHED_ID)    FROM dbo.CONT_SCHED  UNION ALL
    SELECT 'CONT_PAYMNT',   MAX(CP_PMT_ID)      FROM dbo.CONT_PAYMNT UNION ALL
    SELECT 'CUST_MAST',     MAX(CM_CUST_ID)     FROM dbo.CUST_MAST   UNION ALL
    SELECT 'ASST_MAST',     MAX(AM_ASSET_ID)    FROM dbo.ASST_MAST
) mx ON mx.t = RTRIM(sq.SQ_TABLE_NAME);
-- Expected: all PASS

-- Check 5: VISION layout validation (from Part 2)
EXEC dbo.usp_ValidateVisionLayout;
-- Expected: all rows show 'OK'

-- Check 6: No orphaned records remain (from Part 3)
SELECT COUNT(*) AS OrphansRemaining
FROM   dbo.ORPHAN_AUDIT
WHERE  CAST(OA_RUN_DATE AS DATE) = '2024-06-06';  -- last pre-cutover audit run
-- Expected: 0

-- Check 7: All indexes in a healthy state (no disabled indexes)
SELECT i.name, t.name AS TableName, i.is_disabled
FROM   sys.indexes i
JOIN   sys.tables  t ON t.object_id = i.object_id
WHERE  i.is_disabled = 1;
-- Expected: 0 rows

-- Check 8: Active contract financial totals within tolerance
SELECT
    SUM(CH_OUTSTANDING) AS TotalOutstanding,
    SUM(CH_TOTAL_PAID)  AS TotalPaid,
    SUM(CH_FINANCE_AMT) AS TotalFinanced,
    COUNT(*)            AS ActiveContracts
FROM dbo.CONT_HDR
WHERE CH_STATUS IN ('AC','AR','SU');
-- Compare against figures in the go/no-go pack (tolerance: outstanding ±£500)

-- Check 9: Direct debit file can be generated (dry run)
EXEC dbo.usp_GenerateDirectDebitFile
    @RunDate    = 20240615,  -- next scheduled DD date
    @DryRun     = 1;         -- does not write file, just returns row count
-- Expected: row count matches OpenEdge DD file from parallel run Week 12

-- Check 10: FCA regulated contract count (must match FCA notification figure)
SELECT COUNT(*) AS FCAContracts
FROM   dbo.CONT_HDR
WHERE  CH_FCA_REGULATED = 'Y' AND CH_STATUS IN ('AC','AR','SU');
-- Expected: within 1% of figure submitted in FCA notification

Checks 11–23 covered: index fragmentation levels, TempDB configuration, SQL Agent job status, backup configuration verification, read-only standby availability, VISION export dry run, arrears band distribution match, and the application team's end-to-end smoke test of 10 key user journeys against the SQL Server database.

The Go/No-Go Decision Point

The go/no-go decision was taken at 15:00 on Saturday — 18 hours into the cutover window. This gave the team 30 hours of remaining window to roll back to OpenEdge if needed. The rollback trigger was defined in writing before the cutover started:

Phase 5: The Three Month-1 Incidents

Hypercare ran through June 2024. Three incidents required DBA intervention in the first 30 days. None were data integrity incidents — all were performance and configuration issues that the pre-production environment had not exposed.

Incident 1: Parameter Sniffing on the Payment Allocation Query (Day 3)

The payment allocation stored procedure usp_AllocatePayment ran in under 1 second during UAT. In production on Day 3 it started timing out at 30 seconds. Root cause: the procedure had a @ContID INT parameter. The first execution with a high-volume contract (10,000+ schedule rows) compiled a plan optimised for that contract. Subsequent executions with standard contracts (48 rows) used the same plan — a nested loops scan that was terrible for small contracts but great for the sniffed large one.

-- Fix: add OPTION(OPTIMIZE FOR UNKNOWN) to prevent sniffing
-- on the variable-row-count contract schedule join
CREATE OR ALTER PROCEDURE dbo.usp_AllocatePayment
    @ContID  INT,
    @Amount  DECIMAL(13,2),
    @PmtDate INT
AS
BEGIN
    SET NOCOUNT ON;

    SELECT
        CS_SCHED_ID,
        CS_DUE_DATE,
        CS_TOTAL_AMT - CS_PAID_AMT AS AmountDue
    FROM   dbo.CONT_SCHED
    WHERE  CS_CONT_ID = @ContID
      AND  CS_STATUS  = 'PE'
    ORDER  BY CS_DUE_DATE
    OPTION (OPTIMIZE FOR (@ContID UNKNOWN));  -- <-- fix
END;
GO

Incident 2: TempDB Contention During Month-End Batch (Day 25)

The month-end arrears calculation ran 85,000 individual contract updates — each one opening a transaction, recalculating arrears bands, and updating CONT_HDR. TempDB hit PAGELATCH_EX waits on the PFS (Page Free Space) pages within minutes. Production had 1 TempDB data file; the recommendation is one per logical CPU core (up to 8).

-- Fix: add TempDB files to match logical CPU count
-- Run in the brief window between batch completion and next business day start
-- (requires brief SQL Server restart or ALTER DATABASE ... ADD FILE)

-- Check current TempDB file count
SELECT name, physical_name, size * 8 / 1024 AS SizeMB
FROM   tempdb.sys.database_files;

-- Add 7 more files (server has 8 logical CPUs — want 1 file per CPU)
ALTER DATABASE tempdb ADD FILE (
    NAME = 'tempdev2', FILENAME = 'T:\MSSQL\DATA\tempdb2.ndf', SIZE = 1024MB, FILEGROWTH = 256MB);
ALTER DATABASE tempdb ADD FILE (
    NAME = 'tempdev3', FILENAME = 'T:\MSSQL\DATA\tempdb3.ndf', SIZE = 1024MB, FILEGROWTH = 256MB);
-- (repeat for tempdev4 through tempdev8)

-- Also: batch the arrears update to avoid long-running transaction
-- Process 1000 contracts per batch instead of all 85,000 in one transaction
DECLARE @BatchSize INT = 1000, @Offset INT = 0, @TotalRows INT;
SELECT @TotalRows = COUNT(*) FROM dbo.CONT_HDR WHERE CH_STATUS IN ('AC','AR');

WHILE @Offset < @TotalRows
BEGIN
    UPDATE c
    SET    c.CH_ARREARS_BAND = dbo.fn_CalcArrearsBand(c.CH_CONT_ID),
           c.CH_ARREARS_AMT  = dbo.fn_CalcArrearsAmt(c.CH_CONT_ID)
    FROM   dbo.CONT_HDR c
    JOIN (
        SELECT CH_CONT_ID FROM dbo.CONT_HDR
        WHERE  CH_STATUS IN ('AC','AR')
        ORDER  BY CH_CONT_ID
        OFFSET @Offset ROWS FETCH NEXT @BatchSize ROWS ONLY
    ) batch ON batch.CH_CONT_ID = c.CH_CONT_ID;

    SET @Offset += @BatchSize;
    CHECKPOINT;  -- flush dirty pages regularly, keeps log lean
END;

Incident 3: SYS_SEQUENCE Contention at Peak Origination Time (Day 18)

At 10:00 on Day 18 — the mid-morning origination peak when 12 brokers simultaneously submitted new contract applications — the sequence fetch procedure caused blocking chains up to 8 seconds long. This was the exact race condition documented in Part 1. The two-statement version of usp_GetNextID was still in production. The OUTPUT clause fix was deployed in a 10-minute maintenance slot at lunchtime.

-- Fix deployed on Day 18 lunch — see Part 1 for full explanation
CREATE OR ALTER PROCEDURE dbo.usp_GetNextID
    @TableName CHAR(20),
    @NextID    INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @T TABLE (NewID INT);

    UPDATE dbo.SYS_SEQUENCE
    SET    SQ_NEXT_VAL     = SQ_NEXT_VAL + SQ_INCREMENT,
           SQ_LAST_UPDATED = CAST(FORMAT(GETDATE(),'yyyyMMdd') AS INT)
    OUTPUT INSERTED.SQ_NEXT_VAL INTO @T(NewID)
    WHERE  SQ_TABLE_NAME = @TableName;

    SELECT @NextID = NewID FROM @T;
END;
GO
-- Blocking duration immediately dropped from 8s to <50ms after deployment.

What's Next in the DemoBank Finance Series

• Part 5 — Before vs After: The Real Benefits of Modernising a Legacy Finance Database (next): The performance benchmarks (4-hour report runs → 3 minutes), the developer experience improvements, FCA compliance posture after FK constraints were added, and the honest accounting of what the migration cost versus what it delivered.

The SSIS package architecture, reconciliation procedure, cutover validation scripts, and batch-update pattern from this article are directly reusable on any 4GL migration project. The seven reconciliation failures are representative — expect similar categories on your own project, not necessarily the same causes but the same domains: status definition mismatches, timing windows, dual-write gaps, unported procedures, dirty source data, timeout handling, and batch job scheduling conflicts.

Frequently Asked Questions

How was the dual-write implemented during parallel operation?

The application's data access layer was modified to write to both OpenEdge (via the 4GL ODBC driver) and SQL Server (via the SQL Server ODBC driver) within the same application-level transaction. The OpenEdge write was the primary: if it failed, the transaction aborted and the SQL Server write was rolled back. The SQL Server write was secondary: if it failed, the transaction also aborted (after Incident 6 in Week 11 — before that fix, SQL Server failures were swallowed silently). This approach meant OpenEdge remained the system of record until cutover day.

Was there a dry run of the cutover weekend before the real one?

Yes — two dress rehearsals. The first rehearsal in April used a clone of the production database and ran all 23 validation scripts against it. Seven scripts failed due to data differences between the clone and the real production state. The scripts were tuned and a second rehearsal in May passed all 23. Dress rehearsals are non-negotiable on a regulated system: a failed validation on a real cutover night with stakeholders watching is a very different experience from a failed validation in a rehearsal.

What did the FCA notification process involve?

Under FCA SYSC (Senior Management Arrangements, Systems and Controls) rules, DemoBank Finance was required to notify the FCA at least 30 days before making a material change to a regulated system. The notification described the change (database platform migration), the risk mitigations (parallel operation, rollback plan, data integrity audit), and the testing evidence (90-day reconciliation results). The FCA acknowledged the notification within 14 days with no objections. The FCA compliance team also required that the contract counts in the SQL Server database matched the figures in the notification — which is why Check 10 in the cutover validation suite verifies the FCA contract count within 1%.