SQLWebpedia

SQL 2008 has some new DBA focused features, one of which is the Central Management Server for issuing multi-queries (queries against many servers at once, even down level clients) and Policy Based Management. Connections use your Windows Authentication only.

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. If you started with an instance that you do want to maintain, switch to another by exporting a file of all connections and importing into new server instance. Find this option by right clicking your CMS and selecting Tasks flyout.

To use the multi-query capability just right click the group name and select new query. Note all result sets must match otherwise those that don't match the first will be skipped.

There is a similar named project on CodePlex SQLCMS, but this one stores more information about all your servers.

Queries to add groups and servers if you don't like the GUI (found via profiler):

--Add a group at root level. (Change parent_id if you want to added nested group.
Declare @server_group_id int
EXEC msdb.dbo.sp_sysmanagement_add_shared_server_group @parent_id=1, @name=N'VAS', @description=N'', @server_type=0, @server_group_id=@server_group_id OUTPUT
Select @server_group_id

--Add a server to a group.
Declare @server_id int
EXEC msdb.dbo.sp_sysmanagement_add_shared_registered_server @server_group_id=17, @name=N'WH2K5', @server_name=N'WH2K5', @description=N'', @server_type=0, @server_id=@server_id OUTPUT
Select @server_id

--Is this server used as a CMS store?
exec sp_executesql N'
declare @tmp_shared_server_store_stub TABLE (ID int)
insert into @tmp_shared_server_store_stub (ID) values (0)   

SELECT
N''RegisteredServersStore'' + ''/ServerGroup[@Name='' + quotename(sg1.name,'''''''') + '']'' AS [Urn],
sg1.server_group_id AS [ID],
sg1.name AS [Name],
sg1.description AS [Description],
sg1.server_type AS [ServerType],
ISNULL(sg1.parent_id,0) AS [ParentGroupID],
sg1.num_server_group_children AS [ServerGroupChildCount],
sg1.num_registered_server_children AS [RegisteredServerChildCount]
FROM
@tmp_shared_server_store_stub AS store
INNER JOIN msdb.dbo.sysmanagement_shared_server_groups AS sg1 ON ISNULL(sg1.parent_id,0)=store.ID
WHERE
(sg1.name=@_msparam_0)
option (hash join, force order)
',N'@_msparam_0 nvarchar(4000)',@_msparam_0=N'DatabaseEngineServerGroup'

--Find all Groups.
exec sp_executesql N'
declare @tmp_shared_server_store_stub TABLE (ID int)
insert into @tmp_shared_server_store_stub (ID) values (0)

SELECT
N''RegisteredServersStore'' + ''/ServerGroup[@Name='' + quotename(sg1.name,'''''''') + '']'' + ''/ServerGroup[@Name='' + quotename(sg2.name,'''''''') + '']'' AS [Urn],
sg2.server_group_id AS [ID],
sg2.name AS [Name],
sg2.description AS [Description],
sg2.server_type AS [ServerType],
ISNULL(sg2.parent_id,0) AS [ParentGroupID],
sg2.num_server_group_children AS [ServerGroupChildCount],
sg2.num_registered_server_children AS [RegisteredServerChildCount]
FROM
@tmp_shared_server_store_stub AS store
INNER JOIN msdb.dbo.sysmanagement_shared_server_groups AS sg1 ON ISNULL(sg1.parent_id,0)=store.ID
INNER JOIN msdb.dbo.sysmanagement_shared_server_groups AS sg2 ON ISNULL(sg2.parent_id,0)=sg1.server_group_id
WHERE
(sg1.name=@_msparam_0)
option (hash join, force order)
',N'@_msparam_0 nvarchar(4000)',@_msparam_0=N'DatabaseEngineServerGroup'

--Find contents of a specific group.
exec sp_executesql N'
declare @tmp_shared_server_store_stub TABLE (ID int)
insert into @tmp_shared_server_store_stub (ID) values (0)

SELECT
N''RegisteredServersStore'' + ''/ServerGroup[@Name='' + quotename(sg1.name,'''''''') + '']'' + ''/ServerGroup[@Name='' + quotename(sg2.name,'''''''') + '']'' + ''/RegisteredServer[@Name='' + quotename(rs.name,'''''''') + '']'' AS [Urn],
rs.server_id AS [ID],
ISNULL(rs.server_group_id, 0) AS [ServerGroupID],
rs.name AS [Name],
rs.description AS [Description],
rs.server_name AS [ServerName],
rs.server_type AS [ServerType],
0 AS [CredentialPersistenceType],
N'''' AS [ConnectionStringWithEncryptedPassword]
FROM
@tmp_shared_server_store_stub AS store
INNER JOIN msdb.dbo.sysmanagement_shared_server_groups AS sg1 ON ISNULL(sg1.parent_id,0)=store.ID
INNER JOIN msdb.dbo.sysmanagement_shared_server_groups AS sg2 ON ISNULL(sg2.parent_id,0)=sg1.server_group_id
INNER JOIN msdb.dbo.sysmanagement_shared_registered_servers AS rs ON ISNULL(rs.server_group_id, 0)=sg2.server_group_id
WHERE
(sg2.name=@_msparam_0)and((sg1.name=@_msparam_1))
option (hash join, force order)
',N'@_msparam_0 nvarchar(4000),@_msparam_1 nvarchar(4000)',@_msparam_0=N'RegisteredGroupNameHere',@_msparam_1=N'DatabaseEngineServerGroup'