Using Central Management as Documentation

I hope you are using Central Management Servers to manage your environment; if not, that’s okay, but I highly recommend taking a look at Brent Ozar’s blog where he talks about how to configure a Central Management Server and some of the pros and cons. Then, if you really want to dive further into using Central Management, you can use Policy Based Management to help manage your environment; for that I recommend reading Jorge Segarras blogs on Policy Based Management. It’s amazing what you can do, and Jorge and Brent really lay the ground work for you to manage any size environment using Central Management Servers.

You might be saying, “Okay, that’s great and wonderful, but what does that have to do with documentation?” Great question! If you are like me, you will probably set up your registered servers using groups to classify what they are: Development, QA, and Production. The names may vary, but you get the point. You might even go as far as to sub-classify them into support tiers or SQL Server versions. The possibilities are endless, but you get the point.

Now, as you can easily see, I have a server named SQLONE that is a production server in the Tier 3 level of support. This is great, but you can’t tell what each level means. Well, this is where a commonly overlooked text box comes in very handy. When you are setting up the server group or registered server, there is a description box that many skip. Don’t do it! Fill in the description with some helpful information like the description of each support level or the Application and Point of Contact for a particular server. Then, when you hover over a server or server group with your mouse, a tool tip will appear with the description you entered.

This can be very useful; for instance, if you have given rights to a Business Analyst or Developer to view the Central Management server list, they can easily see what the support levels are, what application each server supports and the person to contact about that app.

The best part is that all this data is stored for you in tables on your Central Management server and can easily be queried through the two views sysmanagement_shared_registered_servers or sysmanagement_share_server_groups, or through the two underlying tables sysmanagement_shared_registered_server_internal or sysmanagement_share_server_groups_internal. So once you have created your server list, you have an inventory of your servers that you will keep up-to-date because it will be something you are looking at and touching on a daily basis.

I am working with CMS and maintaining compliance. I am trying to create and schedule job with a policy and check the role of availability replica on all servers listed in a server group(AG Servers- with Server Groups Test, Dev, Prod- Each listed with multiple servers). I am not sure about how to include the power shell script in a job step to refer back to apply the policy check (from SQL scheduled JOB) on all servers included in (CMS- Server- Server Group-Server groups- Server Instances). Need some power shell scripts to point to and if this is appropriate?

I would recommend checking out the Enterprise Policy Management Framework here. This is what I use to execute policies across my environment. The documentation is well done but feel free to send me any questions or post it on the discussion board.

I have created policy with a condition to check for the Primary role of Availability Replica. Now, I need to use a SQL Job step(with Power Shell scripts) where I can use my policy to check against the list of servers inside server groups of a server group in CMS.