How do you get your database schema changes live on your production system? As your team of developers and DBAs work on changes to the database to support your business-critical applications, how do updates wend their way through dev environments, possibly to QA, hopefully through pre-production and eventually to production in a controlled, reliable and repeatable way?<\/p>\n
In this article, I describe a model we use to try and understand the different stages that customers go through as their database change management processes mature, from the very basic and manual, through to advanced continuous delivery practices. I also provide a simple chart that will help you determine how mature your process is.<\/p>\n
This method of managing changes to the database \u2013 which all of us who have worked in application\/database development have had to deal with in one form or another \u2013 is sometimes known as Database Change Management. And it\u2019s a difficult process, often painfully so.<\/p>\n
Some developers take the approach of: \u201cI\u2019ve no idea how my changes get live \u2013 I just write the stored procedures and add columns to the tables. It\u2019s someone else\u2019s problem to get this stuff live. I think we\u2019ve got a DBA somewhere who deals with it \u2013 I don\u2019t know, I\u2019ve never met him\/her\u201d<\/em>.<\/p>\n I know I used to work that way because I assumed that making the updates to production was a trivial task \u2013 how hard can it be? Pause the application for half an hour in the middle of the night, copy over the changes to the app and the database, and switch it back on again. Voila!<\/p>\n But somehow it never seemed that easy. And it certainly never was that easy for database changes. Why? Because you can\u2019t just overwrite the old database with the new version. Databases have a state \u2013 more specifically, 4Tb of critical data built up over the last 12 years of running your business. If your quick hotfix happens to accidentally delete that 4Tb of data, then you\u2019ll be looking for a new role pretty quickly after the failed release.<\/p>\n There are a lot of other reasons why a properly managed database change management process is important for organizations, besides job security, not least:<\/p>\n We\u2019ve found, at Redgate that we have a very wide range of customers using every possible form of database change management imaginable. Everything from: \u201cNothing \u2013 I just fix the schema on production from my laptop when things go wrong, and write it down in my notebook\u201d<\/em>, to: \u201cA full Continuous Delivery process \u2013 any change made by a dev gets checked in and recorded, fully tested (including performance tests) before a (tested) release is made available to our Release Management system, ready for live deployment!\u201d<\/em>. And everything in between of course.<\/p>\n Because of the vast number of customers using so many different approaches, we found ourselves struggling to keep on top of what everyone was doing \u2013 grappling to identify patterns in customer behavior. This is useful for us, because we want to try and fit the products we have to different needs \u2013 different products are relevant to different customers and we waste everyone\u2019s time (most notably, that of our customers) if we suggest products that aren\u2019t appropriate for them.<\/p>\n If someone visited a sports store, looking to embark on a new fitness program, and the assistant suggested the latest $10,000 multi-gym, complete with multiple weights mechanisms, dumb-bells, pull-up bars and so on, then the store is likely to lose that customer. All he needed was a pair of running shoes!<\/p>\n Introducing the Customer Maturity Framework<\/strong><\/p>\n To solve this issue and attempt to simplify how we understand our customers and our offerings, we built a model. This is an attempt at classifying our customers into a \u2018Customer Maturity Framework\u2019 as we rather grandly term it, which somehow simplifies our understanding of what our customers are doing.<\/p>\n The great statistician, George Box<\/a> (amongst other things, the \u2018Box\u2019 in the Box-Jenkins time series model) gave us the famous quote: \u201cEssentially all models are wrong, but some are useful.\u201d<\/em><\/p>\n We\u2019ve taken this quote to heart with our own model \u2013 we know it\u2019s a gross over-simplification of the real world of how users work with complex legacy and new database developments. Almost nobody precisely fits in to one of our categories, but we hope it\u2019s useful and interesting.<\/p>\n There are actually a number of similar models that have existed for a long time for more general application delivery like these from ThoughtWorks\/Forrester<\/a> and InfoQ<\/a>. Initially we tried just taking these models and replacing the word \u2018application\u2019 for \u2018database\u2019, but we hit a problem. From talking to our customers we know that users are far less further down the road of mature database change management than they are for application development.<\/p>\n As a simple example, no application developer who wants to keep his\/her job would develop an application for an organization without source controlling that code. Sure, they might not be using an advanced Gitflow branching methodology but they\u2019ll certainly be making sure their code gets managed in a repo somewhere with all the benefits of history, auditing and so on. This often isn\u2019t the case for the database. A very large segment of the people we speak to have no source control set up for their databases whatsoever, even at the most basic level like keeping change scripts in a source control system somewhere. By the way, if this is you, one of the most viewed posts on the Redgate blog is An introduction to SQL Source Control<\/a>, which explains what it is, what it does, and the advantages it brings to day-to-day development.<\/p>\n This difference in maturity in database change management is the same as you move into areas such as continuous integration (common amongst app developers, relatively rare for database developers), and automated release management (becoming increasingly common amongst app developers, growing for the database).<\/p>\n So, when we created the model we started from scratch and biased the levels of maturity towards what we actually see amongst our customers. But what are these stages? And what level are you? The table below describes our definitions for four levels of maturity \u2013 Baseline, Beginner, Intermediate and Advanced. As I say, this is a model \u2013 you won\u2019t fit any of these categories perfectly, but hopefully one will ring true more than others.<\/p>\n\n