I have been working on some new materials for an upcoming webcast on leveraging Policy-Based Management using the Enterprise Policy Management Framework. As I was working through some sample policies, I noticed that my SQL Server 2008 instances were not consistently configured for history retention and to log on success. Well, I thought this was a good thing to put in a policy, as policies allow me to easily manage configuration settings for my instance. Much to my surprise, SQL Server 2008 does not currently have a Policy Based Management Configuration facet! I logged a suggestion to connect.microsoft.com (https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=419574), and hopefully we will see a new facet in the future. Please feel free to vote if you think this would be a helpful addition.
In the meantime, we still need to manage our PBM configuraton in our SQL Server 2008 installations. So I developed a policy which will execute some scripts and report the state of our environments. I cannot force configuration to my policy when conditions are defined with scripts, but at least I have a scalable solution to report on those instances which do not conform.
The attached policy defines the PBM management settings. I typically like to change the PBM Management properties such that the policy history is only retained for 14 days and successful evaluations are logged. If you care to look at these setting in Management Studio, right click on Policy Management to select properties.
- When LogOnSuccess is set to false (default) successful evaluations are not logged. Setting LogOnSuccess to true will log all policy evaluation results. You will want to log on success if you decide to centralize policy history and perform enterprise management reporting. Without the log on success, you will only have failed evaluations. This makes it difficult to determine if a policy is currently in a failed state or if it was in a failed state in the past but is currently in compliance with your specifications.
- HistoryRetentionInDays will determine how many days of policy history will be saved in the policy history tables in MSDB.
The attached policy is not configured to run on a schedule. You may want to change this setting prior to importing the policy to your SQL Server 2008 instances.