5

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

Improve this question
4
  • Good question; does it work?
    – Hannah Vernon ♦
    Nov 7, 2013 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, 2013 at 23:15
  • 1
    Then I'd say, in short, it works. Perhaps it is not supported because some things don't work.
    – Hannah Vernon ♦
    Nov 7, 2013 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, 2013 at 23:21

2 Answers 2

Reset to default
3

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.

Improve this answer
3
  • 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, 2013 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, 2013 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, 2013 at 19:41
1

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

Improve this answer

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.