2

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.

2

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.

| improve this answer | |
  • Yes, you can have multiple CNAMEs (DNS Aliases) pointing to the same IP. – John Eisbrener Feb 21 '19 at 16:59
  • I've tried with IP address and with a new CNAME in DNS. Both times, SSMS was smart enough to know that I was trying to register the same server as the Central Management Server. – Matt J Feb 21 '19 at 18:13
  • 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. I then registered the server by name as the CMS, and 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. – Matt J Feb 21 '19 at 18:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.