By Alex
On the weekend of 20 April 2018, the British bank TSB moved 5.2 million customers off the platform it had rented from its former parent, Lloyds, and onto a new one built by its new owner, Banco Sabadell. The data was copied over in a single planned outage. When the doors reopened, customers saw other people’s accounts, payments vanished, and the online bank was unreliable for weeks. The independent review that followed, written by the law firm Slaughter and May, concluded that TSB “went live with a system that was neither stable nor complete”. The bill for compensation, fraud losses and remediation ran to roughly £330 million.
What makes TSB instructive is not that it was unusual. It is that every element of it had happened before, in some form, for forty years. Database migration is the oldest recurring project in corporate IT, and it keeps failing for the same few reasons. Looking at how the job has changed since the mainframe era, and how it has not, explains why the data is almost always the riskiest part of any modernisation, and what the teams that move it safely do differently.
Forty years of the same project
The first great migration wave was conceptual before it was technical. In 1968 IBM shipped IMS, a hierarchical database in which records hung from parent records like branches from a trunk, and programs navigated those trees with pointers. Two years later an IBM researcher, Edgar Codd, published the relational model: tables instead of trees, keys instead of pointers. IBM was slow to believe in its own researcher. IMS remained the company’s flagship, and the first commercial relational products, SQL/DS in 1981 and DB2 in 1983, arrived more than a decade after Codd’s paper.
The migration that followed took most of the next twenty years, and it established the pattern every later wave repeated. Moving from IMS to DB2 was never a copy. Data that had been shaped by how one program walked a tree had to be reshaped into normalised tables, which meant discovering what the data actually meant, field by field. Much of that meaning lived in application code, not in the database.
Each decade since has brought its own reason to move:
- The 1990s: ERP consolidation and Y2K. Companies collapsed dozens of departmental systems into SAP, Oracle or PeopleSoft, and the year-2000 deadline forced a census of every legacy store. A 1999 Standish Group study found that roughly 80% of data migration projects ran over time or budget.
- The 2000s: the ETL and data warehouse era. Extract-transform-load became a tool category of its own. It did not change the results. A 2007 Bloor Research survey of more than 700 large companies found that 64% of migration projects were delivered late and 37% ran over budget, by an average of 30%.
- The 2010s: cloud. In 2010-2011 Gartner described five ways to move an application to the cloud: rehost, refactor, revise, rebuild, replace. AWS later stretched the list to seven. Every one of them still had to bring the data along.
- The 2020s: managed databases and the SAP 2027 deadline. Teams are leaving self-hosted Oracle and SQL Server for managed PostgreSQL and cloud-native stores. Meanwhile SAP’s end of mainstream support for its older ERP has pushed thousands of companies through the same migration at once, and ISG research found nearly 60% of those projects delayed and over budget.
The tools are unrecognisable from 1985. The failure rate is not. That is the strongest evidence that the problem was never the tooling.
Why the data is the hard part
Application code can be rewritten, tested in isolation and redeployed in minutes. Data cannot. It carries three properties that make every move harder than it looks on a plan.
Data holds decisions nobody remembers making
A production database is a sediment of every business rule the company ever had. There is the status field with a value of 7 that only one retired developer could explain. There is the customer record with two addresses because a 2009 import ran twice. There are the dates stored as text in one table and as integers in another. None of this appears in the schema documentation, because the documentation describes what the system was supposed to hold, not what it actually holds. Profiling the real data before writing a single transformation rule is the step most often cut from plans, and it is the step that predicts the outcome best.
The Queensland Health payroll replacement, started in 2007, is the extreme case. A project planned at about A$98 million needed some 2,500 customisations and more than 130 manual workarounds to reproduce the award rules buried in the old system. It went live in 2010 with tens of thousands of pay errors and ended up costing taxpayers around A$1.2 billion.
Correctness is invisible at cutover
When a code deployment fails, it usually fails loudly: errors, alerts, a page that will not load. When a data migration fails, it fails quietly. A rounding rule applied to the wrong currency, a time zone shifted by an hour, or a foreign key mapped to the wrong customer all produce a system that runs perfectly and is wrong. TSB’s customers found the errors before the bank did. The only defence is reconciliation: counting, summing and comparing source against target at row, table and business level, before and after the switch.
Rollback stops being free the moment users write
Before cutover, abandoning a migration costs nothing but time. After cutover, every new transaction lands only in the new system, and going back means losing that work or migrating it in reverse, which is a second migration under pressure. This is why big-bang cutovers are so dangerous. They turn the decision to roll back into a negotiation with the business, held in the middle of an incident, and that negotiation is usually lost.
GitHub showed the same trade-off from the other side in October 2018. A 43-second network partition caused its database tooling to promote replicas in a second data centre, leaving writes in both locations that the other did not have. The engineers chose to keep the site degraded for just over 24 hours rather than fail back and risk losing user data. Once two copies of the data diverge, recovery is measured in hours of careful reconciliation, not in the seconds it takes to redeploy code.
What safe migrations have in common
The teams that move large datasets without incident did not find better tools than everyone else. They changed the shape of the project, so that no single moment carries all the risk.
Move in phases, not in one weekend
The Slaughter and May review singled out TSB’s choice of a single-event migration over a phased one. Phasing by customer segment, region or product line means the first wave is small enough that its errors are survivable, and each wave teaches you something about the data before the next. It costs more calendar time and more temporary integration work. That cost buys every later wave a smaller chance of failure.
Run both systems at once: dual writes and shadow reads
The modern version of phasing is to keep old and new stores live in parallel. Stripe described the pattern in “Online migrations at scale” in four steps:
- Dual-write: every new write goes to both the old and the new store, while a backfill copies historical records.
- Shadow-read: reads still come from the old store, but the same reads also run against the new one and the results are compared automatically. Stripe used GitHub’s Scientist library for this.
- Switch reads, then writes: once the comparisons stay clean, the new store becomes the source of truth.
- Clean up: only when no code path touches the old store is it switched off.
The point is not elegance. At every step before the last one, rolling back is a configuration change, not a recovery operation.
Rehearse the cutover until it is boring
A rehearsal on production-sized data, ideally a recent copy of production itself, is the only reliable way to learn how long the load really takes, which constraints fail, and which reports stop matching. A useful rule is to keep rehearsing until two consecutive runs finish inside the planned window with clean reconciliation. TSB tested in environments that did not match live, and the review was explicit that this made the causes of the failure hard even to identify afterwards.
Prove the backup by restoring it
In January 2017 an engineer at GitLab, working on a replication problem, ran a delete against the primary database instead of the secondary. Of the five backup and replication mechanisms the company thought it had, none worked as expected, and about six hours of data was lost for good. Every migration plan includes a backup step. Far fewer include a timed test restore of that backup into a clean environment. A backup that has never been restored is only an assumption.
Reconcile with numbers the business recognises
Row counts prove that records arrived. They do not prove they are right. Strong reconciliation adds checksums per table and, more importantly, business totals: account balances, open orders, invoice sums and active users, compared between source and target and signed off by someone outside the migration team. If finance cannot reproduce last month’s closing figures from the new system, the migration has not finished, whatever the project plan says.
What this means when you plan one
Most migrations are not TSB-sized, but they fail in the same way at smaller scale: a weekend that becomes a week, a report that quietly disagrees with the old one, a rollback plan nobody dared to use. In our own work, such as the database migration project and moving a client’s infrastructure to a new data centre, the effort went mostly into the unglamorous parts: profiling the real data, rehearsing on copies of production, and agreeing reconciliation checks with the business before cutover day.
If you are scoping a database migration, four questions tell you more about its risk than the choice of tool:
- Has anyone profiled the production data, rather than the schema, and listed what does not fit the target model?
- Is there a point after which rollback is no longer possible, and does the business know where it is?
- Has the cutover been rehearsed end to end on production-sized data, with timings?
- Who outside the project team signs off that the numbers match?
The next wave is already forming. AI workloads are pulling data into vector stores and lakehouses, and the SAP deadline will not be the last forced move. The platforms will keep changing, and the lesson from forty years of migrations stays the same: the data has to be treated as the product being delivered. A team that plans for the data to be wrong in ways nobody has found yet will usually finish on time. A team that assumes the old system was clean usually becomes the next cautionary example. If a migration is on your roadmap, our quality assurance and managed services teams can help plan the reconciliation and rehearsal work before cutover day.
