Log in
Schema change is inevitable. We do not get everything right the first time, and even when we do, the world around the database keeps changing. Requirements mature, products evolve, and assumptions that made sense years ago eventually stop matching reality.
Businesses change too. We acquire companies, merge with other customers and systems, enter new markets, and adapt to new opportunities. Sometimes a schema has to change because the original design was wrong. More often, it changes because the business is no longer the same business that existed when the schema was designed.
That is normal. The challenge is not avoiding schema change. The challenge is making those changes without interrupting the applications and users that depend on the database.
Spoiler alert
If there is anything that should be said about maintaining uptime through schema change, it should be: don’t get in a hurry. A sensible approach is a phased approach. It’s tempting to jump ahead, but read through this article and you’ll hopefully conclude that an intentional approach preserves integrity and eliminates downtime.A simple example
Consider something simple: a Name column in a User table needs to become FirstName and LastName. The final schema is straightforward. Getting there safely in production is not. If uptime matters, this becomes a multi-phase migration where old and new application versions can coexist while the database evolves underneath them.
Phase 1: Add columns FirstName and LastName
The safest first step is also the simplest: add the new columns without changing anything the application already depends on.
ALTER TABLE dbo.[User]
ADD
FirstName nvarchar(100) NULL,
LastName nvarchar(100) NULL;At this point, Name remains exactly where it was. Existing application instances continue reading and writing it, while the new columns simply wait for the next phase.
This is the “expand” part of the expand-and-contract pattern. We are making the schema larger before making it smaller. Nothing has been renamed, removed, or made incompatible.
Making the new columns nullable is intentional.
Existing rows do not have values for them yet, and requiring values immediately would turn a harmless additive change into a migration problem. We will populate them later, validate them, and only then decide whether stronger constraints make sense.
The important idea is sequencing
Deploy the database change first. Once every production database understands both the old and new schema, we can safely begin deploying application code that understands both as well.Phase 2: Dual-Write Old and New Columns
Now that the database understands both shapes, the application can begin writing both.
user.Name = $"{user.FirstName} {user.LastName}";
user.FirstName = firstName;
user.LastName = lastName;The exact implementation will vary, but the principle is the same: every insert or update that affects a user’s name must keep Name, FirstName, and LastName synchronized.
This phase is what allows old and new application versions to coexist. Older instances can continue reading Name. Newer instances can begin working with FirstName and LastName. Because both representations are written together, neither version sees stale data.
There is a subtle sequencing requirement here
Start dual-writing before you start reading from the new columns. Existing rows have not been backfilled yet, so FirstName and LastName may still be NULL. Dual-write only guarantees that data changed from this point forward is correct in both representations.You may be tempted to solve this with a trigger.
That can work, but I generally prefer putting the synchronization in the application when possible. The application understands the semantics of the change, keeps the migration logic visible, and makes it easier to remove later. A trigger can be useful when multiple applications write directly to the table and you cannot update all of them at once.
You may be tempted to solve this with a stored procedure.
That can work well, especially if all writes already flow through a stored procedure. In that case, updating one database boundary may be simpler than changing every caller. The procedure can accept FirstName and LastName, continue populating Name, and preserve compatibility while applications migrate.
The downside is similar to any abstraction introduced only for a migration: it becomes another layer that has to be deployed, understood, and eventually removed. I would use it when stored procedures are already part of the application’s write path, but I would not introduce one solely to avoid a straightforward application change.
At the end of this phase, every new write is safe for both the old schema and the new schema. The historical data is the only thing left behind.
Phase 3: Backfill Existing Rows
Now that every new write keeps both representations synchronized, we can turn our attention to the rows that already existed before the application change. The goal is simple: populate FirstName and LastName from Name without interfering with normal production traffic.
UPDATE dbo.[User]
SET
FirstName = LEFT(Name, CHARINDEX(' ', Name + ' ') - 1),
LastName = LTRIM(SUBSTRING(
Name,
CHARINDEX(' ', Name + ' ') + 1,
LEN(Name)
))
WHERE FirstName IS NULL
OR LastName IS NULL;Real names are messy.
The parsing logic here is intentionally simple for the example. Prefixes, suffixes, compound surnames, single-word names, and cultural naming conventions can make splitting a name surprisingly difficult. In a real migration, this transformation deserves its own validation and may not be practical in T-SQL.The more important production concern is how much data we update at once. A single large UPDATE can hold locks, grow the transaction log, and create unnecessary pressure on the system. For a large table, backfill in batches.
WHILE 1 = 1
BEGIN
UPDATE TOP (1000) dbo.[User]
SET
FirstName = LEFT(Name, CHARINDEX(' ', Name + ' ') - 1),
LastName = LTRIM(SUBSTRING(
Name,
CHARINDEX(' ', Name + ' ') + 1,
LEN(Name)
))
WHERE FirstName IS NULL
OR LastName IS NULL;
IF @@ROWCOUNT = 0
BREAK;
ENDThis is one of the benefits of dual-write happening first. While the backfill works through historical rows, every new insert or update is already maintaining the new columns. The migration is moving forward without requiring the application to stop.
At the end of this phase, old rows and new rows have the same shape. The database now contains both representations for every user, which means we can safely begin changing how the application reads the data.
Phase 4: Move Reads to the New Columns
At this point, every row has values in FirstName and LastName, and every new write keeps those columns synchronized with Name. Now the application can begin reading from the new schema.
SELECT
Id,
FirstName,
LastName,
Email
FROM dbo.[User];This should still be treated as a deployment phase, not a cleanup phase. The Name column remains in place because older application instances may still be running, and other consumers may still depend on it.
The safest approach is to deploy the read change gradually and verify that nothing breaks. If your application supports staged rollout, canary deployment, or feature flags, this is a good place to use them. The database now supports both shapes, so the application can move forward without forcing every consumer to change at the same moment.
Once all supported application versions are reading FirstName and LastName, the primary application no longer depends on Name.
You might be tempted to remove read permissions from the old column.
That can seem like a useful way to flush out anything still depending on Name, but in production it turns discovery into an outage. Reports, jobs, scripts, or older application instances may still be reading the column.
A safer approach is to observe usage, update known consumers, and leave the old column readable until you are confident nothing depends on it. Permissions are better used to enforce the final state than to test whether you missed something.
How to observe usage?
Query Store is a good place to start because it can reveal recent queries still referencing the old column. Application logs, database logs, dependency metadata, and temporary Extended Events can also help identify active consumers. None is complete by itself, so combine approaches and use the tooling appropriate for your environment.Don’t forget about reports
Your application may not be the only thing reading this table. Reports, ETL jobs, exports, scripts, scheduled jobs, notebooks, and downstream services may still reference Name.
Before declaring the old column obsolete, search for those dependencies and give their owners time to migrate. This is one reason the phased approach matters: keeping Name available costs very little, while removing it too early can break consumers you did not know existed.
At the end of this phase, reads have moved to the new schema, but writes still maintain both representations. That gives us one more compatibility window before we stop maintaining Name.
Phase 5: Stop Writing Name
By now, the application reads from FirstName and LastName, and we have given other consumers time to move away from Name. The next step is to stop maintaining the old representation. Remove the dual-write logic so inserts and updates write only the new columns.
user.FirstName = firstName;
user.LastName = lastName;At this point, Name becomes stale by design. That is okay because nothing should depend on it anymore.
This phase is another useful checkpoint.
Do not remove the column yet. Leaving Name in place for a while gives you time to confirm that no application, report, job, or downstream process unexpectedly starts failing once the old value stops changing. The important distinction is that Name is now deprecated, not deleted. We have stopped investing in its correctness before taking the irreversible step of removing it.
At the end of this phase, the new schema is the only schema being actively maintained. The old column remains only as a compatibility buffer while we validate that the transition is complete.
Phase 6: Validate and Remove the Old Schema
Congratulations! At this point, the migration is functionally complete. The application reads and writes only FirstName and LastName, and Name has been left in place long enough to prove that nothing still depends on it.
Before removing the old column
Before removing the old column, validate the new state. Confirm that expected rows have values, investigate any unexpected NULL values, verify that reports and downstream consumers have moved, and add any final constraints or indexes that belong on the new columns.Once that validation is complete, the old column can finally be removed.
ALTER TABLE dbo.[User]
DROP COLUMN Name;This is the “contract” part of expand-and-contract. We expanded the schema first, allowed old and new versions to coexist, moved writes, backfilled data, moved reads, stopped maintaining the old shape, and only now remove it. The important point is that dropping the column should be boring. By the time you execute this statement, nothing should notice.
The Pattern Is the Point
This example is intentionally simple, but the pattern scales. Expand the schema first. Let old and new representations coexist. Move writes, move data, move reads, validate, and only then contract the schema. The individual SQL statements are easy. The discipline is in the sequencing.
The post Advocating Uptime: Schema Changes in 6 Phases appeared first on Azure SQL Dev Corner.
