Why You Should Script your Application's Database Changes

It's easier to not do this, so why do it? This argument seems to be alive, for some reason, more frequently than I thought. What if you don't script your database schema as well as data, each in separate scripts? What can you not do if you just keep one database that everyone mucks with, and then try to capture its changes when you deploy v.next? Here are some of the things you will not be able to do:

• Version control - your database structure, system data, and stored procedure and function logic constitute a bona fide layer of your application. You will not be able to track changes or easily roll back to a previous database version if you don't use version control of some sort.
  
• Associate a schema version with an application version - if you can version-stamp your schema and therefore identify it, then your application has a known-state it can check and know if it needs to degrade certain functionality, or upgrade itself, or terminate with a warning. The alternative (and unfortunately more common) approach is to just crash once a breaking schema change is encountered at runtime.
  
• Cleanly maintain separate copies of development, unit test, performance test, QA and actual data. Each of these are for completely different purposes. Yes, schema changes require changes to these scripts and so are a pain in the ass, but if you can't purpose your data to serve specific testing scenarios, you are not testing very thoroughly. And if you're not testing thoroughly and in an automated fashion, that is a bigger pain in the ass.
  
• Automate your build process completely - you can automate almost all of it, but if creating database change scripts is a manual, time-consuming process, you are defeating many of the benefits of continuous integration.
  
• Deploy the latest database changes to your local "sandbox" database server - why send emails to your team saying, "I'm about to make a breaking change to the database, so go get lunch or something and try back in an hour." Much better to allow anyone at any time to grab a database to-go and install it locally. You can test changes there, not crash everyone else if you screw something up, and still be able to go back to a known good state if needed.
• Deploy the latest database changes to the real world - the latest database is defined by your change scripts, and upgrading a client's database is relatively simple and can be tested in-house to iron out the small problems. Better this than waiting for Bob to come in and spend three hours scripting things manually and wondering if it's really good to go.
  
• Compare the effects of historical changes on performance - did that index really speed up your queries? Slow down your inserts? And who created that table and forgot to index that column?
  
• Search and replace column names - not always so easy, but if you have some standards, it can be pretty simple and is a powerful capability.

So what is easier right now? Piling it all into one database that all developers and testers bang on, and figure out change and deployment scenarios later. What is easier in the long run? Not being lazy and investing some effort into configuration management today so tomorrow is much less painful.