I’m sorry but I’m loving me some PBM right now.
Something that I’ve found to be very useful is creating a custom Policy Category. I have scheduled jobs that evaluates all the policies that belong to a specific category. So what I do is I categorize the Policies that I want ran based on when I want them run. Currently I have 2 custom categories (Daily and Weekly).
A question you may be asking is why wouldn’t you just check all your policies every day? If you’ve played with Policy Based Management and have been running it against multiple server, multiple dbs, etc you’ll know that it doesn’t take long before your job takes 15, 20, 30 minutes to finish… I’m a fan of minimizing the amount of “admin” work being done on my instances so if there are some policies that I don’t feel are critical then I wont run them every day.
Some Examples Below:
Daily – Last Successful Full Backup Date, Database has less then 10 Pct Free Space
Weekly - Database Auto Shrink, Data and Log File Location
Now these aren’t ALL my policies that I check but just some examples.
The daily policies are what I consider to be the most critical and I want to know the status every day so that if something does not conform it can be immediately actioned. Now I would have alerts and notifications setup for some of these scenarios as well but some things are important enough (backups) that I don’t like relying on only one check.
The weekly policies are ones that I consider to be VERY unlikely to change but still want and like the weekly sniff test to make sure things are inline with what my expectations are.
Creating a new Policy Category is as easy as right clicking on a Policy and going to the properties. If you click on the Description page on the left hand side you’ll see the following window:
If you click on the “New” button you can enter a new category and then start assigning policies to that category.
And once you’ve started going “Category-Crazy” you might be wanting a query to see your categories and the policies assigned to those categories so I’ll save you some trouble:
SELECT B.name as 'CategoryName', A.name as 'PolicyName', A.is_enabled FROM msdb.dbo.syspolicy_policies_internal A INNER JOIN msdb.dbo.syspolicy_policy_categories_internal B ON A.policy_category_id = B.policy_category_id ORDER BY B.name, A.name
(I promise the next post will not be about PBM…. well I’ll try at least LOL )