SQL 2008 Central Management Server

I have been using the Central Management Server feature of SQL 2008 since, well, about 2008. And I have found it to be quite useful on occasions. But there are times when it is just frustrating to use. Let me go over a handful of the good and the bad.

The Good

When new people join your team, they immediately get a list of all the servers. Compared to the old school way of having to share your settings in Enterprise Manager, the CMS is simply wonderful. All you need to do is tell the new team member to register the CMS and boom goes the dynamite. And you can easily export/import the list, which is quite nice.

Another great feature is that you can group your registered servers logically, making it easy for you to run queries against multiple servers. For example, you could put all of your SQL 2000 instances in a directory name ‘SQL2000’, right-click, select ‘New Query’, and then execute some T-SQL against all of the registered instances at the same time. Or you could evaluate policies against all servers, if desired.

Lastly, another great thing about the CMS is…wait…that’s it. There is nothing else.

The Bad

Well, for starters, it only uses Windows Authentication. Oh, sure, we would all like to be handed servers to administer that exist only inside of our domain. But the reality is that we are often handed boxes outside of our domain (think a DMZ, firewalls, or a hosted provider) and we often need to use SQL authentication. For those particular cases the CMS is useless, outside of sharing just the server name with other team members. Oh, sure, I could save my CMS list and import it to my Local Server Group, but the SQL passwords are not stored so that is really not a solution either. I would still end up needing to put the user id and password on a Post-It note, take a photo, and email it to my entire team.

Another issue is the fact that you cannot select multiple folders inside of the CMS. Let’s say I have grouped my servers into three directories, SQL2000, SQL2005, and SQL2008. If I want to run a query against just the SQL2000 and SQL2008 servers I would need to create a new directory because I cannot select two distinct folders. It is either all or nothing. I’ve lost track of the number of times I have wanted (or needed) to be able to select a subset of servers from different directories.

And if I create additional directories inside of the CMS for those special cases then I have a server listed twice, and that means when I go to execute against all of my instances I end up executing against those servers twice.

Another item about CMS that you need to be aware of is that if that server where you have registered your CMS is down, then so is your list. So, it is a good idea to keep your list saved somewhere so that you can quickly and easily create a new CMS in the event of a disaster.

The Ugly

Earlier this year I was honored to be asked to be the technical reviewer for the book Pro SQL Server 2008 Policy-Based Management. As a result I had the opportunity to learn some Powershell code. One of the pieces of code allowed for me to connect to a CMS and grab the name of all the registered servers. As cool as that little piece of code was to come across it was really no different that doing a right-click on the CMS inside of SSMS.

So I did what anyone else would do when faced with the prospect of chewing tinfoil along with your baked potato, sour cream, and bacon. I asked the Most Interesting DBA In The World.

Buck Woody (blog | twitter) and I were having a conversation regarding Powershell and I mentioned that I was connecting to the CMS in order to get a list of server names. Buck immediately questioned why I would want to do it that way because as most great DBAs we always like to ask the simplest question: “why are you doing it that way?”

Buck explained that a better way to get the list of server names from the CMS was to just dive into the msdb database and get them from their instead.

“Huh?” I replied. “I didn’t know they were there, I thought the CMS was a registry thingy.”

Sure enough, they they are. They sit inside of two tables:

msdb.dbo.sysmanagement_shared_server_groups_internal
msdb.dbo.sysmanagement_shared_registered_servers_internal

And being the fact that this was Buck Woody, it came as no surprise that he had already written a thing or two about this. So, the MIDBAITW pointed me to a piece of Codeplex and then to a series of articles where I found this piece of code:

/* Put it all together */
SELECT DISTINCT g.name AS 'GroupName'
, s.server_name AS 'ServerName'
FROM msdb.dbo.sysmanagement_shared_server_groups_internal g
INNER JOIN msdb.dbo.sysmanagement_shared_registered_servers_internal s
ON g.server_group_id = s.server_group_id
ORDER BY s.server_name ;
GO

So, now I can just use an Invoke-Sqlcmd command in Powershell in order to get the list of servers I really want from the CMS, which is real nice.