Step 5: Supply a proper change script
Again, this may sound obvious, but trust me, it isn’t. Just ask your DBA’s and I bet they can tell you some interesting war stories. But what does “proper” mean?
Well, first of all “proper” means, that there is a script that can be executed. Generating such a script is not the task of the DBA.
Next, ensure that this script can be executed by anyone with sufficient privileges just by connecting to the right instance, open the file and execute it. The person in charge of executing it may not need any special knowledge about the code in the script.
It really does not matter how you generate such a script. You can do so either by putting together all the bits on your own or use one of the well-known comparison tools on the market. I’m not paid by any third-party vendor, but in my place we use such a third-party tool and are very pleased with it. It not only saves you precious time, but also greatly reduces the likelihood of missing something.
First code line in your script should be USE <database name>;. If for no other reason, this small bit of code will make your DBA happy, so he does not have to add it himself (usually he will refuse to do this, because it changes the script the developer has originally supplied), or select the database manually from the dropdown-list in Management Studio, or – worst case – get rid of unwanted objects in the master database.
Use the database provided transaction functionality. Usually you either want all of the change to succeed or nothing. So, wrap everything inside transaction handling to make sure the database is in the consistent state as it was before the change when things go wrong.
Create the script to be responsive. That is, use PRINT statements throughout the script along with a final sentence if the whole change has succeeded or failed. Once the DBA has run the script, let him send you a mail in which he just needs to copy and paste this final outcome from the script. This mail you should archive to be able to prove to anyone what happened with that change.
Step 6: Supply a proper rollback script
Nobody is perfect and things can go wrong for countless reasons. But it is better to be prepared beforehand for the worst case than start fire-fighting when the change was done. For the majority of changes that “only” change database objects, but leave the data unchanged, a rollback script is just a roll-back to the state of the objects just before the change. Again, this is a straightforward task when you use a synchronization tool. Just revert the synchronization direction and instead of the change script, you get your perfect rollback script. Apart from this the same remarks as for the change script apply to the rollback script as well.
In changes that require data modification in one way or the other, in many cases there is not much you can do apart from restoring the previously taken backup.
Step 7: Use a source code versioning control system.
Note that this does NOT necessarily mean that you should use such a control system in the way you would use it for application development purposes. Database development is quite different from application development. For client application development it makes sense to have the source code files under source code versioning control management as the source code for an application is usually containing within some files. These files can be locked on the file system level whenever a developer checks them out to do some changes. All the time the source code versioning control system takes care of the file. For SQL Server development all source code is stored in some internal system table(s) inside the database and there is no chance to “lock” the row(s) pertaining to a particular object for the time a developer wants to check it out, do his change, and then check it back in again. So, state it clearly, there is no mechanism in place that prevents a second developer scripting out the object in question, changing the object, and apply the changes to the database, while it is “checked-out” by the first developer. And once the first developer is ready to check in his changes to this object, he will nicely overwrite the changes made by the second developer without any sign of that the code has changed since he checked it out.
Another point is that commonly (not necessarily, but usually) you have one class per file in client application development. And because of the nature of classes being usually more complex with all their different methods and properties the likelihood that different developers are working on different methods and/or properties of same class at the same time is much higher than having two different database developers working on the same database object at the same time. So, all the advantages a source code versioning control system offers for team application development hardly apply to database development and, thus, we simply see no clear advantage in using source control.
However, we use a source code versioning control system to save the change and rollback scripts. That is, whenever we release some code, you can find a corresponding change and rollback script pair in that system. And because we tend to do frequent, but smaller releases in a SCRUM-influenced methodology, we organize these scripts by year of release. So, in folder “2008” all releases from 2008 are placed and so on. But that is up to one’s own preferences and personal likings. This does mean that up to to a certain degree scripts are “re-playable” and reproducable across releases.
Step 8: Post change testing
This is an area that should be paid special attention to. How do you as a developer really verify that that what you have coded is correct, giving the expected results, and performing good?
Ultimately you can get this kind of feedback only from the business users. So, why not go and see your business users if you have the chance to? But what if the business users tell you that nothing works any longer and performance is bad. Before users notice you should at least try to verify the results from calling the changed objects are the same as you would expect to. If you as a developer do not have access to the production system as such (and you shouldn’t!), get someone else from the operations team and some dedicated support staff to run the changed code parts. If possible, right after the change and before the business user start working with the system.
Step 9: Stand by for any eventualities and be available when things go wrong
Are you as a developer done when your code is in place after the release? No!
You do not have to act as first line support (unless your job specs say otherwise, of course). However we try to be in before the business users starts to work the day after a major release to do some more basic checks and, honestly, to show our presence. Just in case…
But what to do when things really go wrong? Well, nobody is perfect, no software is bug-free and so errors just happen every now and then. If such a bug is “just” a small bug in a procedure or function that leaves the data unaffected, you can easily roll-back that particular object to its previous state. Either by running the whole rollback script or by selectively rolling just this object back, if possible. This can only be evaluated on a case by case basis. If, however, the bug is “a more severe” one that affects data, communicate this as soon as possible to the contacts defined as the escalation paths. Then it is up to you all to decide how to proceed. If you see a chance to fix this issue within a reasonable amount of time, leave the decision up to the business if they are willing to give you that time or instead go straight for a restore. Obviously and in order not to make things even worse you’re better off giving a realistic estimation on the time it takes you to fix the issue. Don’t promise something you can not keep. It might very well be that your job is on the line at this moment. When in doubt, I would always go for a straight restore rather than getting back to the business to say that I’m unable to fix it although I’ve promised, tried, and failed. In any case the damage is done, but I think it still is better to be at least honest.
Conclusion
Database Change Management is nothing to be taken lightly. It is also no rocket science.
It is just plain solid craftmanship once you have found your own way of handling it. The more thought you put into developing your own strategy and the more care you take applying this strategy, the more trustworthy you become in the eyes of your customers and your own management. And that is certainly a huge plus.
The only question now remains is not “Am I paranoid?”, it’s “Am I paranoid enough?”
Acknowledgements
Many people have been involved directly or indirectly in the creation of this article. However a very special “Thank you” goes out to my coworker Shawn Crocker who introduced me to real-world database development and still is keeping an eye on me.
Actually the whole process outlined in this article was developed, implemented, and established by him in our workplace. So, he really deserves all the due credit for fighting endless battles with users, change managers, senior managment, etc… Cheers! 🙂
]]>