Administering Multiple Servers Using Central Management Servers
In SQL Server 2008, you can administer multiple servers by designating Central Management Servers and creating server groups. An instance of SQL Server that is designated as a Central Management Server maintains server groups which maintain the connection information for one or more instances of SQL Server. Transact-SQL statements and Policy-Based Management policies can be executed at the same time against server groups. Versions of SQL Server that are earlier than SQL Server 2008 cannot be designated as a Central Management Server.
Note
Transact-SQL statements can also be executed against local server groups in Registered Servers.
Creating Central Management Servers and Server Groups
The Central Management Server includes connection information about the configuration targets. Only Windows Authentication is supported. Therefore, authentication information is not stored. This enables Central Management Servers to execute Transact-SQL statements on multiple servers at the same time.
To create a Central Management Server and server groups, use the Registered Servers window in SQL Server Management Studio. The Central Management Server cannot be a member of a group that it maintains. For more information about how to create Central Management Servers and server groups, see How to: Create a Central Management Server and Server Group (SQL Server Management Studio).
Multiserver Queries
To execute Transact-SQL statements against all the servers in a server group at the same time, open a Query Editor from the server group in the Registered Servers window. Transact-SQL statements executed in the Query Editor will be executed against all the servers in the group. The results that are returned by the query can be merged into a single results pane, or can be returned in separate results panes. When you are merging results, the first server to respond sets the schema for the result set. To merge the result sets, the query must return the same number of columns that have the same column names from each server. When you are merging results, a message is displayed for each server that does not match the schema (column count and column names) that is returned by the first server to return results. When you do not merge results, the results set from each server will be displayed in its own grid with its own schema.
As an option, the Query Editor can include columns that provide the name of the server that produced each row, and the login that was used to connect to the server that provided each row.
For more information about how to execute multiserver queries, see How to: Execute Statements Against Multiple Servers Simultaneously (SQL Server Management Studio).
Policy-Based Management
A policy can be evaluated against a registered server or a whole server group. The on-demand policy execution mode enables both the evaluate and apply modes for policies. If a connection cannot be established to one or more servers, those servers are ignored and evaluation against the other servers continues independently. For more information about Policy-Based Management and execution modes, see Tutorial: Administering Servers by Using Policy-Based Management.
Security
Because the connections to servers in Central Management Server groups execute in the context of the user, by using Windows Authentication, the effective permissions on the servers in the server groups 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 instance of SQL Server B.