Policy-Based Management with Central Management Servers

                As a database administrator, you may be in charge of multiple databases.  But often times, it’s the multi-server/instance burden that becomes the most “click” intensive.  What I mean by that is you have to connect to multiple servers and multiple instances, or in SSMS connect your Object Explorer to a bunch of different instances, and before you know it you have a spaghetti cluster of instances on your desktop.  You end up spending 90% of the time just mis-clicking the wrong one, or worse, executing T-SQL against an unintended instance.

                Sometimes, the above scenario is necessary.  But for much object/database/instance/server administration, this can be carried out with PBM (Policy-Based Management) with the combination of CMS (Central Management Servers).  This central location allows for the convenient method for do many tasks, and this blog post will focus on PBM with CMS.

                Policy-Based Management in and of itself is a great addition with SQL Server 2008.  But when you have dozens (or even hundreds) of instances that require a standard set of policies and conditions, manually pushing them out and evaluating them would be absurd (and let’s be honest…we should be automating at every possible junction).

                The first step to utilizing PBM with CMS is registering a Central Management Server:  Through SSMS in the Registered Servers section, right-click on Central Management Servers and select Register Central Management Server….  Select a SQL Server 2008 or higher instance to act as the CMS:

                To logically group your servers, I recommend creating a Server Group:  Right-click on the CMS and select New Server Group….  Name the Server Group to your desire.  I will be calling mine “2008R2” as this Server Group will house my 2008 R2 instances, and “2012” to contain my 2012 instances:

                Now I want to register a server in my 2008R2 Server Group:

                My Registered Servers pane should look like the below now.  Mind you, there are only a couple of instances here and this is a very over-simplified implementation of CMS:

                There are a couple of ways to approach PBM with CMS:  1) Create a Policy on the Central Management Server and use that to evaluate against managed servers, or 2) create a Policy and export/import to the managed servers.  I will be focusing on the former method.

                Go to the CMS and create a Policy.  Because this is not an in-depth look at the extreme flexibility and power of PBM, I will just use an MS-shipped out-of-the-box Best Practices Policy to import as an XML file.  In the Object Explorer of my CMS I will go into Management, Policy Management.  Right-click on Policies and select Import Policy….  Navigate to the desired Policy File (XML file format) and select it.  I chose the famous Database Auto Close Policy to work with:

                Now that I have a Policy created on my CMS, so I will not worry about duplicating this effort and exporting/importing into my registered servers.  Back at the Registered Servers SSMS pane, I want to be able to evaluate this Policy against all of the servers registered under my CMS named “CMS”.  I simply right-click on the CMS and select Evaluate Policies….  Now I will need to select the source.  In this case, my CMS will house all of the Policies, so I’ll choose this server.  

Select the Policy(ies) that you want to evaluate against the registered servers, and click Evaluate.  You should be staring at your evaluation results for the registered servers in the scope of where you selected to evaluate policies:

 

                And there it is!  We have just used the administrative power of Policy-Based Management, and ease of Central Management Servers to evaluate a Policy against a subset of registered servers.  If there are any comments, questions, or issues please feel free to leave a comment or email me at sqlsalt@gmail.com.