Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Worst Practices - Making On-The-Fly Changes

By Steve Jones,

Worst Practices - Making Changes to Live Objects "On The Fly"

Introduction

This is another of those "everyone has done it" items that is a worst practice. It is a practice that is also extremely difficult to break people of once they have had some success with it. However, sooner or later, this practice will come back to haunt you, usually once you have started to work with a large scale or mission critical project.

Before I go further, let me define in more detail what I mean. A "live object" is one on a SQL Server that is being used in a production or "live" system. This is in contrast to a test, development, or QA system. On a production system, real work is being done by users, clients, customers, etc. A good example of a live system would be the database that is used to drive your company's website. Or if you don't have one, the database that displays the recommendation and takes orders at Amazon.com.

It is this database that must work, must be available, and is used in furthering your company's business. It may be an internal-use only database, like the one my friend runs which handles the workflow in a factory. It is, however, important, and mistakes, bugs, and errors here are visible and job-threatening.

It's stupid and reckless to make changes to a live database without testing them. I've been doing this for many years and seen many things done that don't cause issues. However I've also had more than my fair share of "simple" changes that had some unforseen effect and caused downtime.

Some of the more interesting things I've done or seen done:

  • Running a script that dropped and then created a stored procedure. One minor problem: There was a typo in the stored procedure code so the procedure was dropped and not rebuilt. The website was down until the typo was caught.
  • Someone wanted to test a change to a table, so he copied the table, made the change to the live database, and when it didn't work, renamed the copied table. Surprisingly no one had rights to the "renamed" table. Nearly an hour of downtime until someone tracked me down to "fix" the permissions.
  • Making a "simple" change to a trigger to audit activity. Nothing more than adding a "insert into audit_table select * from" line. The catch? The select was from the base table instead of the inserted table. Surprisingly the audit table grew rather quickly and grew the database until the disk ran out of space.

Change Management

I've seen this term bandied about more often than I've ever seen a process implemented. Even in my small company positions where I have substantial influence, it's more often than not that change management is a theoretical concept, not a practice.

Slowly, I've started putting more emphasis on managing change and I have made headway with my supervisors and upper management. It's really a simple thing and one that doesn't have to take lots of time. Even in the smallest companies I've had test servers. It's a question of using them.

Simply put, change management in SQL Server involves making a copy of your production environment and restoring that in a lab or QA setting. Then applying your changes and ensuring that they have the desired effect.

That's it. It's that simple.

Something as easy as a permissions change could easily be tested and verified. Something as complicated as a schema change takes more work, but the process is the same.

Conclusion

Not having some process in place, formal or not, to manage change is a recipe for disaster. There's no excuse and no reason why one cannot take some time to be sure that what you wrote/scripted/coded/whatever does that you intend it to do. We've all made mistakes and making them on a live system is inexcusable. It's truly a worst practice.

As always I welcome feedback on this article using the "Your Opinion" button below.

Steve Jones
©dkRanch.net January 2003
Return to Steve Jones Home

Some of the other "Worst Practices" articles:
Total article views: 7742 | Views in the last 30 days: 1
 
Related Articles
ARTICLE

Worst Practices - Encrypting Data

Continuing with the Worst Practices Series: Steve Jones examines why encryption in the database is a...

ARTICLE

Worst Practices - Assigning Users Rights

Continuing with Andy Warren's series on Worst Practices for a DBA, Steve Jones joins in this week wi...

ARTICLE

Worst Practices - Making Databases Case Sensitive (Or Anything Else)

Article number four in this popular series continues exposing Worst Practices! This week Andy contin...

ARTICLE

Worst Practices - Blank Passwords

Not even worth talking about, right? Probably, but they still exist. Read about this worst practice ...

ARTICLE

Worst Practice - Triggering External Events

Andy Warren started his worst practice series some time ago with the intention of looking at the wor...

 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones