For the majority of people databases are “just” a place to store and retrieve data from. Many people realize only in cases of hardware failures and/or other unplanned outages, that a software component named “database” exists and that this component prevents the systems from working. Recent evidence of this mindset can be found here and here.
However, it is not the intention of this article to discuss what went wrong in these cases. That is clearly a different issue.
Just like applications, most databases evolve over time in-line with the latest business requirements. Changing the database component therefore is (or better: should be) an integral and crucial part in every application life cycle and I’ve realised that this part is not given nearly as much of attention as it should.
The purpose here is to introduce you to database change management as it is handled in our workplace.
Why do you need Database Change Management?
Database Change Management is not only of concern to bigger corporations, but basically to every company who is building its business on the availability of some data. If you are living on the wild side and change objects in production databases on the fly, you can just as well stop reading this article, because you would waste your time. However bearing in mind that most businesses rely on databases in one way or the other, I realise that Database Change Management must be treated in the same manner as client application change management.
But what do you really need for some kind of Database Change Management?
Here are a couple of starting points:
- A formal release procedure that is understood and agreed upon by the various involved teams within an organization. Meaning it must be defined, documented, and approved.
- A well defined implemention plan.
- A well defined back-out plan.
- A well defined escalation path.
Business Continuity is among the biggest concerns for Database Change Management. If your company loses business or is unable to operate in any other way because of the effect of a database change, guess who they will sacrifice right in front of the main entrance? So, in a way by establishing such a workflow you cover your own back and that should be more than enough of a motivation. Still not convinced? Then have a look here. Now, I’m not saying that every single database change bears the risk of putting your company out of business. But as one step leads to another…
What is Database Change Management?
What exactly now is “Database Change Management”?
For this article Database Change Management is defined as the process of making a change to an object in a production database. This process obviously starts out with coding the change, continues with testing and finishes with rolling out the change to the production environment.
It is worth noting that I’m only outlining “standard changes” here. These are changes that are planned ahead and follow well-defined and established paths’. There are other types of change, of which “emergency changes” might be those that may immediately spring to mind. Changes falling into this category require immediate action in response to a production outage. I will describe this type of change in a separated article.
Who needs Database Change Management?
This is a question with an easy and concise answer: Everyone who either provides this “database” service to some clients or consumes and builds his business upon this service. Well, in the first place Database Change Management is IT-specific, but as we will see later on, there are definitely some overlapping areas where the business users needs to be involved as well.
Having such a workflow in place won’t make you a better developer nor does it prevent disaster from happening, but to the very least it gives you the good feeling of having conformed to such a standard and everyone knows what to expect when a change is about to take place with defined escalation paths in case something goes wrong.
Some background information
Let me give you a little bit of background on my work environment: I’m working in the IT department of a large financial institution. Within the IT department I’m working for the Rapid Application Development team. We develop and maintain databases that are used by internal customers. For the most part there is no project manager, business analyst or the like between us and the business user. So, we’re in direct contact with the end user, which you can imagine is challenging at times.
The “database services” we provide to our users are essential for them. Meaning, they are to a large degree unable to operate without them and thus are very keen on a smooth and reliable change management process. Though they do not really care about the details of the process, of course.
I think you need to know about my environment to understand the Database Change Management workflow I’m about to outline in this article. This workflow is certainly different when you work for a software vendor or when you work for more classical waterfall oriented model with 6 month release cycles and a bunch of ITPL’s, analysts, between you and the end user. Still I think our database release workflow can be adapted and/or modified to fit into your particular environment, be it Agile or Waterfall.
For us Database Change Management is an aspect of a general (database) developers’ attitude towards his profession. It’s all about discipline and solid craftmanship. There is little room in database development for the next generation cutting edge application development hype. I don’t question that application change management is just as challenging, but I believe changing a database requires a different mindset than changing an application. And that mindset can probably explained best with: “Am I paranoid?”.
Having that said, here is now how we handle Database Change Management:
Step 1: Keep track of what you are doing
This means, whenever you get a requirement summarize it in your own words along with some more information of interest such as who requested it, who approved the requirement, the expected release date, etc… Ideally this is done in some small application that can be shared across your organization.
Why keeping track? Here are some reasons:
– By writing it down in your own words there is a good chance that you start thinking about what you are supposed to do. And it might be every now and then that you do not agree with a requirement at all or with the way it is supposed to be implemented. Don’t get me wrong but this happens every so often when you work with end users directly.
– Check your paraphrasing with business. This will clarify the requirement and probably refine it so that both parties have a clear agreement on the deliverable.
– For your own history so that you can document on which code you have spend how long.
– In your code you don’t have to write down the complete requirement again or any other lengthy explanations or comments on the general purpose of that particular bit of code. Instead you can refer to your tracker in a one-liner such as
CREATE PROCEDURE dbo.DoSomeWhizzyStuff
AS
/* Some intelligent short comments
History:
<your tracking application identifier 1> 10.01.2009 Frank Kalis
<your tracking application identifier 2> 14.04.2009 Frank Kalis
*/
SET NOCOUNT, XACT_ABORT ON;
...
Now anyone who is interested can look up that tracking application identifier in the tracking application to read more about this change and the SQL code is not bloated any longer more than necessary. Also, we tend to give the source file for a change the same name as the tracking application identifier so we can easily find it in the filesystem whenever we need to. Furthermore it gives you the way-back history for your code. Without having to restore the database, you can easily see when the code changed and also what it looked like before that change by opening the previous file in the list from the filesystem. Translated to the above example, we see that there have been 2 changes made to the dbo.DoSomeWhizzyStuff stored procedure. The first one on the 10.01.2009 was the initial implementation of the procedure. Thereafter the stored procedure has changed only once on the 14.04.2009.
A word about the filesystem: We create the source files in our work folders. Whenever they are ready to be deployed they are moved from the work folder to the release folder. From the content of this release folder we create the release scripts. More on this later on.
Once the release is done, the source files are moved to an archiving folder which contains all completed items from the tracking application. Arguably these “completed” files could be under supervision of a source code version control system, but we have decided against this because we keep the change scripts under such a control and because these change scripts implement the content of the source files there is no need to do things twice. More on using a source code version control system later on.