It seems like every week I get hit up with some kind of “Its a view only for this specific report,” or “It is a simple change that the vendor would have made if they had access” type of request related to SQL Server. If you’ve never dealt with me, let me start off this blog post by saying that I am a staunch advocate of Change Control Processes for production systems, I don’t care how simple you might think the change might actually be. Personally, I submit change requests for even the most simplistic tasks like creating a new index in the database or even changing the frequency of DBCC CHECKDB commands on production servers. Why do I do things like this? Basically put; because you never know!
Early on in my career with SQL Server, I learned the impact that so called “Small Changes” to a SQL Server database could have in a production environment. As a Business Analyst I had elevated access to a production system, which I really had no business having, that I did a significant amount of work on. One of my primary job functions was to prevent bills from going out to customers that contained errors on them, so I created a table in the database to exempt accounts from the billing process by inserting the account primary key into the table, which was then checked during Billing Nomination to exclude those accounts until the associated problem was fixed. So that I had a full history of when an account was added and removed from the table and by who, a audit table was added with auditing triggers for INSERT/UPDATE/DELETE on the exception table.
At some point, someone decided to alter this table and add an additional column to it to make it so they didn’t have to join to the accounts table to get the LDC (local distribution company) account number for troubleshooting problems with the accounts. When this was done, it broke reports that I had written for our CTO at the time because now the column name for the LDC account number existed in two of the tables, so
being young, dumb, thinking that this column wasn’t needed, I mean really how hard is it to write a JOIN, I dropped it from the table. This is where the walls came crashing down.
Being relatively new to SQL there were a lot of things I didn’t know, like for example, SELECT * is dangerous to use, especially in auditing triggers when the table definitions don’t match. When i dropped the column from the base table, I didn’t change the audit table to match, causing the columns to mismatch and the trigger to fail. Now if the only thing that actually used this table was me, stupid assumption on my part, this wouldn’t have been a big deal, but the new account creation process had been recently changed to add new accounts to this table to create a billing hold during the account setup period. This is actually what broke, and it broke very fast causing a cascading effect of problems across multiple systems. It took the DBA and application developers a bit of time to trace this down and when they did I got a phone call asking what I had changed.
Such a small change, such a big mess. The impacts of this solidified for me that there is no such thing as a small change to a production database very early in my career. It took multiple people nearly two hours to cleanup the residual effects of this small change, primarily because a new account manager had to manually reenter the failed new accounts into the system and let them process again. I’d like to say that I never made a change outside of change control processes after this again, but I’d be lying if I did. However, I can say that I haven’t made untested adhoc changes like that ever again, and it has been over two years since I have made any undocumented change to a system. (Well at least intentionally, I have made mistakes and been connected to the wrong server and done seriously bad stuff like dropping a 80GB table which is why I use SSMSToolPack to color code my servers now.)