Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm setting up a SQL Server 2012 lab on Windows 2008 R2 which has 5 SQL Servers. I registered SQL1 as my central management server and created a group under this server that includes all the servers and the CMS itself. I'm a bit confused since the documentation says the CMS cannot be a member of a group it maintains. However, I'm able to include it in the group, run multiple queries and evaluate policies successfully.

Reference: http://msdn.microsoft.com/en-us/library/bb895144.aspx see under "Related tasks"

I've seen other forum posts where users have issues registering the CMS itself but I don't have this problem at all. Has something changed?

I'm running SQL Server 2012 Enterprise SP1 and SSMS 2012.

Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64) Dec 28 2012 20:23:12 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1) (Hypervisor)

Here's the CMS returning queries for the whole group.

enter image description here

share|improve this question
    
Good question; does it work? –  Max Vernon Nov 7 '13 at 22:19
    
Yes Max, I ran a query on all servers and they all respond. I created a test policy which also evaluated against the CMS. Curious. –  Paweł Czopowik Nov 7 '13 at 23:15
1  
Then I'd say, in short, it works. Perhaps it is not supported because some things don't work. –  Max Vernon Nov 7 '13 at 23:17
    
That's why I ask - I'm wondering about the actual experience people have with this. I found forums where people had issues even registering the CMS under itself and worked-around by using IP instead of name. But these were SQL 2008 posts i believe. I'll update this post if i run into anything. –  Paweł Czopowik Nov 7 '13 at 23:21

2 Answers 2

I had also previously assumed that this wasn't possible at all, until I started playing around with it.

From what I can tell, the matching is only done by the server network name that's entered.

If you attempt to add a server to the tree with the same network name, it won't allow it; however, if you use a different network name, even though it may point to the same instance, it does work.

For example, I successfully registered my local instance .\SQL2008R2DEV as a CMS, and then successfully registered (local)\SQL2008R2DEV in the tree. (Tested in 2012 Management Studio.)

From a management perspective, this may or may not be a good idea. Furthermore, if Microsoft decides to tighten down the policy, any reliance on this ability will force you to change your process.

Certainly if SQL1 would be a production instance in your case, this is a bad idea because you want to separate management duties from serving user data duties. I strongly recommend using a separate instance of SQL Server just for CMS purposes, particularly in a production environment.

share|improve this answer
    
I agree this may not be best practice. Notice that I was able to register the server under the same exact name and so far, it works. I'm also curious as to why other cannot replicate this. I wonder what i'm doing wrong :) –  Paweł Czopowik Nov 11 '13 at 22:31
    
I've seen unpredictable results at different patch levels. I haven't ever tried to narrow down exactly which SPs changed it in which versions, though. –  Brent Ozar Nov 13 '13 at 19:15
    
Thanks, @Brent. I'd just as soon try to avoid the issue entirely, as I mentioned in my answer. But I suppose if something is allowed, then someone, somewhere, at some time will be using it, whether it's a good idea or not. :) –  Jon Seigel Nov 13 '13 at 19:41

Technically you can specify the central management server as the one under the managed groups. However, once the needed policies are tested (whatever their source is), the policies can be imported to the central management server which will perform the policies import to appropriate servers, including itself. This recursion should be tested as it may produce serious side effects. This is just a notification of the potential issue - I had no opportunity to test it

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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