Today’s Monday Morning Mistake issue is another painfully common one that we all run into at some point. You’ve got some new code given to you by a developer that’s supposed to be ran against QA (because you’re a good DBA and you don’t grant developers access to anything outside of Development). Part of the code drops some existing tables and recreates them with new schemas, objects, etc. You hit the execute button when the blood drains from your face when you realize the instance you were connected to was a production instance!

Issue

You have multiple instances of SQL Server to manage and you want a visual indicator in SQL Server Management Studio (SSMS) to let you quickly know which environment you’re connected to.

Solution

There are a number of things we can do to help mitigate this issue but we’re going to tackle the high-level problem first: Quickly being able to visually identify which system/environment you’re connected to. Unfortunately, by default the SSMS interface doesn’t do much to help us in terms of showing us the difference between server connections.

The Native Way

Notice how I said by default up there? SSMS does have a solution but it’s not as intuitive as it could be to help a user out. When you open SSMS and are presented with the Connect to Server dialog. The trick is to click on the Options button. Towards the bottom of the dialog there’s an option to use Custom Color. Check the box for ‘Use Custom Color’ and then click on the Select button to choose your color (applies for this server connection only).

Now hit connect. Your server connection will open in Object Explorer. Open a new query window by clicking the New Query button up top (or use keyboard shortcut Ctrl+N). You’ll notice at the bottom of your query window you now have a colored stripe giving you a visual indicator.

You can come up with whatever color combination/system you’d like using this method but I like keeping it simple for my environments:

• Production – Red
• QA/UAT – Yellow
• Development – Green

The obvious drawbacks for the native method are that you need to setup the color selection preferences one connection at a time. What if you want to share those color combination selections with your team? You’re in luck, there’s a way to do this via the Central Management Server (CMS) feature of SSMS!

For servers you have registered in your CMS, right-click them and go to Properties. Click on the Connection Properties tab and select your custom color as you did in the previous step. Once you’ve selected your color click the Save button. Now, when someone connects to that instance using CMS, the color connection property will apply and you’ll have your visual indicator. Again, drawback here is that the color stripe will only apply to the connection window assuming the user connected via CMS.

The Third-Party Solution

While the native tools have the color coding per connection ability, it’s not as easy as it could be. Luckily, there are third-party tools that can do this for you in a really slick manner. One of my favorite, all-time, must-have (and most importantly) FREE third-party tools is the SSMS Tools Pack by SQL Server MVP Mladen Prajdić (Blog | Twitter).

Like SSMS, you’ll get a color strip indicating your server connection however the default for SSMS Tools Pack is it puts it at the top. The awesome thing about Tools Pack, that you can’t do with native tools, is customization of connections. For instance you can change the color strip thickness, change the location of the stripe (Top, Bottom, Left, Right), choose custom color but the absolute coolest option is to use Regex to automatically select color stripe options for you.

The regex is really helpful, especially if you have standardized naming conventions that include environment-level clues such as “PROD”, “PRD”, or numbering convention in the name that you can key off of. For this example let’s say I have multiple servers with a numbering convention called BIDEV01-BIDEV09. Instead of manually setting color combination for all nine connections, I’d like regex to make all connections to my BIDEV servers green, no matter which number I connect to.

To do this, open Window Connection Coloring Options box (SSMS Tools->Window Connection Coloring->Options). Find the first server name in the list that follows the convention (i.e. BIDEV01). Click in the Server Name box to edit it and delete the 01 portion of the server name and replace it with the [0-9][0-9] so the server name now reads as ‘BIDEV[0-9][0-9]’ (no single quotes). Check the box in the column IsRegex, select your dock location option (top is default), and select your color. Once you’ve got your selections set click the Save button.

Now whenever you connect to any of your BIDEV servers, be it BIDEV01 or BIDEV07, SSMS Tools Pack uses the regex to automatically apply your color connection preference to those windows!

As far as sharing goes, if you have created multitude of really cool RegEx connection formulas and you want the team to standardize on the settings you’ve setup in Tools Pack, you can export all your settings from the Options menu. Unfortunately it’s an all or nothing export here so if you highly customize your SSMS Tools Packs options and give them to someone they’ll be getting all of your customizations.

SSMS Tools Pack is a free add-in, and does a TON more stuff as well. While it is free I highly encourage you to donate to the developer. I wasn’t asked to write this glowing review of the product, I just REALLY love it and want to help throw a little love back towards the developer for making such a great tool. It is worth noting there are other third-party products as well such as Redgate’s SQL Prompt tool (which is a TREMENDOUS product if you haven’t tried it yet), but you must purchase licenses for them.