SQL Server 2008′s new Central Management Server

Got more than one DBA?  Want to make your life easier?  You might want to configure a Central Management Server.

This is useful for fast-changing shops where there’s a lot of servers added and removed.  At my last company, we had a hard enough time telling everybody when we’d finished adding a new server, or what the new server’s name was, or when we didn’t need to look at a server anymore.

A SQL Server CMS is just a central repository that holds a list of managed servers. Sounds simple – and it is – but it comes in handy, and it’s practically a requirement for a good policy-based management deployment.

In a shop with two DBAs, they both have their own desktops (plus maybe laptops) and each machine has its own list of registered servers.  With a CMS, the list of registered servers is stored on the central SQL Server.  When the DBA opens SQL Server Management Studio, they point at the CMS, and SSMS grabs the list of registered servers from there.

How to Set Up a Central Management Server

To configure it, open SSMS 2008 and go into the Registered Servers window.  Right-click on Central Management Servers and you get options to set one up.  From there, it’s basically the same as your local registered server list – only it’s centralized:

Registered Servers window showing a CMS

In that above screenshot, I connected to a CMS on P-SQL20081\CMS, and that instance stores the list of SQL Servers.  The list is initially empty – it doesn’t automatically detect all of the database servers in your enterprise – you just add servers and groups manually.

After you set it up from any workstation, then on any OTHER workstation, you can point SQL Server Management Studio at that CMS, and the list of servers is always in sync.  Think of it as a server list repository.

CMS Drawback: Windows Authentication Only, And Only Your Login

The CMS server list is just a list of server names: nothing more, nothing less.  Authentication is not saved at all.  When you connect to any server in the CMS list, your Windows authentication is used.  You can’t save an override list of logins, like an SA login for a specific server in the DMZ.

DBAs in large shops administer databases all over the world, in lots of domains that don’t trust each other, and in DMZs, and the Central Management Server is useless here.  We can set up multiple CMS’s, one in each domain, but that’s not exactly ideal.

I hate this limitation.  It means the CMS is nothing more than a centralized text file list of servers.  But it’s what we’ve got, so let’s get over it.  Either you can use a CMS, or you can’t.  Those of you who can, keep reading.

Next Drawback: Group Management Won’t Apply to the CMS

When you pick your Central Management Server, it needs to be a server that you won’t need to run multi-server queries against.  In the screenshot below, my CMS is the server P-SQL20081, and I’m trying to register P-SQL20081 as one of the registered servers:

Cannot Register the CMS Server In Itself

To understand why this is an issue, you have to understand what CMS registered server groups are useful for: multi-server queries and policy-based management.  Say I have groups for Development, Lab, QA and Production.  When I want to run a multi-server query against all of my development boxes, or I want to evaluate a policy against all of them, I right-click on the Group D registration and click New Query or Evaluation Policies:

Taking an Action on a Registered Group

The problem is that if I can’t register the server P-SQL20081 inside Group D, then I can’t include it in group queries or policy-based management for that group.

Furthermore, if I right-click on P-SQL20081 and click New Query, I do get a group-execute query – but it does not include the CMS.  The query only executes against all of the registered servers, and since you can’t include the CMS as a registered server in its own server lists, it’s effectively outside of all management groups.  Therefore, when choosing a CMS, choose carefully – it needs to be a somewhat highly available server, since all of your DBAs will be relying on it for a server list, but at the same time, it can’t really be managed the same way, so it’s almost a throwaway.

My solution was to add a separate instance on a development box just to be my CMS – MyDevServerName\CMS.  That way, the box was reliable, but the instance doesn’t store any databases.

More About SQL Server’s Central Management Server

Here’s some more articles that take advantage of the CMS:

• Multi-server queries using SQL Server Management Studio 2008′s Group Query Execute feature.
• Policy-Based Management Training Video – Tom LaRock and I explain how to use PBM.
• Central Management Server Training Video – I recorded this for SQLServerPedia’s wiki.

Brent Ozar

Brent specializes in performance tuning for SQL Server, VMware, and storage. He's one of the very few Microsoft Certified Masters of SQL Server, a published author, and a Microsoft MVP. He likes travel, Jeeps, Apple gear, jokes, and writing about himself in the third person. Read more and contact Brent.

Website - Twitter - Facebook - More Posts