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
Some of the other "Worst Practices" articles:
Worst Practices - Part 1 of a Very Long Series!
Worst Practices - Objects Not Owned by DBO
Worst Practices - Not Using Primary Keys and Clustered Indexes
Worst Practices - Making Databases Case Sensitive (Or Anything Else)
- Assigning User Rights
- Depending on the GUI
- Sorting By Ordinal
- Bad Connection Strings and Bad Info in Sysprocesses
