Database Reference
In-Depth Information
4.
From the preceding screenshot, the results with a pink bar at the bottom state
that this is a Production group. To change the multi-server results options, in the
Management Studio, on the Tools menu, click Options.
5.
Expand the Query Results, expand SQL Server, and then click Multi-server Results.
6.
On the Multi-server Results page, specify the option settings that you want, and then
click OK.
We now should have successfully completed the CMS feature implementation into the SQL
Server data platform.
How it works...
The SQL Server instance that is designated as a Central Management Server maintains server
groups, which maintain the connection information for one or more instances of SQL Server.
CMS and all subsidiary servers on the network are registered using Windows Authentication
only; in case local server groups are registered using Windows Authentication and SQL
Server Authentication.
The storage for centralized management server groups, connection information, and
authentication details are available from
themsdb
system
database in:
F
dbo.sysmanagement_shared_registered_servers_internal
F
dbo.sysmanagement_shared_server_groups_internal
system tables
F
dbo.sysmanagement_shared_registered_servers
F
dbo.sysmanagement_shared_server_groups
system views
The user and permissions information on the registered servers may vary based on the user
permission that uses Windows Authentication connection. For instance, in our recipe, on the
dbia-ssqa\SQL2K8
instance, the
login
is a member of the
SYSADMIN
fixed server role
and on the
DBIA-SSQA\SSQA
instance, the
login
is a member of the
DBCREATOR
fixed
server role. In this case, we may not be able to run certain
SYSADMIN
privileged statements
on
DBIA-SSQA\SSQA
instance.
There's more...
Using SQL Server 2008 R2 Management Studio with CMS, we can manage the lower SQL
Server version instances, such as 2005 (90), 2000 (80), and 7.0 (70), which is truly a central
management of instances.
Search WWH ::
Custom Search