Registered servers

Peter Schmitz

Administrator
Staff member
Registered servers come in use when you find yourself regularly connecting to different servers on the network (for instance when, like yours truly, you are in a DBA position), and thus provide easy access to the servers.

There are several ways to setup a registered server:
  1. Prior to connecting to it.
  2. After having connected from Object Explorer.
1. Registering a server prior to connecting to it.
  • To manually create a new registered server, from SQL Server Management Studio, in the menu, click "View", and then select "Registered Servers" (or use the shortcut CTRL + ALT + G):

Registered Servers 01.png

  • In the Registered Servers browser, ensure that you select "Database Engine" by using the "Database Engine" icon from the available options (The first icon. The second icon is for "Analysis Server", the third one for "Reporting Services", the fourth one for "SQL Server - Compact", and the last one for "Integration Services"), and then expand the "Database Engine" group:
Registered Servers 02.png
  • You can choose to add a new server group if you want. To do so, right-click "Local Server Groups", and then select "New Server Group". For this article, we will simply add a new server. Right-click "Local Server Groups", and then select "New Server Registration":
Registered Servers 03.png

  • In the "New Server Registration" screen fill in the name of the server and the authentication method you want to use. In the case of Windows authentication, you are done. In case of "SQL Server Authentication", fill in the login name and password, and optionally check whether you want Management Studio to remember the password. At the bottom, you can optionally change the name of the server (by default, it takes the name of the server), and add a description for the server:
Registered Servers 04.png

  • In most cases, you can now click the "Test" button to verify the registration works, and then click "Save" to save the registration. Optionally, you can change the Connection properties by using the "Connection Properties" tab, where you can select the database to connect to, change the network protocol or packet size used, and alter other options dealing with the connection.

    One cool trick on that screen is the ability to assign a custom color to the statusbar of SQL Server Management Studio when you execute a query against that server in the future, and thus allow you to easily identify where you are running a query:

    Registered Servers 05.png
    Using the above setting yields the following status bar at the bottom of the query window:
Registered Servers 06.png

2. After having connected from Object Explorer.
If you already connected to the server you want to register, registering is as easy as right-clicking the server in question, and selecting "Register":

Registered Servers 07.png


Exporting and importing registered servers
You can easily import and export registered servers from one server to another, and thus create a unified interface across the organization. To do so, right-click the server group you wish to export, select "Tasks", and then select Export:

Registered Servers 08.png

In the "Export Registered Servers" screen, you can then select exactly what you want to export:

Registered Servers 09.png
To import on a different server , you use the Import function available directly above the Export function. You can then select the file to import from, and which server group and or servers you want to import:

Registered Servers 10.png

If you import a registered server to a server group that already contains a server with that name you will be prompted on whether you want to overwrite it or not:

"A server or server group named "<name>" already exists. Do you want to overwrite it?"

If you select to overwrite the server or server group, that server/group will be replaced by the information contained in the import file.
 

Attachments

  • Registered Servers 10.png
    Registered Servers 10.png
    17.9 KB · Views: 729
Top