Capstone Part 4 — The Data Move & the Cutover Runbook

Parts 1 to 3 were decisions on paper. This is the part where something actually has to happen on a specific Sunday at a specific hour, and 22 clinics open on Monday whether you finished or not. You’ll do the arithmetic that decides how 50.2 terabytes physically travel — in the open, with every unit conversion shown — then write the minute-by-minute runbook for the single hardest cutover in the estate, and finally the rollback plan that answers the only question that matters at four in the morning: what happens to the clinical notes that were written twelve minutes ago and exist in exactly one place?

What this part assumes, and what it produces

☺ Like you’re 10: Three earlier pages made the decisions. This page turns three of those decisions into things you could hand to someone on a Sunday night and they’d know what to do.

This part is written to follow Part 3, but it is deliberately self-contained: every fact about the estate that this page needs is restated below, so you can work it cold. If you have done the earlier parts, use your own answers where they differ from the ones here — the method is what’s being practised, not the specific R you chose.

The three things Parts 1–3 were supposed to leave behind

What you will produce

• Artifact A — the data-movement plan. One row per dataset: size, file count, the path it travels by, why that path, who validates it on arrival, and who signs the reconciliation report.
• Artifact B — the PIMS-Core cutover runbook. A prep block from T-14 days, then a minute-by-minute table for the night with two go/no-go gates, a smoke test, and a comms plan.
• Artifact C — the rollback plan. An explicit point of no return, one plan for each side of it, a named decision owner, and the latest wall-clock minute the revert may be called.

The estate, restated — only the parts that move bytes

☺ Like you’re 10: Here’s the full list of boxes, how big each one is, and the rules about when you’re allowed to be carrying them.

Brambleside Veterinary Group runs 22 small-animal clinics plus one 24-hour emergency hospital, around 600 staff, all in one country. Everything currently lives at Fort Rusty: VMware vSphere 7 across a converted server room at the flagship clinic and eight racks in a colo 40 km away. The colo contract ends 31 March — month 9 of a nine-month programme, and there is no renewal beyond a three-month extension at 2.4× the rate.

The stated data footprint — 50.2 TB

The four constraints that decide this whole part

Artifact A — the data-movement plan

☺ Like you’re 10: Sort every box into “goes on the lorry”, “goes through the letterbox”, and “doesn’t go at all” — and write down how you’ll know it arrived.

Start with the arithmetic, because the arithmetic is the only part of a migration that gives an unambiguous answer. Everything else is judgement; this is a division problem, and a division problem that nobody does in the open is a division problem that gets guessed.

Step 1 — do the 41 TB sum, showing every conversion

The single question this part exists to answer: can ImageVault go over the wire? Do not reach for a rule of thumb. Do it properly, one line at a time, so that anyone reading your plan can find the step they disagree with.

