The Right Connection

Travis-CI had some staffers connect to the wrong database and truncate production tables. Needless to say this caused an outage and disrupted their business. Hopefully they didn’t lose too many customers, but they certainly did not help their reputation. I’m sure there are more than a few customers trying to decide if they continue to trust the company or move their Continuous Integration (CI) processes to another platform.

I’ve done this before. Not shut down a company, but I have actually truncated a production table by mistake. Well, not TRUNCATE, I mean, who runs that. But I have run a DELETE without a WHERE clause and killed a lookup table in a production database. Fortunately I had a copy of the table elsewhere and could rebuild it in minutes. Only a few customers had their work interrupted and only for a portion of our system. The point is that I’ve been a very good DBA, with a lot of success and experience, and I still make mistakes.

Often this type of mistakes comes about because we get busy, and we keep connections open to different systems. When we might be developing code against a schema that is close to production, it’s easy to forget which database we’re working on. Someone calls with a problem or we fight a fire, and we run some code. We fix the issue, stress bleeds away and we go back to work, but forget to switch connections or tabs. Then we run some code that would be fine in development, but causes issues in production.

SSMS has colors for a connectionSQL Prompt has tab coloring by system and database, as do some other products., which can help, but it isn’t perfect. One thing I’ve found with colors is that if I use them constantly, my mind starts to filter out the color. I don’t always realize the outline of the tab is a different color. This is especially true if I have the need to switch back and forth between both production and non-production systems. I’ve tried running two instances of SSMS, which helps, but at times I’ll forget which one I’m working with and make a connection to a production server from a non-production instance of SSMS.

Ultimately, we need to be careful. I know one friend that has no access to production and must hop through an RDP session and connect to a production database. However, if you run your RDP session in full screen, how often would you forget that you’re in the SSMS on the hop system and not in SSMS on your local machine.

I don’t know if there’s a good solution. Many of the convenience features that make life easier, like reconnecting tabs when I restart SSMS are great, however, they can compromise security and safety. I don’t know if there is a good solution, but I’d certainly like more checks against ad hoc issues occurring in production systems. Maybe some sort of lock against certain instances that prevents destructive execution on certain instances or databases without some confirmation. I love SQL Prompt preventing me from running code without WHERE clauses, but that isn’t always enough. At least not for me.

Steve Jones

The Voice of the DBA Podcast

Listen to the MP3 Audio ( 4.4MB) podcast or subscribe to the feed at iTunes and Libsyn.

About way0utwest

Editor, SQLServerCentral
This entry was posted in Editorial and tagged . Bookmark the permalink.

4 Responses to The Right Connection

  1. paschott says:

    Can’t even say how many times this bit me back in the day. The color-coding was a great idea when it was introduced, but it never changed when you changed connections. That definitely led me to run queries against Prod thinking that it was a Dev instance because the color never changed.

    SSMS Toolspack remedied that and added more functionality as well. The query history function alone made a huge difference to me as I spent a good portion of my time writing ad-hoc queries. Many of them were similar, but different enough where trying to save any given one would have led to a lot of scripts to manage.

    The color coding has apparently been fixed in later versions of SSMS, but I’ll admit to still being wary and even tweaking my layout so the server name/db are more prominent.

  2. way0utwest says:

    Yeah, there are issues with some tools. I like how Prompt matches an instance/db and color codes that. I’m sure SSMS Toolpack works well as Mladen would be careful here. The main concern I have is that nothing is foolproof. It’s not that we aren’t careful, but we get busy, or stressed, and it’s easy to make a mistake. Having things run through a pipeline is ultimately the best solution I know of.

    • paschott says:

      I pretty much agree about pipelines – just harder to do that when you have a lot of ad-hoc queries to run for updates and such. Mladen’s product was out before Red-Gate added this to SQL Prompt and I’ll admit I don’t use it quite as much now w/ the easier options in Red-Gate, but it was _really_ helpful when it first came out. All of my “Prod” connections were some variation of red, while the other major environments got some gentler color coding.

      I didn’t hit the “run against wrong environment” often, but with the colors not changing when you changed connections it was a lot easier to miss that I was on the wrong server if I counted on the colors to tell me that. I was a bit more cautious before that feature, but still would miss the connection details from time to time.

  3. way0utwest says:

    I think the idea is that you move updates to the app or you set them in a separate pipeline that can be approved to run quickly. That gives you audits and ensures you don’t run dev stuff in the wrong environment.

Comments are closed.