Policy Based Management or PBM was introduced in SQL Server 2008 initially.This feature will help Database Administrators to create policies and ensure that his/her environment is in compliance with that policy.

Lets take a simple example of recovery model.Using PBM a DBA has got the ability to enforce a policy which will check for the recovery model and database creation with recovery model other than Full can be evaluated or even it can be enforced.

What about if DBA is running an environment with 100 production servers and he/she wants to evaluate a policy?

The short answer is we can use Central Management Servers to import policies and Evaluate them.

Demo

1. Using SSMS I created a policy called ‘Check_Autoclose_Policy_Enterprise’ which will use condition ‘Check_AutoClose_Condition_Enterprise’

You can ref BOL topic [here] to understand how to create a policy and a condition.

This policy simply means that I have set a condition with an expected value ‘False’ for Autoclose property and a policy is created for that condition.

If I evaluate that policy for my servers,it will check the condition for each and every database and will report me in case there are any deviation.If Autoclose = True,then I am notified.

2. Now I will proceed and export this policy.Export action will create an XML file and I will store the XML file to the local drive.

3. I will now proceed to use Central Management Servers(Available under registered servers).For the purpose of this demo I have added 2 Instances of SQL 2012 RC 0 already to Central Management Servers as show below.

 

4. Using Root level Central Management Server we can Import the policy which I had created.

5. Once import action is completed.The policy is spread across all the 2 instances.

6. For the purpose of the demo I had created 2 similar databases for both Instance B and Instance C.The name of the database is Test_PBM.

For Instance B – Autoclose is set as False

For Instance C – Autoclose is set as True

7. Using Root level Central Management server,we can now evaluate the policy

8. Once evaluation is completed,we are provided with a clear explanation on how the evaluation went through and which all databases were not according to our policy compliance.

The policy was created for Auto Close property to be False(Condition) for all the databases and after evaluation we found that there is one deviation and that is our Test_PBM database which is residing under Instance C.

 

Conclusion – The power of PBM and Central Management Servers can be combined for great control.

Have you deployed anything like this in your environment.I am interested to know your scenarios.

Thanks for reading.