Multiserver Management with SQL Server 2008’s Central Management Server

My friend Lara Rubbelke, who works for Microsoft in Minneapolis, sent me an email message the other day asking me what I’ve heard from my students and other contacts about one of her favorite SQL Server 2008 features called Central Management Server (CMS). I remembered that I’d seen this feature in a few demonstrations prior to the general release of SQL Server 2008, but I had to reluctantly admit that I hadn’t really spent much time exploring this feature and hadn’t seen a lot of discussion about it, at least not on the blogs that I read. So maybe it’s time to change that.

One of the nicest things about having a CMS is that DBAs and users across an enterprise can have the same registered servers available in SQL Server Management Studio (SSMS). In SSMS 2005, each client stores its own local server groups, so each person in an organization might have different server groups and instances available. When moving to a new client machine, you have a completely different setup of available servers and basically have to start from scratch.

If you set up a CMS in SSMS, you can define server groups and then the definition of your groups will be stored in the msdb database of the server that you’ve defined as the CMS. Then, anyone choosing the same servers as the CMS will automatically have the same server groups defined and available.  You can take advantage of this feature even if you’re not using SQL Server 2008 in production yet, all you need is SSMS 2008. You can add instances of SQL Server 2000 or later to the server groups managed by your CMS.

Another great feature of a CMS is the ability to run a query against multiple SQL Server instances. (This feature is often shown in SQL Server 2008 demos.) This group of instances could be mixed versions, as long as the statement that you’re trying to execute is supported on all the included versions. Consider a scenario in which you need to make a mass configuration change for all instances in your production environment.  You can now select the appropriate group in the CMS and run the query to make a configuration change.  This is now a single query running against a group of instances.  Note that you can also run SELECT statements against a group of servers, which could be useful if you want to determine the service pack, version, and edition of all instances in production. 

I have seen a lot of discussion, as well as conference sessions, about the new Policy-based Management feature in SQL Server 2008, and CMS ties in very nicely with it. Once you have policies defined, you can issue a request for on-demand evaluation of those policies against all the servers that have the same policy definition. Or, because you might be running SQL Server versions that don’t support Policy-based Management, you can run commands using straight T-SQL to enforce or check policies manually.

Even if you don’t have a lot of SQL Servers to manage, having a CMS can simplify your administration requirements between your development, test, and production servers. After looking at this feature I can understand why not a lot has been written about it, at least not in the blogs I read. It’s not that complicated of a feature, and there’s not a lot of secret internal knowledge that can enhance your use of it. It’s just a client-side shortcut, but one that can really shorten the time you spend on some basic management tasks.