Sql-server – Multi-server administration – force poll on target fails, server needs to be registered locally without FQDN

central-management-serversql-server-2012

I'm experimenting with multi=server administration on my SQL lab. Running SQL 2012 Enterprise SP1 on Windows 2008 R2 on a domain.

I've created a domain account for my sql server agent user on each server including the master. I was able to create the Master and set Targets to two other servers. The targets are added via the registered servers (FQDN) on the CMS which is also the master.

When i go to the master, right click > Multi-Server Administration > Manage Target Servers, i see the servers are listed using the short sever name not FQDN. When I click Force Poll, I get an error message:

This operation requires SQL3 server to be a registered server. (SqlManagerUI)

In order to work around this, i have to setup a LOCAL server registration and use the short name SQL3 instead of a FQDN.

This seems rather inconvenient to say the least – why do i have to register each server locally to use this feature? Why is it relying on the short name and not FQDN. These servers are already registered in my CMS which is also the master server. I could see this being a big problem when managing many servers – would i have to locally register a 100 servers??

Best Answer

I know it's been a while, but I have the same issue trying to manage multiple domains. (Avoid using the same server name in more than one domain.) The Multi-server feature seems to be very limited, but it could be so much better with a little effort. I figure it will be removed from the product one of these years, fixed, and then added back as a great "new" feature in the ENT edition.

I think the limitation is in the extended procs such as xp_msx_enlist code. They determine the name of the local instance. If it was T-SQL code, it might have been easier to fix. As far as I can tell, the only place the target name is stored is in msdb.dbo.systargetservers table on the master server. If the targets could be tricked into requesting a sync for the FSDN, then the table could have be updated (hacked) directly to use the FQDN.

If it was open source, it would have been fixed years ago. It would have had more features by now too.