DaveWentzel.com            All Things Data

Central Management Server


CMS is used to manage all sql instances (2000, 2005, 2008) across the enterprise. 
Registered Servers window|Database Engine|Central Management Servers
Requires Windows Auth Mode for all servers registered within the CMS. 
You can group registered servers within a CMS by right clicking and choosing New Server Group.  This is useful if you want to manage dev servers separate from QA and prod servers. 
To run a query against all members of a CMS right click and choose "New Query".  A new col is added to the output to denote which SQL Server the results apply to.  This can be changed under Tools|Options|Query Results|SQL Server|MultiServer Results. 
You can also quickly apply policies to all 2008 instances in the CMS using Policy Based Management. 
You must be a member of ServerGroupAdministratorRole database role in MSDB. 
Settings can be imported/exported right from SSMS using Tasks|Import or Export. 

Add new comment