ImageVault over the wire — the full working

  1.  41 TB                                   the archive, as it stands today
  2.  41 × 1,000            =    41,000 GB    TB → GB   (decimal, as storage is sold)
  3.  41,000 × 1,000        = 41,000,000 MB   GB → MB
  4.  41,000,000 × 8        = 328,000,000 Mb  BYTES → BITS  ← this is the ×8 everyone forgets
  5.  328,000,000 ÷ 150     =  2,186,667 s    at the 150 Mbps we are allowed to sustain
  6.  2,186,667 ÷ 3,600     =      607.4 h
  7.  607.4 ÷ 24            =       25.3 days at a PERFECT, uninterrupted 150 Mbps
  8.  25.3 ÷ 0.75           =       33.7 days at 75% goodput (TCP overhead, retries,
                                             the sync tool's own chatter, checksums)

  ANSWER: ~34 days of continuously saturating a link that 22 clinics also need,
          during which the archive grows by another ~1 TB.

Line 4 is where most people’s estimate silently breaks. Storage is sold in bytes; links are sold in bits per second. If your answer is eight times too optimistic, that is the line you skipped. Line 8 is the second trap: nobody ever gets the sticker rate. Pick a goodput assumption, write it down as an assumption, and be ready to defend it — 75% is a reasonable planning figure for a WAN sync with verification enabled, and if your organisation has measured its own, use that instead.

Step 2 — cost the appliance round trip honestly

An offline appliance — AWS Snowball, Azure Data Box, Google Transfer Appliance, and each cloud’s equivalents — is a rugged encrypted box the provider ships you. You copy onto it over your own LAN, ship it back, and they ingest it. Its wall clock is dominated by logistics, not by data size, which is exactly why it wins at scale. But it is not free of time, and a plan that says “ship an appliance” without a day count is not a plan.

Look hard at rows two and three. FileShare’s hot subset is eleven times smaller than ImageVault and takes 45% as long to copy. That is the file-count trap appearing for the first time — and it appears on your own LAN, before a single byte has gone anywhere near the WAN. If you sized the appliance window on total terabytes, you just under-booked it by a day.

Step 3 — find the crossover, and then distrust it slightly

The crossover is the dataset size at which the appliance stops being slower and starts being faster. It is worth deriving once, for your own link, because after that you can answer the question in your head for every dataset in the estate.

Where does the appliance start winning on wall-clock?

  Over the wire, per TB:   8,000,000 Mb ÷ (150 Mbps × 0.75) ÷ 86,400 s/day
                         = 0.823 days per TB

  By appliance:            14 days fixed (ship out, ship back, ingest)
                         + 0.058 days per TB of local copy (at 200 MB/s)

  Set them equal:          0.823 × S = 14 + 0.058 × S
                           0.765 × S = 14
                                   S = 18.3 TB

  CROSSOVER: just over 18 TB on THIS link with THIS logistics chain.

Now distrust it slightly, in two directions. Downward: at 10 TB the wire technically “wins” at 8.2 days — but that is 8.2 days of a circuit the clinics are sharing, and the appliance would have cost zero of it. Upward: the crossover collapses if you can buy more bandwidth, and it climbs if delivery is slow or your country’s customs are involved. 18.3 TB is a property of Brambleside’s situation, not a fact about the universe. The transferable skill is the derivation, not the number — that is precisely what the Size the Data Move drill exists to give you reps on.

Step 4 — the number that makes the whole plan work

Here is the part people miss. Once the bulk seed has landed, keeping the cloud copy current until Wave 4 does not need 150 Mbps. It needs almost nothing:

The standing sync, month 2 → month 8

  Archive growth:      900 GB/month
  900,000 MB × 8   =   7,200,000 Mb per month
  30 days × 86,400 =   2,592,000 seconds per month
  7,200,000 ÷ 2,592,000 = 2.78 Mbps average
  ÷ 0.75 goodput        = 3.7 Mbps of budget

  ANSWER: under 4 Mbps of your 150 keeps the cloud copy current
          for the six months between the seed landing and the flip.

That is the argument in one line: the appliance converts a 34-day link-saturating problem into an 18-day logistics problem plus a permanent 4 Mbps trickle. And it explains why Part 3 insists the seed happens in month 2 even though the cutover is in month 8. Seed early and you buy six months of cheap, boring, unattended sync. Seed late and either PIMS-Core reads 41 TB back over the VPN for months, or your entire nine-month schedule is hostage to one copy job with no second attempt before 31 March.

Step 5 — the FileShare problem, which bandwidth math will lie to you about

FileShare’s hot subset is 3.7 TB. By line 8 of the arithmetic above that is 3.0 days over the wire — comfortably under the crossover, so the wire should win. It doesn’t, and the reason has nothing to do with bandwidth.

• Enumeration dominates. A full walk of 1.1 million files across 140,000 folders, reading NTFS ACLs and timestamps, takes 3.5 hours against this NAS. That cost is paid before a single byte moves, and it is paid again on every incremental pass, because a timestamp-based sync has to re-walk the whole tree just to discover the three thousand files that changed.
• The median file is 340 KB, not 5.6 MB. The mean is dragged up by a few thousand large scans; 2% of the files hold about 74% of the bytes. So the long tail is hundreds of thousands of tiny files, each paying a fixed per-file cost in round trips, and per-file cost does not shrink when you buy more bandwidth.
• The window arithmetic is brutal. If your final sync is a timestamp walk and your cutover window is four hours, you have spent the entire window enumerating before you copy anything.

So FileShare gets three answers, not one: the hot 3.7 TB rides the same appliance as ImageVault (it is already going, and the box has room); the final delta uses a change-journal-based mechanism — the NTFS USN journal, or a DFS-R-style replication group — so the last sync reads a list of changes rather than re-deriving one; and the cold 2.5 TB never travels at all, because Part 1 marked it Retire.

Step 6 — write the ledger

Now put it all in one table. This is Artifact A. Every dataset in the estate appears exactly once, including the ones that don’t travel, because “we consciously decided not to move this” is a line a steering committee will ask you for and you will not want to reconstruct it from memory.

Artifact A — data-movement plan. One row per dataset. Columns:

  Dataset | Size | File count | Travels? | Path | Why this path
          | Scheduled | Validation on arrival | Signed off by

Rules for filling it in:
  • Every row's "Why this path" must cite a number, not an adjective.
  • Every row that travels must name a validation step that would
    catch a SILENT failure — not just "the job said success".
  • Every row must name a human who signs the reconciliation report.
  • Rows that don't travel still need a "Signed off by" — someone
    has to own the decision not to move something.

Step 7 — validation, and the difference between “the job said success” and “it arrived”

☺ Like you’re 10: The lorry driver saying “all delivered” is not the same as you counting the boxes and opening a few.

A copy tool reporting success means the tool did not crash. It does not mean the data is right. Every dataset in Artifact A therefore names a check that would catch a silent failure — the kind that reports green and is wrong. Four techniques cover almost everything, and Data Migration & Data Gravity covers the theory behind each:

And then the step that is not technical at all: somebody signs. Each reconciliation report gets a named human who has read it and accepts the data is correct. Not the person who ran the copy — the person who owns the consequence if it is wrong. That is why the ledger’s last column pairs a technical signer with a business one for anything with a legal or financial obligation attached.

Artifact B — the PIMS-Core cutover runbook

☺ Like you’re 10: The exact list of what happens, minute by minute, on the one night that matters — written weeks earlier, in daylight, by people who slept the night before.

PIMS-Core is the hardest single cutover in the estate, and it is worth being precise about why: it is used every minute in every clinic; it has a four-hour window that can only ever be a Sunday and never a Monday; it depends on Active Directory, which is Retained on-prem; it depends on StockCtrl and on PrintSrv, which is also Retained on-prem and latency-sensitive; and it reads 1.5 million imaging studies by UNC path, which means the imaging archive and the practice system have to flip in the same hour or not at all.

A note on which R you chose

The UNC path — the one design decision that makes this cutover short

PIMS-Core stores every study as a path like \\NAS-COLO-01\imaging\2019\04\1145882.dcm, in a table with 1,502,384 rows. When PIMS-Core moves, that string has to keep resolving. There are three ways to arrange that, and only one of them is a four-hour cutover:

This is worth internalising well beyond this exercise: the cheapest thing to move on the night is a pointer; the most expensive thing to move on the night is data. Every hour you spend in the weeks beforehand converting a data change into a pointer change buys you back ten minutes of the window and an entire rollback path. Read Architecture Patterns on what actually flips the traffic — DNS, load balancer, feature flag — and notice that every mechanism it lists is a pointer.

The prep block — everything that happens in daylight

A runbook that starts at 02:00 is a runbook that will fail at 02:40. Most of the work is before the night, done by people who are awake:

The seven people, and why one of them isn’t from IT

Two of those seven are not from IT, and that is deliberate. The clinical validator exists because “the service returned HTTP 200” and “a vet can do a consultation” are different claims, and only one of them matters on Monday. The decision owner exists because the person who spent nine months building this is the worst possible person to ask whether to abandon it at 03:55 — you want the decision owned by whoever carries the consequence, not whoever carries the sunk cost.

Note also what this costs: five of the seven come from a migration team that Part 3 established is six people, and one of them left at month 3. The entire remaining migration team is on this bridge. Nothing else happens that weekend, and your wave plan has to say so.

The night — minute by minute

This is the table. Every row has a clock time, an owner, an expected result, and a failure branch — because a runbook that only describes the happy path is a description, not a runbook.

The twelve reconciliation queries

Every one of these has an expected value recorded at 02:00 from the frozen source — that is the entire trick. A query that returns a number nobody can compare against has told you nothing.

-- Three of the twelve, written out in full. Run against the frozen SOURCE at
-- 02:02, record the results on the bridge; run against the TARGET at 02:30
-- and compare. Every one is an exact match or the cutover does not proceed.

-- (3) Total clinical notes — the record that matters most and is never deleted.
SELECT COUNT_BIG(*) AS note_rows, MAX(NoteId) AS max_id, MAX(CreatedUtc) AS newest
FROM   dbo.ClinicalNote;
-- expected: 4,912,663 | 4,912,663 | (the freeze instant, to the second)

-- (6) Money. Row count AND sum, because a count alone misses a mangled decimal.
SELECT COUNT_BIG(*) AS line_rows, SUM(AmountGross) AS gross
FROM   dbo.InvoiceLine
WHERE  FinancialYear = @currentFY;
-- expected: 1,204,559 | 18,442,915.60

-- (11) The ImageVault join. This number IS the archive's study count --
--      if these two ever disagree, a study exists that nothing can find.
SELECT COUNT_BIG(*) AS linked_studies
FROM   dbo.StudyLink
WHERE  UncPath IS NOT NULL;
-- expected: 1,502,384  -- matches the DICOM index exactly

The nine-step clinic script

☺ Like you’re 10: Don’t ask “is the computer on?” Ask a real vet nurse to do a real appointment, start to finish, and watch.

A smoke test that checks endpoints tests the thing you built. A smoke test that walks a working day tests the thing they need. The clinical validator runs all nine, in order, on a real clinic workstation — twice, once at the flagship and once from a remote clinic over the VPN:

1. Log in with a normal clinic account — proving the on-prem Active Directory that is Retained at Fort Rusty still authenticates against a system that now lives in the cloud.
2. Find a client by surname, with a deliberately awkward one — an apostrophe, an accent, a hyphen. Character-set damage in a migration hides in exactly these names.
3. Open the pet’s history and read a note written more than five years ago.
4. View an X-ray from ImageVault — the UNC path, the SMB endpoint, the DNS change and the appliance seed, all proven in one click.
5. Add a clinical note and save it. (During the smoke test the database is still read-only, so this step is run against a designated test patient after 04:00, and rerun if the revert is called.)
6. Dispense an item from StockCtrl, including a controlled drug, and confirm the register entry appears.
7. Print a dispensing label on the clinic’s own printer — PrintSrv is Retained on-prem and latency-sensitive, so this is the step that proves the hybrid path actually works rather than merely existing on a diagram.
8. Take a payment via the processor’s hosted redirect, confirming no cardholder data touches Brambleside systems — the arrangement that keeps the compliance scope small stays intact after the move.
9. Book a follow-up appointment and confirm it appears in the clinic diary.

The comms plan

The two rows that separate an experienced plan from a first one are the reference lab and the claims processor. They are external organisations with their own on-call, their own change freezes and their own opinions about a partner’s system going quiet at 02:00 on a Sunday. Telling them is not courtesy; it is how you avoid an automated integration being disabled at the other end because it looked like a fault.

Artifact C — the rollback plan

☺ Like you’re 10: Decide now, in daylight, the exact moment after which “let’s just put it back” stops being a thing you’re allowed to say — and write down what you do instead.

Everything above was preparation for this. A rollback plan is not a paragraph saying “we will roll back if necessary”; that is a wish. A rollback plan names a moment, names a person, names a deadline, and describes two completely different procedures either side of that moment.

Define the point of no return, precisely

This is why the freeze at 02:00 is not an inconvenience to be minimised — it is the mechanism that keeps the whole night reversible. Every minute the source is read-only is a minute in which a mistake costs nothing but time. That is a trade worth making deliberately: you are buying reversibility with downtime, and four hours of Sunday downtime buys you two full hours of it.

Before 04:00 — the clean revert

Rehearsed at T-14d, timed at 12 minutes. Who may call it: the cutover lead, and only the cutover lead. Latest permitted minute: 03:55 — and if go/no-go #2 has not produced a decision by 03:55, the revert runs automatically. The default is safe.

CLEAN REVERT — before 04:00. Rehearsed: 12 minutes.

  1. Cutover lead calls it, out loud, on the bridge, with the time. Logged.
  2. DNS: pims.brambleside.internal  -> on-prem VIP        (TTL 60s)
  3. DNS: NAS-COLO-01                -> colo NAS           (TTL 60s)
  4. Source database: READ_ONLY -> READ_WRITE
  5. Restart the application service on all 4 on-prem nodes
  6. Re-enable ClaimsFeed and LabBridge jobs against the on-prem instance
  7. Clinical validator reruns all NINE smoke-test steps -- against the OLD system.
     A revert is a change too, and an unverified revert is just a second outage.
  8. Comms wave 3-A within 10 minutes, to the full list:
     "The move did not go ahead. Nothing has changed for you. Monday is normal."
  9. Take down the maintenance page.

  DATA STATE AT THIS MOMENT: zero new writes exist anywhere.
  The source has been read-only since 02:00; the target took no user traffic.
  Nothing to reconcile. This is the entire reason the freeze exists.

Step 7 is the one people leave out. A revert puts you back on a system that has been read-only for two hours, with services restarted and DNS changed — three things that can each fail. Verify it with the same nine steps you would have used to accept the new system. Anything less and you have swapped a suspected problem for an unmeasured one.

After 04:00 — there is no rollback, only mitigate-forward

At 04:12 — twelve minutes past the point of no return — the new system holds roughly 40 clinical notes, 6 dispensing records including one controlled drug, and 3 card payments. They came from the emergency hospital, which never closes, and from the out-of-hours triage line. Every one of those clinical notes is a legal record with a seven-year retention obligation attached, and none of them exists anywhere else in the world.

So the plan changes shape entirely:

AFTER THE POINT OF NO RETURN — the order is fixed. Do not improvise it.

  STEP 1 — CAPTURE, ALWAYS, BEFORE ANYTHING ELSE.
      Run the pre-written delta export. Written and tested at the T-14d
      rehearsal -- NOT composed at 04:12 by someone who has been awake
      for twenty-two hours. Output to a file, checksummed, held by the DBA.

  STEP 2 — CHOOSE, and the choice is the decision owner's, not IT's:
      (a) FIX FORWARD -- stay live, vendor on the bridge. Default for
          anything that is merely slow or ugly.
      (b) DEGRADED MODE -- set the new system READ_ONLY, clinics fall
          back to the paper pack delivered at T-3d. Buys unlimited time
          with NO new writes accumulating. Default for anything that is
          wrong rather than slow.
      (c) REVERSE MIGRATION -- only if (a) and (b) have both failed.
          This is a project, not a night: transform the delta file,
          load it into the old system, and have a clinician verify
          every record with a second clinician countersigning.

  NEVER: flip DNS back before STEP 1 has completed. Ever. Not once.

-- The delta export. Written at the T-14d rehearsal. Tested at the rehearsal.
-- Its existence is a milestone, not an afterthought.

DECLARE @cutover datetime2 = '<< the exact UTC instant writes were opened >>';

SELECT * INTO dbo.delta_ClinicalNote
FROM dbo.ClinicalNote            WHERE CreatedUtc >= @cutover;

SELECT * INTO dbo.delta_ControlledDrug
FROM dbo.ControlledDrugRegister  WHERE EntryUtc   >= @cutover;

SELECT * INTO dbo.delta_InvoiceLine
FROM dbo.InvoiceLine             WHERE CreatedUtc >= @cutover;

SELECT * INTO dbo.delta_Appointment
FROM dbo.Appointment             WHERE BookedUtc  >= @cutover;

-- Then, immediately, the count that tells the decision owner how big
-- the problem is -- BEFORE they are asked to choose (a), (b) or (c):
SELECT 'notes'  AS t, COUNT_BIG(*) FROM dbo.delta_ClinicalNote
UNION ALL SELECT 'drugs',    COUNT_BIG(*) FROM dbo.delta_ControlledDrug
UNION ALL SELECT 'invoices', COUNT_BIG(*) FROM dbo.delta_InvoiceLine
UNION ALL SELECT 'appts',    COUNT_BIG(*) FROM dbo.delta_Appointment;

That last query is the most important thing on this page. The decision owner cannot choose between fix-forward and a reverse migration without knowing the size of the delta, and at 04:12 nobody has time to write a query. Forty records is an evening’s careful work for two clinicians. Four thousand is not recoverable by hand at all, and knowing which of those two you are in changes the answer.

The trigger table — what each failure actually means, on each side of 04:00

Milestones

☺ Like you’re 10: Tick a box only when the thing actually exists on your page — not because you’ve read about it and it sounds sensible.

Work these in order; each builds on the one before. Progress saves in this browser.

What “done” looks like for Part 4

☺ Like you’re 10: Four things you can check yourself, without anyone marking it.

• Your arithmetic shows why the appliance beats the wire — with every unit conversion visible, a stated goodput assumption, and a crossover you derived rather than remembered. Someone who disagrees with your answer can point at the exact line they disagree with.
• Every dataset has a named validation step and a named signer — including the ones that don’t travel. No row says “the job reported success”, and nothing financial or clinical is signed by one person alone.
• The runbook fits inside four hours with slack — your happy path finishes with at least 45 minutes unspent, every row names an owner and an expected result, and every row has an “if it fails” branch.
• Your rollback plan says what happens to the notes written at 04:12 — an explicit point of no return, a named person allowed to call the revert, a wall-clock deadline with a safe default if nobody decides, and a capture-before-anything-else rule for the other side of the line.

If any of those four is a paragraph of intent rather than a table of specifics, it isn’t done yet. The test is simple and unforgiving: could someone who was not in any of these conversations run this night from your document alone?

Where this goes next

Part 4 turned three decisions into three documents: an arithmetic-backed plan for moving 50.2 terabytes, a runbook a stranger could execute on a Sunday night, and a rollback plan that names the minute after which “put it back” stops being a sentence anyone is allowed to say. Continue to Capstone Part 5 — Operate, Optimize & Modernize, where the bill arrives and you find out what the lift-and-shift instinct really costs. Step back to Part 3 — The Wave Plan if the month-2 seed still feels arbitrary, or to Move Brambleside — Start Here for the whole track. And for the theory behind everything on this page, revisit Data Migration & Data Gravity, Architecture Patterns, Best Practices and Security, Cost & Resilience.