You cannot add a connection to your CMS server, on your CMS server. Well that is what I thought until SQLSaturday in Houston, where John Sterrett (Blog|Twitter) pointed out that you can use an IP address. I think Microsoft prevents this primarily because you already have a connection defined for it simply by virtue of it being your CMS. However, you may have the need to include it in a folder for the purpose of multi-query or PBM policy evaluations. I decided to do a little more research on the matter to better understand the internals. First let’s walk through the issue, then figure out how Microsoft is preventing it, and finally look at our workaround options.
The Issue
In the example below you can see that the server named “File2” is our CMS server at the root of the tree, and you can right click on it to perform several CMS actions.
If you double click it, a connection to File2 and only to File2 will be automatically opened in your object explorer window. However, if you right click and select Object Explorer it will open connections to every server in your CMS in the object explorer window. The same will occur against all servers in your CMS if you select “New Query” or “Evaluate Policies”. If you want to perform any of these actions against the CMS server alone then you will notice an option in the right click context menu called “Central Management Server Actions”. Choosing options from there will execute against the CMS alone and not every server connection defined within your CMS.
If your CMS (File2 in our example) is also a production SQL Server in the East region, we might need to add it to that folder. If we attempt to do that, here is what we get:
How it is prevented
When you use the GUI to add a server to CMS, it passes the parameters that you define to following stored procedure:
msdb.dbo.sp_sysmanagement_add_shared_registered_server
If you were to look at that stored procedure, you would see several checks on the server name that is passed to it. It is designed to disallow you adding a server with the same name as the CMS. It also will not let you add a server named “.”, “local”, or “localhost”. The check it uses to ensure you do not add a server connection with the same name as the CMS is to compare the name you passed to @@servername. The @@servername system variable holds the NetBIOS name of the machine, and in our case that is File2.
If you are curious, here is the part of the above stored procedure that performs the server name check.
IF (@server_name IS NULL)
BEGIN
RAISERROR(14618, -1, 1, '@server_name')
RETURN(1)
END
set @server_name = LTRIM(@server_name)
set @server_name = RTRIM(@server_name)
— Disallow relative names
IF (‘.’ = @server_name) OR
(1 = CHARINDEX(N’.\’, @server_name)) OR
(1 = CHARINDEX(N’LOCALHOST\’, UPPER(@server_name collate SQL_Latin1_General_CP1_CS_AS))) OR
(UPPER(@server_name collate SQL_Latin1_General_CP1_CS_AS) = ‘LOCALHOST’) OR
(UPPER(@server_name collate SQL_Latin1_General_CP1_CS_AS) = ‘(LOCAL)’)
BEGIN
RAISERROR (35011, -1, -1)
RETURN (1)
END
IF (UPPER(@@SERVERNAME collate SQL_Latin1_General_CP1_CS_AS) = UPPER(@server_name collate SQL_Latin1_General_CP1_CS_AS))
BEGIN
RAISERROR (35012, -1, -1)
RETURN (1)
END
Workaround Options
What does all that mean and how can you circumvent it? It’s quite simple. We just need to add the connection with another name that will resolve to our server other than the NetBIOS name. The easiest method is to add it using the server’s IP address (local loopback address can also be used). Of course that does not look very pretty in our CMS console, but we can alias it when creating the connection. Here is how we do that:
We have two other options for alternate names. As you can see in the above screen shot, we are in a domain named “Camelot”. This means we can use the FQDN of file2.camelot.com. The last option we have is to create an alias or CNAME record in DNS. Here is what it would look like if we added it twice in the East Production group using the IP address for one connection and the FQDN for the other:
