This editorial was originally published on Apr 8, 2015. It is being republished as Steve is out of the office.

Most people work in evolutionary databases. By that I mean a database where you have some schema, and over time you are altering that schema. You might be adding columns to tables or views, changing stored procedure or function code, or something else, but you aren't really rebuilding the database from scratch on a regular basis. Certainly some people sell software and a database that's always being rebuild and upgraded from state X to state Y, but that's a much more complex issue.

For those of us that work with these evolving databases, we really have two choices in how to work on upgrades. We can store each change to the database as a script and ensure we run the correct scripts in the correct order (discarding those that aren't needed) when we deploy changes. I've done that before, and it can work, but this approach requires I've have good control of production to prevent changes from being made in that environment that aren't also made in development. This is the approach advocated by Paul Stovell, of Octopus Deploy.

The other approach is to look at the state of development at some point in time, compare that to production (with a tool like SQL Compare) and then generate a script that makes the changes needed. This is how lots of people deploy their changes today, though this approach isn't without its own issues. NOT NULL columns, renames, and more can cause problems with this approach. There are ways around these issues, but they require some work.

Ultimately the problem of smoothly deploying changes to databases requires a bit of discipline from the DBAs and developers. Tools can help, and they certainly can reduce the work involved, but good habits and a consistent process are important to ensure that changes are made smoothly. The one thing that helps you find problems with your process and code is testing, which is something I'd recommend you implement no matter what method you choose for deploying your changes.