A community of more than 1,600,000 database professionals and growing

I happen to be a fan of database migrations as a way of making and deploying database changes. This is an approach that tracks each of the scripts run by developers in their working environments and the replays these scripts in production to deploy the changes. It works really well, and is the most bulletproof method I know of for ensuring the changes will work in production. That's not to say there aren't issues, but it's the approach I favor. It's a part of what SQL Change Automation from Redgate Software does, and it's also what Microsoft and other companies see as the future of database deployments.

This is in contrast to making changes in development and then using some technology such as SQL Compare or Schema Compare in SSDT to create a script and use that to deploy changes. That works very well for many people, but I do find that most customers outgrow the technology with certain changes that don't lend themselves to this state or model based method of script generation. 

In either case, rollbacks are a concern for many DBAs and developers. After all, the database is a stateful service, as our data must be maintained over time and there are certain changes that are difficult to rollback. While many developers that might have renamed an entity or added a column might be tempted to just reverse the change, this isn't always easy to automate, especially in most of the tools we use. Often we depend on the skill of a particular individual to manually execute the reversing DDL, and possibly determine what to do with any data that was changed.

I ran across another developer that things migrations are a better way, and that in a year of development, they never had to roll back any changes. While I agree that migrations is a more reliable process, I do think that assuming you'll never roll back a deployment is highly optimistic. I've had tales of application developers being out of sync, of other applications not working with a new version of the database, and more. Some of these might be fixed with a quick roll forward, but ultimately I think that there needs to be an easy way to make rollbacks normal.

The one downside of migrations is that the reversing transactions can be complex, since each migration script might make complex changes moving forward. There isn't much help from most migrations tools, whether that's SQL Change Automation, FlywayDB, Entity Framework migrations, and more. These tools can certainly generate reversing code for simple changes, but any sort of complex alteration requires custom code.

My view is that a few simple rules govern how I view rollbacks. For views, procs, functions, I'd grab the previous version from our VCS and re-deploy that. I might try to run through a DevOps pipeline, but if I were in a hurry, I'd grab the code and run it. I'd also then recommit the old version as the latest one. For tables, we should write reversing migration scripts for any entities that are risky. Risky meaning this might affect my employment status. I'd be sure I had a second deployment pipeline that I could use to run these scripts in QA, staging/pre-prod, etc. after we'd verified the code we were deploying. I'd then have checks to ensure we really were reversing the changes.

The last rule I have is that I use time to make deployments easier. I would never add new columns and drop old ones in the same deployment. If I move or change data, I'd always ensure the old versions of data remained. That way I could reverse changes without problems. I can always do cleanup in a later deployment that just removes objects or data, but I want to be sure that old data is really no longer needed. That means I need to be organized and have a good calendar system to scheduling future cleanup work as well.

Migrations are really a better way to do database deployment, whether you're working in a relational system, or you might be altering documents in a NoSQL system. Replay the changes you've made in development, that you are sure worked on "your" machine. You'll be more confident they'll work on another machine.

Join the debate, and respond to today's editorial on the forums

The rows is inserted, and a NULL is used for any sparse columns not included in the XML document.

Ref: Use Column Sets - click here

» Discuss this question and answer on the forums