ORDINA BLOGT

Managing Multiple Server Instances using SQL Server Management Studio

This page describes how to manage multiple SQL Server Instances using Microsoft SQL Server Management Studio (SSMS). This is a SSMS feature so the version of SQL Server is not influencing the use of this.

  • Mario Van Hissenhoven
  • 19 september 2016

Security

You will need the necessary security privileges, depending on the queries you want to execute on the different instances. As a minimum required permission, you need to be part of the data_reader role.

NOTE

The layout may differ from your environment, depending on the version of SQL Server Management Studio. The following screenshots were taken from SQL Server Management Studio 2016 and is not included in the installation of SQL Server 2016 but must be downloaded seperatly.

To download this version of SSMS, go here: Download SQL Server Management Studio (SSMS)

The following steps describe how to

  • Register your servers by grouping them
  • Give them a logical name, description and assign a color to the connection. The color will identify if you are on a production, acceptance, test or local environment.
  • Run a query on multiple instances at the same time

 Register your servers

In management studio, click on View and open the Registered Servers flyout

On the Registered Servers pane, right click on the Local Server Groups and choose New Server Group…

 

On the new group, right click and choose New Server Registration…

Assign logical name and color

Enter the required fields, than switch over to the Connection Properties tab and check on the Use Custom Color checkbox, then choose a color. Choose red for production and green for local or test.

The advantage of this approach is that it allows to run the same query over these multiple instances at the same time, ideal for maintenance queries or the like.

Double clicking on the such a registered server ( click on the icon in front of the registered server ) opens a connection to that instance and switches to the Object Explorer.

Click on the New Query and a new query window will open and connect to the instance. At the bottom, you will notice that the information bar has the color you selected previously.

Run queries on multiple instances

Select the Registered Servers pane again and click on new query. Depending on the selected folder in the Registered Servers pane, the query will be executed on the underlying instances. As you can see in the picture below, the query is connected to the Local group and to the 2 instances in that group.

A query will be executed on both these instances.