Have you ever deployed a new version of an application, only to discover that the older version suddenly stops working because of a database schema change?
This can happen when old and new versions of an application run at the same time during a deployment. For example, renaming or removing a database column may work perfectly with the new version but cause errors in older application instances that still depend on it.
This is where the expand-and-contract approach becomes particularly useful. Instead of making breaking database changes immediately, we introduce changes gradually, allowing both application versions to work during the transition.
In this article, we’ll explore how the expand-and-contract approach works, how to apply database schema changes safely, and how to reduce the risk of application failures during deployments.
Map the consumers before changing the table
The main application may not be the only consumer. Reports, scheduled jobs, exports, integrations and support scripts can depend on the existing column.
For a hypothetical customer table, suppose DisplayName will replace LegacyName. Record which components read or write LegacyName and how they are deployed. A successful application build does not prove those other consumers are ready.
Expand with a compatible addition
Add the new column in a way that the old application can tolerate. Assess locking and execution time for the actual table size before running the change.
Next, define how new writes populate the two representations during transition. If old instances continue to write only LegacyName, simply deploying new code that writes both columns is insufficient. You need a deliberate compatibility strategy, a controlled write transition, or reconciliation that accounts for those older writers.
Do not introduce a trigger casually to hide this problem. A trigger becomes part of the write path and requires its own correctness, performance and recursion review.
Backfill with a restartable process
Populate the new representation for existing records in manageable batches. Record progress and make repeated processing safe. Monitor transaction-log growth and workload impact.
Be careful about concurrent writes. A backfill that copies an old value after a newer update can overwrite the intended result unless the process has a reliable rule for ordering and conflict detection.
Validate more than a row count. Compare meaningful values, identify unresolved exceptions and check the exact cases that motivated the schema change.
Switch readers and observe
Move application reads to the new representation after the compatibility requirements are met. Define how the application behaves if a row is not yet migrated. A fallback may be appropriate during transition, but it should have an owner and a removal condition.
Track whether any known consumer still reads or writes the old column. Give periodic reports and infrequent jobs enough time to run before concluding that the old structure is unused.
| Stage | Evidence required before moving on |
|---|---|
| Add new structure | Old application behaviour remains valid. |
| Populate and synchronise | Existing and concurrent data are handled correctly. |
| Switch consumers | New reads and writes pass functional tests. |
| Remove old structure | Remaining dependencies and rollback needs are resolved. |
Contract only when the transition is complete
Removing LegacyName is a separate change. It may prevent rollback to an older application version, particularly after new data can no longer be represented in the old schema.
Write down that point explicitly. A backup is useful, but restoring it may lose later writes and is not equivalent to an effortless deployment rollback.
Rehearse the transition using realistic data volume and mixed application versions. Include an interrupted backfill and a failed deployment in the exercise.
Schema evolution is a coordination problem across data and consumers. A compatible intermediate state gives the team room to complete that coordination without requiring every component to change at exactly the same moment.
Technical references
Continue learning
For practical work across SQL Server maintenance, performance, security and troubleshooting, explore my course Essential SQL Server Administration Tips (Hands-On Guides) on Udemy. Review the curriculum and preview to see whether it fits your next learning goal.
Read Also
- The “Why,” “How,” and “What” of Infrastructure as Code (IaC)
- SQL Server Database Design Best Practices
- Tip of the Week No.18 – Benefits of Using Database Stored Procedures
Stay connected
Subscribe to the GnoelixiAI Hub newsletter on LinkedIn for practical insights on AI, data and technology.
Watch more on YouTube: SQLNetHub TV and my personal channel.
Originally published on SQLNetHub.
© SQLNetHub
Artemakis Artemiou is a seasoned Senior Database and AI/Automation Architect with over 20 years of expertise in the IT industry. As a Certified Database, Cloud, and AI professional, he has been recognized as a thought leader, earning the prestigious Microsoft Data Platform MVP title for nine consecutive years (2009-2018). Driven by a passion for simplifying complex topics, Artemakis shares his expertise through articles, online courses, and speaking engagements. He empowers professionals around the globe to excel in Databases, Cloud, AI, Automation, and Software Development. Committed to innovation and education, Artemakis strives to make technology accessible and impactful for everyone.