Almost every DBA has to manage more than one instance of SQL Server. If you are administering production, test and development servers then there are probably databases with similar names and structures in each of these instances. The scenario becomes more complicated when you are working with multiple logins in each instance at the same time. Chances increase to run a script on an instance other than on which it was intended to run. Although every query pane in SSMS includes information such as server instance and user this information is not always displayed prominently and it takes a bit of effort to note the details from tab to tab. In this tip, I will show an option in SSMS (SQL Server Management Studio) 2008 to identify instances and users using customized status bars.
Query panes in SSMS can be accessed several ways; from the file menu, the tool bar or by using Cntrl+N. In addition, from one desktop you can connect to production, test, development or any SQL Server as long as your have the right credentials. This tip will show you a way in which to identify a query pane by using customized color bars indicating the related server instance and login to which the query pane is linked.
Customize Query Pane Color for Current Connection
Create a new connection to one of your SQL Servers from SQL Server Management Studio.
When the connection window opens enter a user name and password and then click on the "Options >>" button on the bottom right.
A new tab named "Connection Properties" will become available.
- Click on the "Connection Properties" tab
- Check "Use custom color"
- Click the "Select..." button
- Select a color from the color chart
- Select "OK"
Once you have selected a customized color, click "Connect" to connect to the server using this new color customization. This color customization will also be saved for this specific instance and user for future connections. If at this point you are not able to connect to the SQL Server for any reason the color customization will not be preserved.
Now open a new query pane for this connection and verify that the color of the status bar is the same that was entered in the connection properties. Whenever you connect to this server instance using this same user the status bar in the query pane will use this customized color. You can assign different colors to each login for a single instance.
Customize Query Pane for Registered Servers
Many administrators register their instances in SSMS. You can also customize the color for each registered instance.
For servers you already have registered right click on the server name in the Registered Servers list and select "Properties".
For new servers go through the process of registering the server.
Now for both new or existing registered servers go to the "Connection Properties" tab and follow the steps above to customize the color.
Now any time you open a query pane using this registered server the same color will be applied to the status bar.
Something to notes is that you can only register an instance once, so this limits the color schemes that you can use for different logins.
What it Looks Like
So when you have selected a customized color, the color on the bottom status line will match the color you selected when you connected or whatever has been setup for your registered instance. As you can see below the status bar has been setup to red for this connection.
Here are some additional points to be aware of when applying customization.
- You can use SQL Server or Windows Authentication for color customization in SSMS.
- Customization can be used for pre SQL Server 2008 instances when accessed using SSMS 2008.
- Customization performed through registered servers and the server connection window work independent of each other, even for the same login.
- Color customization is stored locally within SSMS, so the customizations do not show up if you use SSMS from another machine.
- Customized color status bars are only available for database engine connections. Analysis services, reporting services and integration services do not have a customized color option for their connections.
As a part of best practices, you can implement customization of the status bar color for SSMS 2008
- It is a better approach to register instances on your SSMS for your login and customize the color for the registered server. Click here to read about registering instances.
- You can install and use SSMS 2008 for administration of pre 2008 instances of SQL Server
- You should look to implement standard colors for your status bars. One scenario would be
- red for production
- green for test
- blue for development
Last Update: 2009-11-02
About the author
View all my tips