Gold Award WinnerGold Award Winner



Overview

The following article was previously created as a blog post on http://felipelauffer.wordpress.com/2016/01/12/23/ and some parts were modified to best view in Wiki

Registered Servers utility in SQL Server 2014 Management Studio allows you to store server connection information and manage multiple servers from a central server. It helps in organization of your SQL Server instances and you can easily connect to a server for future connections. 

Getting Started

To access the feature in SQL Server Management Studio, you can go to the menu View > Registered Servers or use the Ctrl + Alt + L shortcut keys.



This will enable the tab Registered Servers in the left corner of the Management Studio window.



Accessing this new tab Registered Servers we have two options: Store the list of servers locally (user profile) or in a central server.



Its better store the list of your servers in a centralized server. Thus, regardless of the client you are using, you can connect to this server and have your list ready. The only limitation we have in the case of centralized server is that all servers that will be registered should use Windows Authentication. If you have a server that only has access with SQL authentication, you must opt for local storage (Local Server Groups).


Central Management Server

To register a central management server, we should click with the right button in Central Management Servers and navigate to the Register Central Management Server option


In the new window opened we just have to inform the server name that will centralize the list of servers in the environment. 



With the central server set we can initiate the organization's list of SQL Server servers by creating groups and registering servers below these groups.

Server Group

To create a new group we can navigate to the central server, click the right button on it and choose New Server Group option and finally enter a name and description for the new group








Register Servers

To register a server under a group we can navigate to the group created, click the right button on top of it, navigate to the New Server Registration option, and finally inform the server name and credentials. 





With this you can go signing up all your SQL servers and organize them by type of environment, location, version, etc.



Export

You can also export the registered servers to a file for later use or import on another server. To export a group of servers click the right button on top of the group and navigate to the item Tasks> Export, then choose the location and name of the destination file. 







The server group has been successfully exported.


Import

You can also import the registered servers from a file. To import a group of servers click the right button on top of the group and navigate to the item Tasks> Import, then choose the location and name of the source file.







Benefits

A great benefit that the Registered Servers feature provides is to perform a query on a group of servers in a very simple way. Click on the right button on a group of servers or even the central management server and navigating to the item New query we can run a query at the same time on all servers in a group.



Below are two examples of a query executed over the PRD group and another on the central management server using the SERVERPROPERTY() function.





See Also