Managing SQL Servers With Policy Based Management

Policy Based Management has been in SQL Server since 2008 and allows you to define policies that can report issues when certain conditions are violated, it can also prevent changes that would violate a policy. It does this in a couple of ways…

• On Demand : You execute the policy manually
• On Schedule : A SQL Agent job executes the policy on a schedule
• On Change : Depending on the facets you use you may be able to use on demand which uses DDL triggers to trigger the policy to run when a change is made that meets the policy condition. For on Change policies you have the option to prevent the change if it violates the policy or let the change go through but log the issue.

Policy based management comes with a large number facets which are objects and properties that you can evaluate and fail the policy if it doesn’t match an expected result.

I’m going to run through a couple of examples of policies that you could create to demo their usefulness…

Deny Create Index If Index Name Contains Missing Index

Lets create a rule that will fail if anyone creates an index with missing_index in the name, and lets also schedule this to run every morning.

First up we need to define the conditions for this policy. Let’s create a new condition that will ignore system databases from our check…

Now create another condition to check the index name does not contain the string missing_index…

In Name Condition" />

With these 2 conditions created we can now define a policy that runs on all databases that are not system databases and checks every index in them for missing_index in the name…

Once this is created if you look under SQL Agent\Jobs in SSMS you’ll see a new job has been created to run our policy at the scheduled time that is named something like

syspolicy_check_schedule_FC8D54AD-2B54-4AF7-A349-5F87CC7B1EE2 

If you now execute the job you’ll then be able to view the policy results by looking at the history under Policy Management…

Using this process, you can have a good deal of your morning checks run before you come in to work then review the results under the policy management history.

Check Full Recovery Databases Have Done Log Backups Within X Minutes

Let’s create another policy to check all Full and Bulk recovery model databases have done a log backup within the last 5 minutes.

We need a new condition to filter the databases to just ones in full or bulk recovery.

Then we need a condition to check the last log backup is within 5 minutes.

Lastly we need to create the policy and add it to our MorningDBAChecks Schedule.

Log Backup Policy" />

On Change/Prevent

Only some facets are available to be used with the On Change evaluation mode as this works using DDL Triggers, facets that can’t be triggered in this way can’t be used with the On Change option.

The facets available are all stored in msdb..syspolicy_management_facets which has an execution_mode field that defines what modes you can use (Demand, Schedule, On Change Log, On Change Prevent). This field is a bitwise flag and can be checked with this script