You’ve spent thousands of dollars on that cool technology; clustering, redundant controllers, redundant disks, redundant power supplies, redundant NIC cards, multiple network drops, fancy tape backup devices and the latest and greatest tape technology. You’re all set. There’s no way your going to have downtime.
But one day something does go wrong, is it the disks, no way you’ve implemented RAID with hot swappable hard drives, is it the server, can’t be you’ve got your servers clustered and any downtime due to failover would be so small that hardly anyone would even notice it. Well if it’s not your stuff it must be the network. Those guys are always making changes and not letting you know about it until there’s a problem. No, checked with them, no changes. What’s left? It must be the application. Once again that application group rolled out a new version and no one informed the DBAs. Once again foiled, the application team says no changes went out.
A little investigation on the database and you’ve noticed that some of the create dates on a few stored procedures have yesterday’s date. I guess they could have been recompiled, but you didn’t do it. You take a look at one of the procedures and low and behold someone was kind enough to actually put in comments. It was Dave, one of the DBAs in your group and guess what he’s on vacation this week. It turns out he created a scheduled job to run this past Sunday and forgot to let you know about it. He changed a bunch of procedures for a load that occurs each month. You don’t know much about the load except that it runs and he is responsible. You have no idea why the change was made and if you undo the change what affect it might have. To make things worse you try to find the old code, but you can’t find it anywhere.
The heat starts to rise as the phones keep ringing with people wondering why the database is not responding. You start to panic trying to figure out what to do. This is a pretty critical database and a lot of people in your company need it to do their job. The last time something like this happened you caught hell from your boss, because her boss was breathing down her neck. Now you wish you were the one on vacation instead of Dave. You take a deep breath and think things through.
You remember Dave sent you an email about the load around this time last year when he went on vacation. You quickly do a search and you find the email. The email gives you steps on how to undo the load and what if any consequences you may face by undoing things. You go to a previous nights backup, do a database restore and script out the procedures. You’re taking a gamble that you’ve got the right procedures, but that’s your only course of action. After five or six hours of user complaints and a lot of sweating you’ve got the database back to normal again or at least you think so. You say to yourself, “I need a vacation and Dave’s dead meat when he gets back.”
The Solution
Have you ever found yourself in this situation? Something gets changed and you don’t know about until there’s a problem. Or someone makes a change and says “Don’t worry it’s a small change. No one will even notice.” I think we have all found ourselves in these situations. The only way to fix things like this is to bolt down your servers and make the users sign their life away if they want to use your server. Not too likely, but it’ll work if you could get it implemented.
I think we need to look for a solution in the middle of the road. Something that works for you as a DBA and something that works for the rest of the world. People just don’t understand how stressful your job really is. You’re the bottom of the totem pole, well maybe the NT Engineers are the bottom, but still you’re pretty close. All types of changes occur outside of your control and the only time you are aware is when something goes wrong.
Well you might not be able to fix the things outside of your control, but you are the DBA, the master of the databases. In order to implement change control company-wide it takes a lot of effort, coordination, and buy-in from a lot of people. But that doesn’t mean you can’t start with your own domain and put control mechanisms in place for the databases. So where do you begin?
Start Simple
For most changes to take affect and have a quick payback, implementing things slow and steady is the way to go. Identify a critical database, kind of like the one Dave screwed up, and start to create guidelines around how you would handle changes for this database. If you try to make a master plan to solve all of the problems, you will never get it implemented. Create a small project plan or a task list of things you need to accomplish and take care of one item at a time. If something doesn’t work, adjust the plan accordingly.
Evaluate and tighten database security
There are probably other reasons why you want tight database security, but have you ever thought about it from a change control perspective? The greatest process in the world won’t fix people from sneaking things into production. You might be able to catch and find out who did it, such as the change that DBA Dave did above, but at that point it’s too late. Take a look at your security settings and determine ways people might be able to make changes to your database that they shouldn’t. Start with the server roles and see who has access that really shouldn’t. Then take a look at the database roles. If you are using the standard database roles, see how you can create customized roles with limited access. Usually when things don’t work due to security access, the fix is to grant too much access. Now it’s your turn to reverse the tide.
Establish a release schedule
Instead of making production changes on the fly, make changes on a periodic controlled basis. If people know that changes are to be made on a set schedule you set, they can adjust their schedule accordingly. It doesn’t mean that you can never put changes into production outside of this schedule, it just means that somebody better have a really good reason why they need something immediately instead of waiting for the scheduled release. This may sound like it will slow down the development process and you know your users need those changes right away, but having a more controlled approach will actually benefit your end users as well. The users will know what’s coming and when it’s coming instead of finding out about a change after something that use to work no longer works.
Document and communicate
It is very important to let people know what you have done or you are planning on doing. Verbal communication is great, but having things written down is even better. Generally when you tell someone something they usually hear what they want to hear and not the whole story. Giving them documentation allows them to later review what you have given them. I know documentation is probably not your favorite thing and it takes way too much time. It’s true, documentation does take time, but this is not because it is hard to do, it’s because most people put it off until the very end. At that point instead of just having to fine tune your documentation you have this mammoth task in front of you. Start small and document through the entire process instead of waiting until the very end. Also, make sure the documentation is meaningful to others. Have someone else take a look and provide feedback. Let them know the documentation is more for their benefit then yours, since you’re the one that implemented the change.
Define roles and guidelines
In order for a change to be affective, people need to know the rules. You can’t just assume that since you think it is a great idea or that the process should work this way, others are going to feel the same way. Have you ever been involved in a company reorg? If so, I bet the one thing that you wanted to know was how it was going to affect you. Well this is kind of the same thing, just on a smaller scale. Define who can do what, your documentation needs, testing, signoffs, handoffs, etc The better the roles are established the easier it will be to determine who is responsible and who needs to fix the problem if something goes wrong.
Always have a back out plan
Whenever you move objects and configurations into production always make sure you have a way to back out your changes even if it is something as small as adding or changing one stored procedure. Your back out plan can be as simple as restoring a backup to having complex back out scripts and a coordinated effort with other key groups (i.e. Applications, NT Engineering, Networking). The back out plan is just as important as your roll out plan. You may never have to use it for an implementation, but the one time you do you’ll be glad you took the time to put it together.
Create a repeatable process
Create a process that you can use over and over again for either the same application upgrade or for future projects. Take a look at all of the documents, processes, emails, etc that you have used in the past and create a set of reusable documents that can be used for future projects. If you can recycle and reuse what you have already put together, it will simplify and streamline your procedures.
You start and then involve others
If you really want to have more control over changes to your database, you need to first look at what you can do to get this done. You can’t keep blaming all those Developers or Users if you haven’t set the guidelines. After you have done your homework, then you can start to involve others. Take a look at the things that you have control over or the things you can bring to the surface that someone has to be aware of and manage. Face it, if you don’t do it, who will?
Convince others
As a DBA this probably sounds good to you, but how do you convince others that will have to change the way they do things? Good question!
- Past mistakes – Take a look at past mistakes and how a process like this will eliminate the issues from happening again.
- Management – Find someone above you that will champion the cause and take it to the next level.
- Find others that want a change – Find other people that see there has to be a better way and get them to join you in your effort.
- Collaborate – Talk to other people in an informal manner. Get feedback from them, so you can address their concerns early in the process. This is a sure way to get them to feel like part of the solution instead of part of the problem.
Summary
It may seem like a daunting task to put a change management process in place for your environment, but it’s not impossible. Many companies are already doing it through years of trial and error. The only way to get a better process in place is to start making changes to your existing process. You might need to just formalize what you already have or you may need a complete overhaul. Whatever it may be, start small and think big.
Resources
Change Management for SQL Server Presentation by Greg Robidoux
Published 01.10.2003 – Greg Robidoux – Edgewood Solutions. All rights reserved 2003
Greg Robidoux is the founder of Edgewood Solutions a database solutions company in the United States focusing on Microsoft SQL Server and he is currently the Vice Chair of the PASS DBA SIG. Greg has 14 years of IT experience and his primary areas of database focus are standards, disaster recovery, security and change management controls. You can find out more about Greg and Edgewood Solutions at http://www.edgewoodsolutions.com.
