Product articles SQL Prompt SSMS Tab Management
SQL Prompt Safety Net Features for…

SQL Prompt Safety Net Features for SSMS: Tab Coloring

The Tab Coloring component of SQL Prompt’s SSMS Tab management could save you from accidentally doing something apocalyptic on a production server, ever again.

Guest post

This is a guest post from Phil Factor. Phil Factor (real name withheld to protect the guilty), aka Database Mole, has 30 years of experience with database-intensive applications.

Despite having once been shouted at by a furious Bill Gates at an exhibition in the early 1980s, he has remained resolutely anonymous throughout his career.

He is a regular contributor to Simple Talk and SQLServerCentral.

Have you ever been working away, into the early morning hours, trying things out on your development server, so that you can ‘roll-forward’ the production database server, and fix an urgent and embarrassing problem with the latest release?

No, me neither, as I always say, especially when in august gatherings of DBAs. However, it’s happened to a friend of mine. It was three o’clock in the morning, and he was tired, but still working hard, trying to sort out a bad problem with the shopping cart process of a website. He was working in SSMS, with one query pane connected to the development code, and another query pane open to the production server, showing the current, live version of the same code.

I, by which I mean my friend, had to clear out the entire ledger table on the development server and re-insert with test data, and then run an integration test to verify what he’d done. He executed the wrong code in the wrong query pane. What he’d done wrong hit him almost immediately and in the few brief seconds before the execution failed, his life flashed past him. How many hours of business transactions would be lost to the company? Never had he been so grateful to see his code ‘tripped up’ by the violation of a FOREIGN KEY constraint.

As he pondered his narrow escape, he reprimanded himself for not checking the base of each query pane, which shows the name of the connected server. But who routinely checks that? There must be a better way, surely? For a while, I (and my friend) took to using used different PCs to work with each server simultaneously. It worked well, even when half asleep. Now, we both use Prompt “Tab Magic”, and we never ever again forget to run anything contentious within a transaction, just in case.

For details of SQL Prompt’s SQL History safety net, see: SQL Prompt Safety Net Features for SSMS: SQL History.

How tab coloring works

The query pane of SSMS has a row of tab labels along the top, and a status pane at the bottom of each query tab. SQL Prompt’s ‘Tab Color’ component will color code these to help you to see immediately to what server, or type of server, each tab is connected. The colour of the tab that you choose is bright, if it is the current tab, or darker if it isn’t.

Figure 1 shows my SSMS session, with three query tabs. You would want to think twice before executing this code, intended for your development server, on your production server instead!

SQL Prompt tab colors

Fortunately, with Tab coloring, you will be in no doubt at all, because of the lurid red background colour of the tab connected to the production code, and thanks to the status bar at the base of the query window.

beware you are connected to production

Assigning tab colors to server groups, servers and databases

There are several ways that you can map each of your servers or databases to your chosen colour for that category of environment. The most intuitive way is to do it is to right-click on the server or database in the SSMS object explorer, but you can also do it for servers, or groups of servers, from the local Registered Server groups browser, or the Central Management Servers browser.

  • Tab Color (Group) – assign a color to a server group, for example if you’ve organized your servers into groups according to their environment (development, testing, staging production)
  • Tab Color (Servers in Group) – assign a color to all servers in a particular group
  • Tab Color (Server) – assign a color to a server
  • Tab Color (Database) – assign a color to a database

Personally, I find it neatest to use the registered server groups, and there are a couple of ways to map these groups to their environment (and therefore tab color).

Firstly, you can set the colour for the group by right-clicking on it, selecting Tab Color (Group), and assigning it to an environment. This option is for when you are working on a query pane that will need to connect to several servers in a group.

Assigning a tab color to a server group

The assigned environment color only takes effect for query tabs opened directly from that group. Having assigned the group to Development, say, if you then just open a query tab and connect it to an instance, the tab won’t be green; it will be the default color, or whatever color is assigned lower down in the hierarchy, at the server or database level.

However, if I use the setting Tab Color (Servers in Group) option instead, or as well, then any query tab connected to an instance in that group will inherit the specified colour, unless I then override it at the individual server or database level.

The cleanest way to do it, I find, is to make your assignments at the highest level possible, and then leave everything else set to Default. For example, set the color for a group, and all servers in that group, at the Registered Server Group level and then make sure Tab Color (Server) is set to Default, for each server in that group, and then that Tab Color (Database) is set to Default for each database on each of those servers.

Editing colors, adding environments

You can do the editing of all the tab colour assignments within SSMS, using SQL Prompt’s Options | Queries | Color window. It is in this window that you can delete assignments, which is the same as setting them to Default. It pays to check in here occasionally, and clean stuff up if needed!

add an new environment and assign a tab color

You can use this window to add new environments, if the built-in collection proves insufficient. As you can see, it’s also possible to add server directly from this menu, but it is less error-prone to do it from the server’s context menu in SSMS.

Here, I’m adding a Sandbox environment to the existing built-in environments by clicking on Edit Environments and providing the name and colour I wanted:

add a new environment

I was then able to assign it to my ‘Sandbox’ group, in my registered local servers, from the context menu.

Summary

Often, you need to work on different servers as a developer. I like to keep an eye on the test server, check on metrics in the test runs that wouldn’t interest the test team. The same goes for staging. I need to be very careful, though.

Sometimes, If I’m working late, switching between test and development servers, it feel all too easy to accidentally execute code against the wrong server. However, ever since setting up tab coloring, I’ve never since come close to doing anything apocalyptic on a production server, by mistake.

Tools in this post

SQL Prompt

Write, format, and refactor SQL effortlessly

Find out more