Keep the Database, Replace the App
Updated
IntermediateWhat you'll learn
~55 min- Map every dependent that lands in the legacy database (reports, jobs, other apps) with evidence, and freeze what they need
- Move the database to a managed SQL Server that keeps instance features such as Agent jobs (Azure SQL Managed Instance as the worked example; Amazon RDS for SQL Server and Cloud SQL for SQL Server compared) and prove it arrived intact, shape and content
- Hold both applications to a schema contract that fails the build on any change a legacy dependent cannot survive
- Let the new feature write the legacy tables in one transaction, on the same clock, with the same actor format, under its own least-privilege identity
Lesson 23 moved one route to the new intake feature and kept its writes away from the legacy tables while it ran in shadow mode. That was right for a rehearsal. A real cutover is the opposite: when INTAKE_ROUTE flips, the new feature has to write the same rows the legacy procedure writes, because everything else the office runs already reads them. The nightly report, the export job, a supervisor’s linked spreadsheet and the auditors’ saved queries all land in dbo.Intake. Moving them all at once is the big-bang migration the strangler pattern (replacing a system one route at a time while the old one keeps running) exists to avoid.
So the database stays the system of record and the application is replaced around it. This lesson moves the database intact, proves it arrived intact, writes a contract for its shape that both applications are held to, and then lets the new writer in.
Before the detail — Artifact: modernization/db-dependents.md, scripts/db/verify-intact.mjs with its report, db/schema-contract.json with a checker that runs in CI, and a new intakeStore that writes the legacy tables. Acceptance test: a deleted row and a changed column on the target fail the intact check by name; renaming a column a report reads fails the contract; a new writer that stamps its own clock fails the time-base test. Status: reusable guidance. Keeping the legacy database while the strangle runs is the case Microsoft’s Strangler Fig page tells you to plan for; the scripts and names are this module’s.
A restore carries the source data’s markings and handling rules with it. For a case database that usually means CUI (controlled unclassified information, data that law or policy says must be protected; in a commercial shop, think customer PII under contract), so treat every step below that touches a restore of the real database as handling it. Do it inside the authorization boundary, under the CUI determination from lesson 01, and never paste rows into a prompt. The agent work in this lesson reads schema, catalog views and query text, not case rows; query text can carry literal values, so confirm the determination covers it or strip literals first.
Who else lands here
Microsoft’s Strangler Fig page is direct about it: “Consider how to handle services and data stores that both the new system and the legacy system might use. Make sure that both systems can access these resources at the same time” (Strangler Fig pattern). Before you can do that, you need the list of who those systems are.
Plan the change with your agent
Open your agent in the project root and switch to Plan mode (Shift+Tab, /plan, or the mode picker). Ask it to inventory every database dependent against the restored rehearsal copy and plan modernization/db-dependents.md:
Inspect the rehearsal database configured in .env.bench read-only and plan modernization/db-dependents.md listing every view, procedure, job, trigger, and external client that touches dbo.Intake or dbo.IntakeAudit. Ask me anything unclear before writing files.What to check in the plan before you approve
- Queries run only against the restored rehearsal copy in
.env.bench(never production) and inspect four sources in order:sys.sql_expression_dependenciesandsys.dm_sql_referencing_entities,msdbjob steps, Query Store query text (with literals scrubbed to?), and the month-end session capture inmodernization/legacy/sessions.csv. - Each dependent row records name, kind, objects and columns touched, read/write mode, whether it uses
SELECT *or column-lessINSERT, and its evidence source. - Any dependent whose queries cannot be confirmed from catalog views or Query Store (such as a workstation connecting from Microsoft Access) is also logged as an open item in
modernization/unknowns.md.
If the agent’s plan proposes querying Query Store without scrubbing string or numeric literals from the SQL text, correct it before approving:
Replace every literal in Query Store query text with ? before writing the report so no case values appear in modernization/db-dependents.md.The catalog views find what lives inside the database and names a table: sys.sql_expression_dependencies “contains one row for each by-name dependency on a user-defined entity in the current database” (Microsoft Learn). They miss dynamic SQL and everything outside the database. Query Store (SQL Server’s built-in record of the queries a database ran, with their text and statistics) catches clients that send their own SQL. It is on by default only for new databases on recent SQL Server releases and on Azure SQL; a database restored from an older server keeps its old setting, so check it rather than assume it (Microsoft Learn). A client that runs once a quarter shows up in neither, which is why the session capture spans a month-end and why unknowns.md stays open.
For the intake office the file comes back like this:
| Dependent | Kind | Touches | R/W | Shape risk | Evidence |
|---|---|---|---|---|---|
usp_SubmitIntake | procedure | Intake (all), IntakeAudit (all) | W | column lists explicit | sys.sql_expression_dependencies |
submit.asp | legacy page | via usp_SubmitIntake | W | — | legacy/web/submit.asp:18 |
rpt_DailyIntake | view behind the daily report | Intake | R | SELECT * | view definition |
Intake_Nightly_Export | SQL Agent job, CmdExec step | Intake.CaseId, CaseType, Status, SubmittedAt | R | positional CSV columns | msdb.dbo.sysjobsteps |
trg_Intake_Touch | trigger on Intake | Intake | — | fires for every writer | sys.triggers |
MSACCESS from SUP-WS-014 | linked tables in a supervisor’s database | Intake | R | unknown column use | sessions.csv only |
Three rows change the plan. The view selects *. SQL Server fixes a view’s column list when the view is created, so a new column on dbo.Intake stays invisible to the report until someone refreshes or recreates the view, and then the report’s shape changes without anyone touching the report. Together with the export job’s positional columns, that freezes the table. The export job writes positional columns and uses a CmdExec (operating-system command) step, which the target in the next section does not run (Microsoft’s Managed Instance differences page: “Command shell isn’t yet supported”, T-SQL differences), so the job needs an owner and a rewrite before cutover (U-08). And the Access database appears only in the session capture; nobody has seen its queries (U-09). The trigger is not a risk by itself, but it fires for the new writer too, which the writer code below has to allow for. The auditors’ saved queries read both tables; Query Store shows none of them selecting * from dbo.IntakeAudit, which is why that table is not frozen and can take the new column below.
The rehearsal restore for this lesson holds more objects than lesson 22 showed: the view, the job, the trigger, and later a dbo.LegacyActor table. Lesson 22 read only what the intake page touches; a database always holds more than one page needs.
Move it intact
The target is a pattern decision, not a default:
- The problem. The database depends on features that live outside it, in the server instance: an Agent job here, perhaps a linked server or a cross-database query elsewhere in the estate.
- The worked example. Azure SQL Managed Instance. Microsoft’s feature comparison lists SQL Server Agent, cross-database queries, linked servers (with limits), CLR (.NET code running inside the database) and Database Mail as supported on Managed Instance and not on Azure SQL Database (features comparison). The move is a native backup restored from blob storage, which carries the database as it was. The verified run below used SQL Server semantics, which hold on all three targets, so the checks carry over; the instance-level rows differ, as the table shows.
- The legitimate alternative. Azure SQL Database, when the assessment finds nothing blocking. It does not restore a native backup, so the move is an import or a migration service, and Cloud Modernization Patterns (a separate training in this series) lesson 3.2 teaches the copy route: stamp a watermark before extracting, so no write falls between copy and cutover.
- The agency decision. Which target, on the assessment’s findings, and who owns each instance-level object that does not travel in the backup.
The same database can land on any of the three managed SQL Server services. Each column below is what that service’s documentation says on the checked date (2026-11-05); “not stated” means the source did not say, so test it in the rehearsal rather than assume.
| Concern | Azure SQL Managed Instance | Amazon RDS for SQL Server | Cloud SQL for SQL Server |
|---|---|---|---|
.bak restore route | RESTORE ... FROM URL from Blob storage is the only option; no FROM DISK | native backup and restore from S3, in the same Region as the instance | gcloud sql import bak from Cloud Storage; a full backup with a single backup set only |
| SQL Server Agent jobs | supported; the CmdExec step type is not (“Command shell isn’t yet supported”) | not stated on the pages checked | “SQL Server Agent enabled to facilitate replication and other jobs” |
| Linked servers | supported, only to SQL Server and SQL Database, without distributed transactions | limited support | supported |
| CLR | supported, but CREATE ASSEMBLY only from binary, no file-system access | none on SQL Server 2017 and higher | none (CLR assemblies and EXTERNAL ACCESS ASSEMBLY unavailable) |
xp_cmdshell / CmdExec (U-08’s export job) | not supported | not supported (extended stored procedures, including xp_cmdshell) | not stated; the sysadmin role is unavailable |
sysadmin role | not stated on the pages checked | not stated on the pages checked | unavailable |
| Time zone | set at creation, cannot be changed (default UTC) | set at creation, cannot be changed (default UTC) | settable at creation and changeable afterward |
| Collation | set at creation; later change unverified | set at creation; later change unverified | set at creation; cannot be changed |
| Logins | instance-level, not in the backup | instance-level migration path not stated on the pages checked | not stated; sysadmin unavailable |
Sources: lessons 34, 35 and 36 carry each cloud’s page citations and boundary notes: 34’s passwordless database login, 35’s RDS secret in Secrets Manager, 36’s rotated Cloud SQL secret. Two consequences read straight off the table: a database with CLR code cannot go to RDS 2017 or later or to Cloud SQL, and the export job’s CmdExec step needs the same owner and rewrite (U-08) on every target.
The database credential also differs by cloud, which the app side of this lesson must not assume. On Azure the app presents a platform-issued token; RDS for SQL Server and Cloud SQL have no IAM database login, so the app holds a secret with an owner and a maximum age, as lesson 08 sets out. The intakeStore below takes whichever the seam hands it.
That last point is where most moves stumble. A database backup holds the database. Logins, Agent jobs and operators, credentials, linked servers and server-level triggers live in the instance and have to be moved on their own; Microsoft’s migration guide lists them for exactly that reason (SQL Server to Managed Instance migration overview). Four more things change silently unless you pin them:
- Logins. Users in a restored database are mapped to logins by security identifier; on a new instance they are orphaned until you recreate the logins with the same identifier or replace them. The new application should not use a SQL login at all: it gets its own database principal, below.
- Collation. The server collation is chosen when the instance is created (and on Cloud SQL cannot be changed afterward; whether Managed Instance or RDS can change it is unverified). If it differs from the source, a legacy procedure that joins a temporary table to a user table can fail with a collation conflict.
- Compatibility level. A restore keeps the database’s compatibility level. Raising it changes query plans, so do it later as its own tested change, never as part of the move.
- Time zone.
SYSDATETIME()returns the server’s local time. Managed Instance and RDS fix the time zone at creation, so a wrong choice means a new instance; Cloud SQL lets you change it later. The check matters most on RDS, where Amazon also advises against restoring a backup across time zones and a snapshot cannot move to an instance in a different one. Azure SQL Database runs its instance in UTC; Microsoft also documents database- and session-level time-zone settings for it, which this module has not verified for Azure Government. If the old server ran on Eastern time and the new one does not, everySubmittedAtthe legacy procedure writes after the move shifts by hours, and “today’s intakes” in the daily report shifts with it.
The legacy pages also have to reach the new server. Classic ASP usually connects through an old OLE DB provider (the Windows data-access layer ADO sits on); plan the move to a current provider that negotiates the encryption Azure requires, and treat it as a change to the legacy app with its own test, scheduled before cutover.
Prove it intact
A native restore almost always arrives intact. The check below earns its place in three situations: the copy route, where a missed row is a real risk; the first rehearsal, where it proves the target was built right (collation, time zone); and every later rehearsal, where it catches drift. It compares two databases at the same frozen point: the source set read-only for the final backup, or a second restore of that same backup beside the target. A live source never matches.
Plan the intact check with your agent
Stay in Plan mode and ask the agent to design scripts/db/snapshot.mjs and scripts/db/verify-intact.mjs to compare the source and target restores:
We need scripts/db/snapshot.mjs and scripts/db/verify-intact.mjs to compare schema metadata and row-by-row content hashes between SOURCE_DB and TARGET_DB without using SQL Server's CHECKSUM_AGG. Ask me anything unclear before writing code.What to check in the plan before you approve
snapshot(pool, tables)captures database and server collation, compatibility level, server time zone (CURRENT_TIMEZONE_ID()or offset), table columns (type, length, precision, scale, nullability, identity, collation, default), indexes, triggers,CHECKand foreign-key constraints (enabled/trusted state plus definition SHA-256), and CRLF-normalized SHA-256 hashes of procedures, views, and functions.verify-intact.mjsstreams rows in primary-key order and converts every column to lossless text in SQL by exact type (style 1hex for binary/rowversion,style 121for date/time,style 3for float,style 2for money), preservingNULLas JSONnulland hashing in Node.- Any column type without a lossless conversion stops the run with an explicit error rather than falling back to a lossy cast.
- The output report contains
"intact": trueorfalseand omits timestamps so the evidence file hashes deterministically.
If the first plan uses BINARY_CHECKSUM or casts money and datetime2 columns with default styles, steer it before approving:
Do not use BINARY_CHECKSUM or default casts. Convert columns in SQL using exact CONVERT styles by type and stream rows into Node's crypto hash.The content hash is the part worth reading:
REFERENCE CODE scripts/db/verify-intact.mjs (excerpt): lossless type conversion and streaming row-content hash open reference hide reference Reference implementation from gov-dev-reference-app; open to inspect or compare with what your agent builds.
// scripts/db/verify-intact.mjs (excerpt)// Each column becomes unambiguous text before hashing, by exact type. NULL stays JSON null (never// folded into '' the way CONCAT would). A type with no lossless text form stops the run instead of// hashing something that could hide a difference.const AS_TEXT = { binary: 1, varbinary: 1, image: 1, timestamp: 1, rowversion: 1, // hex, style 1 date: 121, time: 121, datetime: 121, datetime2: 121, smalldatetime: 121, datetimeoffset: 121, float: 3, real: 3, // round-trip digits money: 2, smallmoney: 2, // all four decimals};const AS_IS = new Set(['bigint', 'int', 'smallint', 'tinyint', 'bit', 'decimal', 'numeric', 'char', 'varchar', 'nchar', 'nvarchar', 'text', 'ntext', 'uniqueidentifier', 'xml', 'sysname']);function asText(c) { const col = `[${c.name}]`; const style = AS_TEXT[c.type]; if (style === 1) return `CONVERT(varchar(max), CAST(${col} AS varbinary(max)), 1)`; if (style !== undefined) return `CONVERT(varchar(60), ${col}, ${style})`; if (AS_IS.has(c.type)) return `CAST(${col} AS nvarchar(max))`; throw new Error(`${c.name}: type ${c.type} has no conversion here; add one and a test before trusting the hash`);}
async function contentOf(pool, table, columns, indexes) { const pk = indexes.find((i) => i.isPrimaryKey); if (!pk) throw new Error(`${table}: no primary key; add an ordering before you can prove it intact`); const select = columns.map((c) => `${asText(c)} AS [${c.name}]`).join(', '); const order = pk.columns.split(',').map((c) => `[${c.trim()}]`).join(', '); const hash = createHash('sha256'); let rows = 0; const request = pool.request(); request.stream = true; // rows arrive one at a time; nothing is held in memory await new Promise((resolve, reject) => { request.on('row', (r) => { hash.update(JSON.stringify(columns.map((c) => r[c.name])) + '\n'); rows++; }); request.on('error', reject); request.on('done', resolve); request.query(`SELECT ${select} FROM ${table} ORDER BY ${order}`); }); return { rows, sha256: hash.digest('hex') };}Each choice closes a specific hole. CHECKSUM_AGG(BINARY_CHECKSUM(*)) is the query people reach for, and Microsoft’s own page says a change to the input “isn’t guaranteed” to change the checksum and to “consider using HASHBYTES instead” (BINARY_CHECKSUM); the aggregate also ignores row order. Hashing in Node rather than with HASHBYTES keeps the script working against an older source server, where HASHBYTES input was capped and string-aggregate functions did not exist. The explicit styles (CAST and CONVERT) stop two different values from turning into the same text: without them the driver rounds a datetime2 to milliseconds, and a plain cast of money keeps two of its four decimals, so 1.0001 and 1.0002 both become 1.00 (the container run below caught exactly that pair). The dispatch is by exact type name, because a pattern such as “contains time” also matches timestamp, which is a row version, not a time. A type the table does not cover, such as sql_variant or a spatial type, stops the run. The float style is not available on the oldest SQL Server releases; on such a source, add a conversion that keeps every digit, and a test, before trusting the hash. Matching hashes are strong evidence that the captured content matches for the types listed; they say nothing about a type the script refused.
The schema side compares the shape a dependent relies on: columns, keys, indexes, triggers, CHECK and foreign-key constraints, module definitions, collations and the server’s time zone. Permissions and instance-level objects are separate checks in the migration runbook. On a source too old to name its zone, the script compares the offset, read from both servers at the same moment.
The schema contract
Both applications now depend on one schema, and either team can change it. The contract says which changes a legacy dependent can survive, as a pure function a CI test runs:
REFERENCE CODE src/db/contract.ts (excerpt): schema contract checker for legacy database dependents open reference hide reference Reference implementation from gov-dev-reference-app; open to inspect or compare with what your agent builds.
// src/db/contract.ts (excerpt): which schema changes a legacy dependent can survive.export function contractViolations(contract: Contract, current: Snapshot): string[] { const v: string[] = []; for (const k of ['collation', 'serverCollation', 'compatibilityLevel'] as const) { if (contract.database[k] !== current.database[k]) v.push(`database ${k}: ${contract.database[k]} -> ${current.database[k]}`); } // The zone name, not the offset: the offset moves twice a year and the meaning of SYSDATETIME() does not. if (contract.server.timeZone !== current.server.timeZone) v.push(`server timeZone: ${contract.server.timeZone} -> ${current.server.timeZone}`); for (const [name, want] of Object.entries(contract.tables)) { const have = current.tables[name]; if (!want) continue; if (!have) { v.push(`${name}: table is gone`); continue; } for (const col of want.columns) { const now = have.columns.find((c) => c.name === col.name); if (!now) { v.push(`${name}.${col.name}: column is gone`); continue; } for (const [k, val] of Object.entries(col)) { if (now[k as keyof Column] !== val) v.push(`${name}.${col.name}: ${k} ${JSON.stringify(val)} -> ${JSON.stringify(now[k as keyof Column])}`); } } for (const col of have.columns.filter((c) => !want.columns.some((w) => w.name === c.name))) { if (contract.frozen[name]) v.push(`${name}.${col.name}: new column on a frozen table (${contract.frozen[name]})`); else if (!col.nullable && col.defaultDef === null) v.push(`${name}.${col.name}: new column is NOT NULL with no default`); } for (const ix of have.indexes.filter((i) => !want.indexes.some((w) => w.name === i.name))) { if (ix.isUnique) v.push(`${name}: new unique index ${ix.name} can reject writes a legacy writer makes`); } // ... existing indexes, triggers and constraints must be unchanged; a new trigger fires for the legacy // writer too, and a new CHECK or foreign-key constraint can reject a write it makes; // every contracted procedure and view keeps its definition hash. } return v;}db/schema-contract.json is a snapshot of the restored target plus one hand-kept field, frozen, that names each table a dependent reads by position and why: { "dbo.Intake": "rpt_DailyIntake reads SELECT *" }. The test tests/db/schema-contract.test.ts runs contractViolations() on fixture snapshots in every build, and the environment step npm run db:contract applies the branch’s migrations to a disposable restore, snapshots it, writes evidence/db-contract-report.json (the violations, an empty list when clean, and no timestamp), and fails on any violation.
“Additive is safe” is the rule people quote, and it is only mostly true. A new nullable column is safe on a table nobody reads by position; on a frozen table it changes what SELECT * returns and shifts a positional export. A new unique index is additive and can reject a write the legacy procedure makes. A new trigger fires for the legacy writer as well as yours. A new CHECK constraint is the quietest of all: added WITH NOCHECK, it skips the rows already there and rejects the next legacy write that breaks it. The contract allows exactly three things on a contracted table: a nullable column (or one with a default) on a table that is not frozen, a new non-unique index, and a new table. This is the expand step of what Martin Fowler’s site calls parallel change, “also known as expand and contract” (ParallelChange); DevOps Foundations (a separate training in this series) lesson 4.4 decides when to expand-and-contract versus pair a rollback. The contract step, dropping or renaming, waits until the last dependent that needs the old shape is retired, not just the last legacy writer.
Record the decision as a rule, because it is one (row R-10 in the table below). The citation is a decision file under modernization/, signed by the system owner, so lesson 22’s check-rules.sh resolves it like any other citation; the file must exist, and its line 4 must hold the decision.
Two writers, one database
When the route flips, intakeStore writes dbo.Intake and dbo.IntakeAudit while the legacy procedure is still writing them for every route that has not moved. Four things have to be the same for both writers or the rows stop meaning one thing.
One clock and one time base. Lesson 23’s submitIntake() stamps clock().toISOString(), which is UTC. The procedure stamps SYSDATETIME(), which is server local time. Written into the same datetime2 column, an Eastern-time server ends up holding two time bases that differ by five hours in winter, and nothing in the column says which row is which. The new store lets the database assign both timestamps, exactly as the procedure does. That is a legacy rule (R-08 below). Keeping it keeps its one known flaw too: local time repeats an hour when the clocks fall back, so two different instants can carry the same value. Changing the time base is a later, separate change, with every report that reads the column moved first.
One actor format. The procedure writes Session("user"), the caseworker’s legacy login. Lesson 23’s route passes session.sub, the sign-in subject (sub) from the identity provider of record. Every report that groups by caseworker would split each person in two. The reference choice is a small mapping table, dbo.LegacyActor (IdpSubject, LegacyLogin), keyed by the sign-in sub and kept by the identity team; a user with no mapping is refused rather than written in a new format. The alternative is an identity-provider claim that already carries the legacy login, if your directory has one; the agency decision is which, and who maintains it (R-09 below).
One transaction. The procedure has no explicit transaction (U-06). The new store puts both inserts in one, so a failed audit insert cannot leave an intake row without its audit row. That is a behavior change, not a free fix, so it needs a ruling from the business owner, recorded next to U-06. Probing the rehearsal database turned up a second difference worth knowing. Called from T-SQL, the procedure accepts an actor longer than 50 characters and its @SubmittedBy VARCHAR(50) parameter truncates it silently; the new store’s request is refused by the server and the transaction rolls back, leaving no row. Whether the ASP page’s ADO call truncates the same way was not tested. That is U-10 until someone rules on it.
Its own principal. The new application connects as its own database principal (lesson 08: a short-lived identity token or a stored secret, depending on your platform), never as the legacy SQL login, with only the rights it uses: INSERT on the two tables and SELECT on dbo.LegacyActor. Set the driver’s application name too (options: { appName: 'intake-next' }), which helps when you read session lists and traces. Do not treat it as attribution: Microsoft warns that APP_NAME “does not verify the application name value in any way” (APP_NAME), and it is not stored on any row. To record on each row who wrote it, the reference migration in gov-dev-reference-app adds a column the server fills (compare with your agent’s migration; you do not type it by hand):
-- db/migrations/0001_intake_audit_written_by.sql (allowed by the contract: IntakeAudit is not frozen)ALTER TABLE dbo.IntakeAudit ADD WrittenBy nvarchar(128) NULL CONSTRAINT DF_IntakeAudit_WrittenBy DEFAULT ORIGINAL_LOGIN();The procedure’s INSERT lists its columns, so it picks up the default without a change, and every new audit row names the server login that wrote it, legacy or new. Rows written before the migration stay NULL; history is not back-filled by guessing. A default is attribution for writers that cooperate: a principal with INSERT on the table can still supply its own value. Where that matters, SQL Server Audit on each writer’s principal is the record a client cannot override.
The store, written to those four rules:
REFERENCE CODE src/features/intake/store.ts: transaction wrapper, legacy actor lookup, and SQL Server intake store open reference hide reference Reference implementation from gov-dev-reference-app; open to inspect or compare with what your agent builds.
// src/features/intake/store.ts — the new writer, on the same tables the legacy procedure writes.import sql from 'mssql';import { withPool } from '@/lib/db/pool';import type { AuditRow, IntakeRow, IntakeStore } from './submit';
// One transaction per submission: both rows land, or neither does (see U-06 and the ruling on it).export async function inTransaction<T>(work: (tx: sql.Transaction) => Promise<T>): Promise<T> { return withPool(async (pool) => { // the one door to the database (lesson 08) const tx = new sql.Transaction(pool); let rolledBack = false; tx.on('rollback', () => { rolledBack = true; }); // the server can abort it first (mssql's documented pattern) await tx.begin(); try { const result = await work(tx); await tx.commit(); return result; } catch (e) { if (!rolledBack) await tx.rollback().catch(() => {}); // a failed rollback must not hide e throw e; // the original error, not a rollback complaint } });}
// The legacy login for a signed-in user (R-09). No mapping, no write: an opaque subject id in Actor would split// every report that groups by caseworker.export async function legacyActorFor(tx: sql.Transaction, sub: string): Promise<string | null> { const r = await new sql.Request(tx).input('sub', sql.NVarChar(256), sub) .query('SELECT LegacyLogin FROM dbo.LegacyActor WHERE IdpSubject = @sub'); return r.recordset[0]?.LegacyLogin ?? null;}
export function sqlIntakeStore(tx: sql.Transaction): IntakeStore { return { // SubmittedAt and At come from SYSDATETIME(), as the procedure's do (R-08): one clock and one // time base for every writer. The row's submittedAt from the app is deliberately not written. async insertIntake(row: IntakeRow) { const r = await new sql.Request(tx) .input('CaseType', sql.VarChar(20), row.caseType) .input('Score', sql.Int, row.score) .input('Status', sql.VarChar(20), row.status) .query(`INSERT dbo.Intake (CaseType, Score, Status, SubmittedAt) VALUES (@CaseType, @Score, @Status, SYSDATETIME()); SELECT CAST(SCOPE_IDENTITY() AS int) AS CaseId;`); // not OUTPUT: dbo.Intake has a trigger return r.recordset[0].CaseId as number; }, async insertAudit(row: AuditRow) { await new sql.Request(tx) .input('CaseId', sql.Int, row.caseId) .input('Action', sql.VarChar(20), row.action) .input('Actor', sql.VarChar(50), row.actor) .query(`INSERT dbo.IntakeAudit (CaseId, Action, Actor, At) VALUES (@CaseId, @Action, @Actor, SYSDATETIME());`); }, };}The legitimate alternative is to have the new store call usp_SubmitIntake itself and port its logic later. Less has to be proved on day one, because the procedure stays the only code that writes the rows, and the time base and the trigger behavior come with it. You give up the procedure-free transaction unless the caller wraps the call in its own (the procedure starts none), the score is still computed outside the procedure, and moving the rule out of the database becomes its own later change. The agency decision is which, and the acceptance test is the same either way: lesson 25’s bench shows the two writers’ rows match under the tolerances.
SCOPE_IDENTITY() is what the procedure uses, and it is the safe choice here: OUTPUT INSERTED.CaseId without INTO fails on a table with an enabled trigger (error 334 in the container run; OUTPUT clause), and @@IDENTITY can return an identity value a trigger generated in another table. The IntakeStore interface from lesson 23 is unchanged, so the differential harness still runs against its recording store. Only the route handler changes (reference excerpt from gov-dev-reference-app for comparison with what your agent updates):
// src/app/api/intake/route.ts (changed lines; imports SafeError from @/lib/response, and inTransaction,// legacyActorFor, sqlIntakeStore from @/features/intake/store, which reaches the database through withPool)const result = await inTransaction(async (tx) => { const actor = await legacyActorFor(tx, session.sub); if (!actor) throw new SafeError('no legacy login is mapped for this user', 403); return submitIntake(parsed.data, actor, sqlIntakeStore(tx));});The three rules this lesson adds, as rows of modernization/rules.md:
| ID | Rule | Citation | Side effects |
|---|---|---|---|
| R-08 | SubmittedAt and At are server local time from SYSDATETIME() | proc:usp_SubmitIntake | INSERT dbo.Intake; INSERT dbo.IntakeAudit |
| R-09 | Actor holds the caseworker’s legacy login | legacy/web/submit.asp:22; proc:usp_SubmitIntake | INSERT dbo.IntakeAudit |
| R-10 | objects in db/schema-contract.json change only in ways contractViolations() allows until every dependent that freezes them is retired | decisions/D-01-shared-database.md:4 | none |
Two consequences to know before they surprise someone. IDENTITY values are not reused after a rollback, so a refused submission leaves a gap in CaseId; gaps are normal and no report should count on their absence. And do not switch the database to read-committed snapshot isolation during the strangle to ease locking: it changes what every legacy reader sees.
Lesson 23’s response.caseId tolerance stays as it is: in its harness the new side runs against an in-memory store, so the two IDs really are allocated independently. Lesson 25 runs both writers against real restores and decides how to compare IDs there.
Rules whose test is not a corpus case
Lesson 23’s coverage test fails when a rule in rules.md has no golden case, and that is right for R-01 to R-07. R-08 to R-10 are different: no input to submitIntake() exercises a server clock, an actor mapping or a schema. Their tests of record are the environment tests and the contract test above. Name them in modernization/golden/tested-elsewhere.json instead of inventing cases (reference excerpt from gov-dev-reference-app for comparison with your agent’s file):
{ "R-08": { "file": "tests/db/timebase.env.test.ts", "name": "both writers stamp server local time" }, "R-09": { "file": "tests/db/actor.env.test.ts", "name": "writes the legacy login, never a sign-in subject id" }, "R-10": { "file": "tests/db/schema-contract.test.ts", "name": "refuses every change a frozen dependent cannot survive" }}And have the coverage test in tests/differential/intake.diff.test.ts check that each named test exists (reference excerpt from gov-dev-reference-app):
// tests/differential/intake.diff.test.ts (the coverage test, changed lines)const elsewhere: Record<string, { file: string; name: string }> = JSON.parse(readFileSync('modernization/golden/tested-elsewhere.json', 'utf8'));for (const [rule, t] of Object.entries(elsewhere)) { expect(readFileSync(t.file, 'utf8'), `${rule}: no test named "${t.name}" in ${t.file}`).toContain(t.name);}const uncovered = rows.filter((r) => r.cases === 0 && !elsewhere[r.rule]).map((r) => r.rule);This is reference validation: it proves the named test exists, not that it ran or passed. The results come from the environment run, which lesson 38 lists by name and the pull request attaches. The file sits under modernization/golden/, so moving a rule out of the corpus’s reach takes the same review as changing an expected output.
Watch it work
- Typed in the terminal:
claude - Mode:Plan mode
- You type in the session: We need scripts/db/snapshot.mjs and scripts/db/verify-intact.mjs to compare schema metadata and row-by-row content hashes between SOURCE_DB and TARGET_DB without using SQL Server's CHECKSUM_AGG. Ask me anything unclear before writing code.
- The tool replies: I will plan scripts/db/snapshot.mjs and scripts/db/verify-intact.mjs. I'll stream rows ordered by primary key, convert each column to lossless text by exact SQL type, keep NULL distinct from empty strings, and hash in Node. Should the output JSON omit timestamps so it hashes cleanly as build evidence?
- You type in the session: Yes, omit timestamps from the report and fail immediately if a column type has no lossless conversion. Approved to implement.
- The tool replies: Wrote scripts/db/snapshot.mjs and scripts/db/verify-intact.mjs.
- You type in the session: Run verify-intact against SOURCE_DB (restore A, read-only) and TARGET_DB (the Managed Instance rehearsal restore) for the tables in db/tables.txt.
- The tool replies: node scripts/db/verify-intact.mjs db/tables.txt evidence/db-intact-report.json intact: 2 tables, shape and content identical Report written: evidence/db-intact-report.json (no timestamp inside, so it can be hashed).
- You type in the session: On the TARGET only: delete the audit row for case 2, and make Intake.CaseType nullable. Then run verify-intact again.
- The tool replies: DIFFERENT shape.tables.dbo.Intake.columns.1.nullable: source=false target=true DIFFERENT content.dbo.IntakeAudit.rows: source=2 target=1 DIFFERENT content.dbo.IntakeAudit.sha256: source="45d156ee..." target="ba6a03f5..." NOT intact: 3 difference(s) exit 1. I have not repaired the target; re-restore it from the same backup.
- You type in the session: Re-restore the target. Apply each candidate migration to a fresh restore in turn and run the contract check: (1) add WrittenBy to IntakeAudit, (2) add Channel to Intake, (3) rename Intake.Status, (4) add a unique index on IntakeAudit(CaseId, Action).
- The tool replies: (1) ALTER TABLE dbo.IntakeAudit ADD WrittenBy ... DEFAULT ORIGINAL_LOGIN() contract: ok (2) ALTER TABLE dbo.Intake ADD Channel varchar(10) NULL dbo.Intake.Channel: new column on a frozen table (rpt_DailyIntake reads SELECT *) (3) EXEC sp_rename 'dbo.Intake.Status', 'CaseStatus', 'COLUMN' dbo.Intake.Status: column is gone dbo.Intake.CaseStatus: new column on a frozen table (rpt_DailyIntake reads SELECT *) (4) CREATE UNIQUE INDEX UX_IntakeAudit_Case ON dbo.IntakeAudit(CaseId, Action) dbo.IntakeAudit: new unique index UX_IntakeAudit_Case can reject writes a legacy writer makes Only (1) is allowed. (2) needs every dependent that reads dbo.Intake by position, or whose use is unknown, cleared first: the daily report, the export job and U-09.
Illustrative session; exact output varies by tool and agency configuration.
The output above, and the numbers in Prove it, come from running these scripts against a current SQL Server release in a container set to Eastern time, with the lesson 22 schema and procedure and the objects listed above loaded. The ASP page and a real Managed Instance were not part of that run.
Prove it
Three acceptance tests, each with the fault it must catch.
Intact. Restore the same backup twice and run node scripts/db/verify-intact.mjs db/tables.txt evidence/db-intact-report.json: expected intact: 2 tables, shape and content identical, exit 0. Plant on the target only: delete one audit row and make one column nullable. Expected: exit 1, with the table, the row count and the column’s nullable change each named by path, as in the session above.
Contract. Run npx vitest run tests/db in any checkout: fixture snapshots exercise each rule. Then, inside the boundary, npm run db:contract with a migration that renames Intake.Status. Expected: it fails with dbo.Intake.Status: column is gone. Plant ALTER TABLE dbo.Intake WITH NOCHECK ADD CONSTRAINT CK_Intake_Score CHECK (Score < 7): it fails with new CHECK constraint CK_Intake_Score can reject a legacy write. The WrittenBy migration must pass.
Time base and actor. The environment test tests/db/timebase.env.test.ts (“both writers stamp server local time”) writes one submission through the legacy procedure and one through sqlIntakeStore on a disposable restore, then reads both back with the server’s own clock using this reference query from gov-dev-reference-app:
SELECT DATEDIFF(minute, i.SubmittedAt, SYSDATETIME()) AS intakeSkew, DATEDIFF(minute, a.At, SYSDATETIME()) AS auditSkewFROM dbo.Intake i JOIN dbo.IntakeAudit a ON a.CaseId = i.CaseIdWHERE i.CaseId = @id;It passes when all four values are within one minute. Plant the obvious shortcut: pass the app’s row.submittedAt as a datetime2 parameter instead of SYSDATETIME(). On a server set to Eastern time in November the test fails with intakeSkew: -300, five hours, which is the difference every report that filters by day would have absorbed without a word. Its companion tests/db/actor.env.test.ts (“writes the legacy login, never a sign-in subject id”) covers three cases: a mapped user is written with the legacy login, an unmapped user gets a 403 and no rows, and an actor over 50 characters leaves no rows (U-10). Like the live endpoint test in lesson 38, the database environment tests need a restore and credentials, so they run inside the boundary and are reported as their own step, never mocked.
The new team wants to add a nullable Channel column to dbo.Intake to record whether an intake came from the web or the counter. The contract check fails. What is the right next step?
After cutover, the daily report shows a burst of intakes at 4 a.m. that nobody remembers taking. What is the most likely cause, and which check would have caught it?
Availability, authorization, agency approval, region support, and price are five separate facts. Tooling: Microsoft retired Data Migration Assistant on 2025-07-16 and Azure Data Studio, with its migration extension, on 2026-02-28 (DMA, Azure Data Studio). Its current guide names the Azure Arc migration experience and Azure Migrate for assessment, and native backup and restore, Log Replay Service, the Managed Instance link and Azure Database Migration Service for the move (migration overview, updated 2026-06-02). Azure Government: Microsoft’s audit-scope page lists SQL Managed Instance and Database Migration Service for Azure Government (audit scope, updated 2026-09-21); this module did not confirm which Government regions host Managed Instance or whether the Managed Instance link is available there, so check Azure’s products-by-region page and your agency’s authorization boundary. Recheck the cited source and your agency’s authorization boundary before you rely on any of it.
Practice status — keeping the legacy database as the system of record while the application is replaced
| Practice | Status | Also called |
|---|---|---|
| dependents of the database mapped from catalog views, captured queries and sessions | common baseline | dependency analysis; data lineage |
| move by native backup to a managed instance that keeps instance features | common baseline | lift and shift for the database; rehost |
| intact proof: schema snapshot plus ordered content hash at one frozen point | strong optional | migration validation; data reconciliation |
| schema contract checked in CI, with tables frozen by position-reading dependents | strong optional | database contract test; schema compatibility check |
| expand now, contract after every dependent is retired | common baseline | parallel change; expand and contract |
| both writers share one clock, one actor format and one transaction rule | strong optional | write-path parity |
| server-filled writer column instead of the client’s application name | emerging | row-level attribution |
Scale: required | common baseline | strong optional | reference-shop (seen only at organizations that publish their own practice) | emerging
Key takeaway
The database is where every other system already lands, so it moves first and intact, and the application is replaced around it. Map who lands there with evidence, prove the move at one frozen point with an ordered hash over explicitly converted values, and hold both applications to a contract that knows which tables are read by position. Then let the new writer in on the same clock, with the same actor format, in one transaction and under its own identity. Lesson 25 runs both writers side by side against real restores and puts agents to work finding the inputs the corpus misses.
- LEADERSHIP DECISION
- the database stays the system of record until the last dependent is retired; no schema change merges while the contract check is red, and each frozen table has an owner
- PRACTITIONER ACTION
- build db-dependents.md, run verify-intact on every rehearsal, wire the contract check, and ship the writer with the time-base and actor rules as tests
- SUCCESS MEASURE
- audit finding avoided: zero dependents broken at cutover and no mixed time bases or actor formats in shared rows
Legend: LEADERSHIP DECISION = what a leader decides · PRACTITIONER ACTION = what the engineer does next · SUCCESS MEASURE = how you know, in calendar time, assessor-hours or cost, contract or revenue risk, or audit finding avoided