Ad Hoc changes are not what I like to makeHow many times have you been asked to change data in a production system? If your career has been like mine, it’s probably too many times to count. It seems that there are always data issues in production, and the DBA is asked to fix or correct something. In my mind, there are two many causes of this: user error and code bugs.

In both cases, you would hope that some application contains the feature to allow a user to fix an error: either one they created or one the application slipped in. However in the reality of today’s IT world, too often there are not enough tools to allow an application to be used to correct issues. Just like testing, it seems that too often we haven’t spent enough resources before deployment in an area where they are needed: tools. As a result, it’s time for the DBA to change things.

I saw a blog from Jon Russell recently that pointed out a few things that it’s easy to forget when making these changes. Many DBAs know their systems very well, understand the data relationships between tables, and hopefully, have declarative referential integrity (DRI) in place to ensure data integrity. However it’s easy to forget about some business rule coded in a trigger, or the downstream implications of a data change. That’s was a problem at SQLServerCentral at one point when I corrected someone’s question of the day response, but didn’t realize that their total points are stored elsewhere and needed to be updated separately. In that case I ended up with a lot of data to fix.

We do have to make manual changes to data at times. When we do, Jon’s list of steps to include in the process is important, especially in many companies where auditing is demanded or even required. Keep copies of the old data, document what you did, and most importantly, make sure you’ve sent notifications. Often if an error crops up, it will be noticed by the end-users that usually work with the data. An email from the DBA just might help them find mistakes quicker, and prevent even more problems.

Steve Jones

The Voice of the DBA Podcasts