Registered Servers is an
optional built-in feature; if you manage only one or a few SQL Servers,
Registered Servers offers little benefit. If, however, you are
responsible for many SQL Servers, or if you simply want an organized
way to manage your registered instances, this is the right place to
take control.
Using Registered Servers, you can maintain
connection information for connections to the Database Engine, Analysis
Services, Reporting Services, SQL Server Mobile Edition Databases, and
Integration Services. The toolbar at the top of Registered Services
enables selection among the types of services.
Managing Servers
Servers are easily registered using the
context menu and supplying the Properties page with the server name,
authentication, and maybe the connection information. One key benefit
of registering a server is that it can be given an alias, or Registered
Server Name, in the Properties page, which is great if you're a DBA
managing dozens of servers or instances with cryptic server names.
After a server is registered, you can easily
select a server, and using the context menu, connect to it with Object
Explorer or Query Editor. Although this is a good thing, the server
aliases don't propagate to the Object Explorer, which can lead to
confusion. The workaround is to keep the Object Explorer free from all
other connections except those currently in use.
You can also use the Server context menu, as shown in Figure 1,
to connect to the server with Object Explorer or Query Editor, or apply
Policies. Other tasks include starting and stopping the service, and
opening the registration's Properties page.
To share your registered server list, or move
from one SSMS installation to another, export and import the server
configurations using the Context menu ? Tasks ? Import/Export.
Server Groups
Within the Registered Servers tree,
servers may be organized by server groups. This not only organizes the
servers, but enables new group actions as well.
- Local Server Groups: Stores the connection information in
the local file system. Think of these as Management Studio groups. The
tree in Registered Servers flows from the service (i.e. Database
Engine), to Local Server Groups, to Server Group (if you've created
any), and finally to the registered server itself. Registering servers
locally allows you to register servers using both Windows and SQL
Server authentication.
- Central Management Server: Central Management Server
functions just like Local Server Group registrations except that the
connection information for registered servers is stored in the msdb
database of the designated Central Management Server. The other notable
difference is that server registrations under a Central Management
Server can only use Windows authentication to register.
The server group (local or Central Management
Servers) context menu includes the same Object Explorer, Query Editor,
and Policy commands as registered servers. When a query/command/policy
is executed at the root level of the registered servers, they apply to
all servers and groups below it. However, when these commands are
executed from a specific group, they apply only to all servers in the
group or groups nested below the selected group.
- Object Explorer: Opens Object Explorer and connects to every server in the Server Group or groups nested below it.
- New Query: Opens a new query window with a connection to the
group instead of a connection to the server. T-SQL commands can then be
submitted to every server simultaneously. The Query Editor merges (or
“unions”) the results from every server and adds two columns, server
name and login, to indicate which server returned each row as seen in Figure 2.
The columns and whether results are merged or returned in separate
result sets can be configured in Tools ? Options ? Query Results ? SQL
Server ? MultiServer Results, or Query Editor context menu ? Results ?
MultiServer. Messages now include the server name and login with each
message.
- Policy-Based Management Policies: May be applied to every server in the group or groups.
