Microsoft introduced a neat feature in SQL Server Management Studio (SSMS) in SQL Server 2008 that helps users quickly determine which connection a tab is currently using by setting custom connection colors in SSMS for different SQL Server nodes.
For database administrators who work on a multiple SQL Servers simultaneously, this feature is a time-saver that allows users to quickly determine which SQL Server are currently used by looking at a color defined for the associated server.
How to set connection colors in SSMS
The colors can be associated with a specific server node in either Object Explorer or Registered Servers.
Open SSMS, and on the Connection to Server window, click the Options button to show advanced options:
This will expand the window and show three tabs: Login, Connection Properties, and Additional Connection Parameters. By default, the Connection Properties tab is selected and this is the one needed to set up custom connection color:
In this example, let’s set a color for the DESKTOP-T720HHD\SQLEXPRESS server, AdventureWorks2014 database. Either type/paste the name in the Connect to database field or select it from the drop-down menu by clicking the Browse server option:
This will pop-up a window indicating that this action requires connection to the server. Click the Yes button to continue:
A new Browse Server for Database window will appear listing all available SQL Server instances and its databases. Under the User Databases folder, select the AdventureWorks2014 database, and click the OK button:
|
Quick tip: The list of available SQL Servers in the window does not show all available SQL Servers instances. If a particular server is not on the list, make sure to select it from the Server name drop-down box, under the Login tab |
Now, when the database for the connection is selected, check the Use custom color check-box, and click the Select button to select the color:
The Color picker window will pop-up in which either a basic or custom color can be selected from the grid. Let’s just pick the green in the top row, 3rd from the left, and then click the OK button:
Once the connection color is selected, click the Connect button:
Go to Object Explorer, expand the Databases folder of the DESKTOP-T720HHD\SQLEXPRESS server, right-click the AdventureWorks2014 database, and select the New Query command:
From now on, the status bar in the editor window for any new query to the AdventureWorks2014 database under the DESKTOP-T720HHD\SQLEXPRESS server will be set to the color defined for the associated server:
As mentioned before, another way to associate a status bar color is from Registered Servers.
To set a server color for a Registered Server, open the Registered Servers pane using either keyboard shortcut combination (Ctrl+Alt+G) or select the Registered Servers item in the View menu from SSMS:
|
Quick tip: If the Registered Server list does not have any server instances under the Local Server Groups folder, right-click the already connected server in Object Explorer, and then click the Register option. To register multiple servers at once, right-click the Local Server Groups folder, from the context menu select Task, and then select Register Local Servers |
In the Registered Servers window, expand the Database Engine, expand the Local Server Groups, right-click the server node and then select Properties command:
This will open the Edit Server Registration Properties window. Under the General tab, make sure that the Server name is set same as before:
Moving on to Connection Properties tab, repeat the exact same steps from before to set up custom connection color, and click the Save button to save the registered server settings:
To register the local server instances, for example if it is no listed, in the Registered Servers window, expand the Database Engine, right-click Local Server Groups, and then select New Server Registration:
Under the General tab, choose the Server name instance from the drop-down list, and then click the Save button:
From here, the new server instance is added to the Local Server Groups folder:
To set a server color for the newly added Registered Server, repeat the steps as before.
Although this feature is very useful, it can also be very unreliable. Setting a server color through the Connect to Server window and Registered Servers work independent of each other, even for the same login. The best practice is to set the same server color settings through both the Connect to Server window and Registered Servers.
Once the status color for a server is set up correctly when a new query is opened for that server, the status bar will be colored, in this example, green:
However, switching from one SQL Server instance to another can lead to misbehavior and not coloring the status bar as a set in the first place. Another drawback of this feature is that the settings are stored within SSMS, and the settings won’t apply in SSMS on another machine, which makes it impossible to share the settings with for example colleagues in a company.
Furthermore, there could be multiple databases for multiple environments on the same SQL Server instance. The biggest drawback of setting connection color in SSMS is the lack of the option to set colors down to a database level.
Alternate approach
Let’s explore another solution and see how to set up a custom connection color to both status bar and query tab in both SSMS and Visual Studio with a neat feature in ApexSQL Complete called the Tab coloring.
ApexSQL Complete is a productivity add-in that integrates seamlessly into both SSMS and Visual Studio and helps users write code faster with its neat features, and much more.
Tab coloring feature is designed to set connection colors in SSMS for a single SQL Server instance, down to a database level. To set a server status color in ApexSQL Complete, navigate to ApexSQL Complete menu in SSMS, select Options from the submenu, and then click the Tab coloring tab:
From here, select the Server, Database, and Environment from the drop-down boxes to match the settings in SSMS example, and then click the Add button to create a new color connection rule:
|
Quick tip: The Server drop-down list of SQL Servers instances is dependent on SQL Servers connected to in Object Explorer. If the Server drop-down list is empty, ensure that at least one connection is active in Object Explorer |
Let’s not stop here, and create another color connection rule for the same SQL Server, but another database. Select the same server as before, only this time, from the Database drop-down list select the another database, and assign it to the Production environment by clicking the Add button:
|
Quick tip: When working with Tab coloring, all changes that have been made e.g. adding a new connection rule, editing existing one, etc. will be saved only if the OK button is pressed in the Options dialog. Clicking the Save button will be overwritten if the Close (x) or Cancel button is clicked to close the dialog |
After saving those settings, newly opened queries on the AdventureWorks2014 database, for the WIN10\SQLEXPRESS server will have both status bar and query editor tab colored green, showing that the user is working on the Development environment for the associated database:
If the query editor is switched to the one that has an active connection to another database assigned to another environment on the same SQL Server, both status bar and tab will change to assigned color for that environment:
If the situation requires all available databases on a single instance of SQL Server to be assigned to one specific environment, there is no need to assign them one-by-one. If this is the case, select preferred SQL Server instance, from the Database drop-down menu select the All databases, and assign it to desired environment:
The result is the same as before when setting this up in SSMS, only this time both status bar and tab will be colored.
Unlike connection coloring settings in the SSMS example, these settings can be exported to an .xml file that can be imported on any machine where ApexSQL Complete is already installed.
For more information on connection colors in SSMS, and Tab coloring feature in general please see the article: How to customize tab colors in SSMS
