Who Needs Change Management?
By: Greg Robidoux, Edgewood
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.”
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?
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
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
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?
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!
mistakes – Take a look at past
mistakes and how a process like this will eliminate the issues from
- Management – Find someone above you that will champion the
cause and take it to the next level.
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.
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.
Management for SQL Server Presentation by Greg Robidoux
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 and is currently the Vice Chair of the PASS DBA
Special Interest Group. He has 14 years
of IT experience and has been working with databases for the last 10 years with
the past four years of that with SQL Server.
Greg’s primary areas of focus are setting standards, disaster recovery,
security and change management controls.
In addition to these areas he has experience with replication, storage
areas networks and SQL Server upgrades.
Greg can be reached at firstname.lastname@example.org.
Edgewood Solutions Overview
Solutions is focused on
supporting the Microsoft SQL Server platform and delivering database solutions
to further enhance the investments companies have already made based on this
product platform. We recommend and
implement processes that are often overlooked, but should be part of every
database installation. In addition, we
have partnered with the following companies that we feel enhance the SQL Server
platform: Lumigent, DBAssociates
and Precise Software Solutions.
Our employees have taken an
active role in the SQL Server community.
We are members of PASS and one of our employees is the Vice Chair of the
PASS DBA Special Interest Group. We also
had the distinct opportunity to provide two presentations at the recent PASS Summit in Seattle, ‘Change Management for SQL Server’ and ‘Successful
Project Management for Database Administrators’.
Solutions SQL Server
Analysis and Tuning
For additional information
about Edgewood Solutions visit www.edgewoodsolutions.com.