Do you find yourself managing lots of SQL Server instances? If so you might find you are connected to many different instances and have numerous query windows open that are connected to different instances. When you have lots of query windows open, do you sometimes find it confusing to know the instance in which your query window is connected?
Sometimes I get so many query window tabs opened up in SQL Server Management Studio when I’m working on multiple different instances that I start getting confused as to which window is connected to which instance. I know I can just look at the status bar to tell me which instance I’m connected to, but sometimes that just isn’t enough. The worst of it is when someone comes by my desk and wants me to run a quick fix against their Quality Assurance environment. If I’m not paying attention to the status line details, I could easily take their code and run it against their production instance. Yes I must admit it; I’ve been guilty of running code against the wrong server. I’m sure this has never happened to any of you, but just in case it has, I have a solution to help you more easily identify the instance in which your query window is connected.
With SQL Server 2008, Microsoft allows you to set custom SQL Server Management Studio color settings so you can color code each of your different connections. In this article I will be showing you how to color code your query windows based on the instance. I will also explain some of the quirks you might experience related to color coding if you are not using the Service Pack 1 version of SQL Server 2008 Management Studio.
Why Color Code Your Query Window?
When you manage multiple SQL Server instances, you might find that sometimes you get overloaded just trying to keep up with the numerous changes. When you do get busy, you don’t need to waste time trying to read the small text in a query window to determine which instance it is associated with; you need to look for ways to work smarter. One of those ways is to color code your different instance connections in SQL Server Management Studio.
If your environment is like mine, the difference between a production instance name and a non-production instance name is just one character. That makes it even more difficult to read those connection names when you’re in a hurry. With a color coded connection, it makes it really easy to visually tell which instance your query window is connected to. Providing an extra visual clue is just one more method to make sure you don’t run a script against the wrong instance.
How to Color Code Your Different Query Window Connections
The color coding of your connection is done by changing the color of the query window status bar. If you look at Figure 1 you can see I changed the status bar to bright green. This allows me to quickly identify that this query window is connected to my development instance.
Figure 1: Color Coded Status Bar
Color coding the status bar in the Query window in SQL Server Management Studio is only available if you are using SQL Server 2008 Management Studio. Although I must note that I’ve found that using versions prior to 2008 R2 SP1, I do not get a consistent color coding experience each and every time. I will discuss the nuances about color coding more in the “Quirks of Color Coding Your Status Bar” section.
To color code a connection, you first need to open up the “Registered Server” view. Under the “Local Server Groups” item of the “Registered Server” view you will be defining your servers and how they will be color coded. Figure 2 shows three of my local instances that I want to color code.
Figure 2: Three local Instances registered that will be color coded
These three instances represent my development, quality assurance and production environments. Where instance name SSESQL08D is for development work, SSESQL08Q is for quality assurance work and SSESQL08P is for production work. For each of these instances I will be color coding the status bar with green for development, yellow for quality assurance and red for production.
To color code each of these servers, I will need to run through the following process. First I right click on the instance I want to color code and select the “Properties…” item. When I do this, the window in Figure 3 is displayed.
Figure 3: Property Window of the SSESQL08P Instance
I have selected to show you the properties for my production instance. To set the color I want for my status bar on my production instance, I now need to select the “Connection Properties” tab. Figure 4 shows the properties available on the “Connection Properties” tab. 
Figure 4: Connection Properties Window
On the connection properties window you can see there is a check box labeled “Use custom color” – this checkbox allows you to identify whether your status bar will use the default color or a color of your choice. When this box is not checked the default color will be used for the status bar in your query window. Note the gray square box to the left of the disabled “Select” button in figure 4. This box represents the color selected for the status bar. In figure 4 this box is all gray and the “Select” button is disabled, which is also another indicator that the default color will be used for query window status bar. When I click, or check the “Use custom color” checkbox, the “Select” button to the right of it will become enabled.
Once the “Select” button is enabled I can click on it to select a color for my query window status bar. When clicking on the “Select” button, a standard color dialog box will be displayed that will allow me to pick my status bar color for my registered server connection. Since I am defining the status bar color for my production instance, I will select “red” for the color. After selecting “red” as a color for this connection, the “Connection Properties” tab changes to having red in the square box next to the “Select” button. This indicates that my status bar now will use red for its custom color. By looking at Figure 5 you can see how my “Connection Properties” tab looks now that I have selected a color for my connection. 
Figure 5: Custom Color of Red Selected
All that is left to do is save my changed connection properties by clicking on the “Save” button. Once I save my connection properties, all my new connections to this server will use the color red on the status bar.
Quirks of Color Coding Your Status Bar
Just because you have defined a color for a registered server doesn’t automatically change all existing connections for that instance to the color you define for the status bar. You need to either exit SQL Server Management Studio or reestablish your query window connection for the proper status bar color to be displayed.
Another quirk that I found was if you connect to the same instance using different names then you need to register each one of these different names with the appropriate color for that instance. I tend to use different DNS names, server names, port numbers, alias name, and sometime even IP addresses to connect to my different instances. When I do this each one of these names needs to be registered in the local servers in order for me to get a connection to display a consistent status bar color for a specific instance.
You can also register servers and status bar colors under the “Central Management Servers” item of the “Registered Server” view. But when you do this the colors go away as soon as you close SQL Server Management Studio. It would really be nice if the color properties were stored in the central server so all DBAs could easily get the same color scheme from one central place. Maybe this will happen in future service packs or versions of SQL Server.
Additionally you might find it frustrating getting a consistent color coding of instance connections if you are not running SQL Server Management Studio 2008 R2 with Service Pack 1. Prior versions of SQL Server 2008 before SQL Server 2008 R2 Service Pack 2 do not consistently display the same color regardless of how you connected to an instance. Keep in mind your instances do not need to be running SQL Server 2008 R2 SP1 instances, only your SQL Server Management Studio client needs to be updated to this version to get a consistence color coding scheme.
Homegrown SQL Server Management Tool
Mladen Prajdic, a SQL Server MVP, has written a tool kit for SQL Server Management Studio. The tool kit is known as “SSMS Tools Pack” and can be found on the following website: http://www.ssmstoolspack.com. This tool kit has lots of different features to enhance SQL Server Management Studio, and works with a number of different versions of SQL Server. One of the features in this tool is Windows Connection Coloring. I haven’t personally downloaded this tool but I have heard good reports on using this tool to improve your SQL Server Management experience. Therefore if you want to color code your SQL Server connections and don’t want to do it using SQL Server Management Studio 2008 R2 SP2, then this tool might be an option.
Visualizing Your Server Connection with Colors
If you have a number of different SQL Server instances, by color coding your connection status bar you can quickly visualize the instance to which you are connected. If you have only a few servers you can color code each server’s status bar with a different color. But if you have lots of servers you might want to color your status bars by zones, where all production zone instances have a “red” status bar, and the other zones prior to production have less bold colors.
See all articles by Greg Larsen
Subscribe to Cloud Insider for top news, trends & analysis
DatabaseJournal.com publishes relevant, up-to-date and pragmatic articles on the use of database hardware and management tools and serves as a forum for professional knowledge about proprietary, open source and cloud-based databases–foundational technology for all IT systems. We publish insightful articles about new products, best practices and trends; readers help each other out on various database questions and problems. Database management systems (DBMS) and database security processes are also key areas of focus at DatabaseJournal.com.
Advertise with TechnologyAdvice on Database Journal and our other IT-focused platforms.
Property of TechnologyAdvice.
© 2022 TechnologyAdvice. All Rights Reserved
Advertiser Disclosure: Some of the products that appear on this site are from companies from which TechnologyAdvice receives compensation. This compensation may impact how and where products appear on this site including, for example, the order in which they appear. TechnologyAdvice does not include all companies or all types of products available in the marketplace.
Leave a Reply