Practical Database Change Management (Part 1)

Step 2: Test what you have developed

This may sound all too obvious to mention?
Trust me, it is NOT!

I can say so, because I have found myself guilty of having released code into production several times that has not been carefully tested as it could have been.

Why? Maybe because I missed a test case since I am kind of my own first-line tester? Maybe I thought: Nah, this is only a small change. No need for a full-blown test script?

Even if you like to think of you as a good developer, even the smartest developer does make mistakes and these mistakes will come back to bite you one day sooner or later. Testing code is not for the weak and timid as the Klingons would probably put it.

How do you test your code? Well, first thing has to be that the code provides the expected result. If the code is a stored procedure with parameters, the resultset has to be tested for any possible and reasonable combination of these parameters. Some of my procedures do have a very long list of input parameters and with a handful of combination of these parameters most of the daily business can be done, while some more exotic combinations occur very infrequently. So, building up your own test suite over time with test cases is essential. For example, after being a couple of month in production, one user executed a stored procedure for the first time with a very exotic combination of input parameters and the procedure gave an incorrect resultset back. Nobody notices this bug before because it was “hidden” in some OUTER JOIN condition and was only valid for this combination. Now I have added this sample calls I captured from this user in our user acceptance environment and added them to my test suite for this part of the database along with the expected “correct” resultset. Everytime now I make some changes to this procedure, I just have to open my test script, execute the captured all there and compare the resultsets (I use Excel for that, because it is quite convenient for this task).

Testing does not necessarily have to be full-blown regression testing, but as soon as your database objects are a bit more complex than “SELECT * FROM table”, at least basic testing should take place.

But are we now done with testing? No way!

Once the code is functionally working, it’s time to look at how the code performs. Is it running fast? Is it affecting other bits of code?

One of the things I really like about a declarative language like SQL is, that you can express things in many almost natural language ways and still get the same result. But it is always worth testing out how different formulations of a statement are carried out by SQL Server. They are often not equally evaluated by the database engine. Sometimes you will be really suprised how different the resulting execution plans are. When you have identified the formulation giving you the “best” execution plan, pick that and go for it.

What I tend to do when I have more than one formulation giving almost identical performance, I save all variations down in a test script and keep that script for future review. Additionally I assign myself another tracker in our tracking application to review this bit of code in several weeks time after that code is released.

Now that you have tested the code you have developed is it ready to make it into the production environment? Not quite! Usually you would want to test it under production-like conditions. We have a testing environment which is almost identical to the production environment, including data and server specs. So, whenever we are done with our testings, we push the changes to the testing environment and then ask our testers to “play around” with the changes. We prefer to have “business power users” as testers. Simply because they are familiar with the numbers they work with each day and can tell you almost always right away if the results are plausible. When they are plausible, they dig deeper into the changes and verify a sample of the result for correctness. Otherwise the changes must be corrected in the development environment and the testing phase begins anew.

Once these testers are happy with the changes, it’s time to prepare the actual release to push the changes into the production environment. With business buy-in to the RAD/Agile approach they become partners in the development and testing cycle. This includes acknowledgement and acceptance of the formal testing/sign-off/release trade-offs between RAD/Agile vs. more formal methods.

Step 3: Change Management Meetings

It is obvious that the whole change management process can (and in larger companies usually will) generate its own dynamic and can be complicated as much as you want or as your company thinks it needs to. You can introduce formal change ticket templates, change windows, change freezes, audit trails, and whatever else you can think of. However try to apply common sense here without building up a bureaucratic apparatus on its own. Larger companies commonly have a dedicated change management staff who is responsible for organizing such meetings and the whole workflow as such. But even for smaller companies it should be possible to establish meetings with all involved teams that need to be aware of the change.

At the very least, such meetings should include the DBA’s who are the ones that will carry out the change. They need to know when the change should take place, how long it is estimated to take, what steps should be performed in which order for the change, and  last, but not least, what the change is all about. The more precise this “runbook” for the change is prepared and described, the less room there is for any interpretation from anyone on how to perform the change. Again, this is a safety-net for all involved people and should taken seriously. For example, if your change requires that some import jobs are disabled for the duration of the change, then write it down as a single step in the change runbook. Don’t rely on anyone guessing your intention and the DBA is usually to busy to remember each and every single speciality in every databases he is responsible for.

If it is a coordinated change for the database and client application(s), specify what to change first and what comes then. Establish some communication channel through which the folks performing the change can notify each other on the progress. For example, we use our corporate instant messaging system for such purposes, if needed. We create dedicated “rooms” for major releases, but always it is a 1:1 conversation at least.

Do the DBA’s need to review code before it goes into production?

Good question with no easy answer. I think we have some credit with the DBAs to not screw up releases and to not “betray” them by sneaking in some code that violates company policies. After all the DBAs have a monitoring software in place that finds such violations and reports them, so there is little use in even trying to sneak in some code past them.

Back to the question: When in doubt, then yes the DBAs should review the code before it goes into production. Why? Guess who will be blamed first when things go wrong with the database? In that respect I can understand that the DBAs will (or should) insist of seeing the code before the change if it conforms to their standards or not. And if not, I can understand that a change gets rejected.

Continues…