I like the idea of setting up a Central Management Server to run queries against other servers that I have to maintain. I'd like to be able to run one query to get information from all servers, like log sizes, backup history, index fragmentation, etc.
I'm just not sure how to set it up correctly so that I can also run these queries against the host database. It seems crazy that you can't manage your Central Management Server.
How do people get around this? Do you create a specialized instance whose sole purpose is acting as the management server? Or pick a server that you just don't care about the health?
I don't want to have to run one query for the single server, and a second for the groups. That seems to defeat the purpose.
Am I missing something?
Update with solution copied from comment below
Oh snap! I stand corrected.
My original attempt was to register the CNAME/Alias as the CMS, then register the server by name. This always failed.
Second attempt, registered the CMS using the server name. I then registered the server using the IP address and this worked. I don't have clue how they're different, but it works so I'm happy.
Best Answer
I assume that we are indeed talking about Central Management Servers here. And not Master/Target Agent or something else which isn't related to CMS. I see a lot of confusion in this area. Here are a few options:
Skip the CMS. You can register whatever SQL Servers you have in the traditional way (locally) and use a multi server query window (MSQW) for the local server group. Again, I frequently see folks believing that MSQW can only be used with CMS.
Just register the SQL server wherever you want to have it in the CMS tree, but use the IP instead of the machine name. SSMS won't understand that it is the same machine.
Configure an alias on the client, and use that alias name in the server registration. Obvious downside is that all clients where you want to use the CMS list need to have that alias. Sort of defeats the purpose of CMS in the first place.
Perhaps an additional DNS entry pointing to your server? I'm no DNS person, so I can't say if you can have two DNS entries pointing to the same IP.
I'd go for the IP address in the CMS registration. In fact, I did exactly just that a few days ago with a client.