Its all about the end result

On Sunday night I was implementing a significant change release on our main production environment and thought after after implementing numerous implementation and migration projects over the years that a blog on the mechanics of change control could be a great help to people in their jobs, be it Developer, DBA or Project Manager. I hope there’s something in here for everyone.

• Before any implementation project is considered you should have change control and source control in place. Remember the old adage failing to prepare is preparing to fail.

 

• Document, document, document. All areas of the implementation should be documented. This should start with the request for change document, from this a technical change document should be created. Once the change has been made and documented in the development environment you need to follow it up with a release plan. Possibly the most important but often overlooked document is the testing plan.

 

• You must have an extensive testing plan. Your test plan should involve unit testing each piece of code or data that is being changed. For example if your code is inserting data into a lookup table what happens if that value already exists? Will this break your application? The chances are that a duplicate value will cause significant problems. If you are able to change the schema think about adding a unique index so the insert will fail, if you are not able to make schema changes then the code should check for the existence of the value prior to inserting the data.
  Make sure that you have end users testing your system and that they sign off the changes have worked successfully, some companies call this User Acceptance Testing (UAT) whilst other call it Quality Assurance (QA) whatever the name it is a vital piece of work which can save hours of headaches post go live. If the end users are not happy, help them! Thomas LaRock recently wrote an entry on his blog about making this right. In many companies I.T. is a supporting service, others call this cost centre’s so it’s important to ensure that your customers are happy with your service, it’s not enough to say “that’s what you asked for” or “I just ran the scripts I was provided with”.

 

• Never ever test your implementation on your live environment. You should always have an environment where you can run a test implementation. Ideally you’re implementation environment should be as up to date as possible so you have an indication of how the data will be affected in the live environment. Chances are that this environment will not to the same spec so it will not be as fast as in the live environment, it will however give you a time that the task should not exceed. You may even want to run a benchmark test with some queries that way you can extrapolate out the times to give a clearer indication of the time it will take to do each task.

 

• Consider what activity will be happening on the server during the implementation window.
  Are there any scheduled jobs that will occur during this window? Does your business require these to run at this time, can they be stopped or rescheduled? In an ideal world you would have no user access during your implementation however in this day and age a lot of companies have their own websites and one day you will find yourself implementing a change on the website’s database. Build a business case for having a maintenance window where a holding page can be put up on your website. Buy in from senior management is especially important here. If you simply cannot stop your website than make sure you have a test website connected to your test implementation environment (or at least the simulated commands) so you can look at the kind of blocking that you may face when implementing on your production environment.

 

• Consider the amount of data that is going to be produced during the implementation.
  Will you need to resize your database to prevent auto growth? Think about both the data file AND the log file.
  If you are using the FULL recovery mode how big are your transaction logs going to be? Will you have enough space to cope with the extra transactions on your backup drive? Would you gain from moving to BULK LOGGED mode?
  If you are log shipping will your destination drive have enough space for the increased size of the log files? Don’t forget that with SQL Server 2008 Enterprise Edition you can compress your log files.
  If you are using replication do you need to resize the distribution database accordingly?

 

• Rollback plans. You should never implement anything without having a way to rollback to the previous correct state. In some scenarios this may be as simple as taking a backup prior to the changes and restoring the database should anything have gone awry. For most of us however there will be activity on the server whilst we are implementing code. So you should think about auditing data changes and creating rollback scripts for each procedure/function that has been modified. Don’t forget that testing your rollback is as important as testing the implementation. Where would you be if your last backup wasn’t valid? I actually work across the road from a job centre which acts as a constant motivator for me!
  Knowing when to implement your rollback scripts is of paramount importance, before running any scripts at all you should discuss with the process owner the potential outcomes and ask them for their views. In other words you should set your rollback threshold levels prior to implementation. On the night there should be no need to make any major decisions as you should be fully prepared, you should however make sure that you have contact details for all the major members of the project team.

 

• Relax, implementations take time so make sure that you have everything that you need. You’re likely to be doing this at some unsociable hour so make sure you’re in the right state of mind. Don’t relax too much to the point of complacency as you will make mistakes. One thing a colleague did was to play a practical joke on another colleague whose favourite football team had taken a battering:

  
  It certainly lifted the mood in the office!