How to: Create a Central Management Server and Server Group (SQL Server Management Studio)
In SQL Server 2008, you can designate an instance of SQL Server as a Central Management Server. Central Management Servers store a list of instances of SQL Server that is organized into one or more Central Management Server groups. Actions that are taken by using a Central Management Server group will act on all servers in the server group. This includes connecting to servers by using Object Explorer and executing Transact-SQL statements and Policy-Based Management policies on multiple servers at the same time. All Central Management Servers and subordinate servers must be registered by using Windows Authentication. Versions of SQL Server that are earlier than SQL Server 2008 cannot be designated as a Central Management Server.
To create a Central Management Server
In SQL Server Management Studio, on the View menu, click Registered Servers.
In Registered Servers, expand Database Engine, right-click Central Management Servers, point to New, and then click Central Management Servers.
In the New Server Registration dialog box, register the instance of SQL Server that you want to become the Central Management Server.
In Registered Servers, right-click the Central Management Server, point to New, and then click New Server Group. Type a group name and description, and then click OK.
In Registered Servers, right-click the Central Management Server group, and then click New Server Registration.
In the New Server Registration dialog box, register one or more instances of SQL Server that you want to become members of the server group.
After you have registered a server, the Central Management Server will be able to execute queries against all servers in the group at the same time.
To execute queries against several configuration targets at the same time
- After you create a Central Management Server, one or more server groups, and one or more registered servers, you can execute queries against a whole group at the same time. For information about how to execute Transact-SQL statements on the servers in a server group at the same time, see How to: Execute Statements Against Multiple Servers Simultaneously (SQL Server Management Studio).
Security
Two database roles in the msdb database grant access to Central Management Servers. Only members of the ServerGroupAdministratorRole role can manage the Central Management Server. Membership in the ServerGroupReaderRole role is required to connect to a Central Management Server.
Because the connections that are maintained by a Central Management Server execute in the context of the user, by using Windows Authentication, the effective permissions on the registered servers might vary. For example, the user might be a member of the sysadmin fixed server role on the instance of SQL Server A, but have limited permissions on the instance of SQL Server B.