PBM raises errors for policy violations.  We can create alerts on those errors to be notified of policy violations.  In order to setup alerts on these errors there are three prerequisites.  Note that violations for the “On Demand” evaluation mode do not raise errors.  A policy must be set to “Enabled” to raise an error, and On Demand policies are always in a disabled state. Here are the prerequisites to setting up alerts on your PBM SQL Server.

• Database Mail must be enabled and configured
• Operators must be created to receive notifications of alerts
• Alert system notifications must be enabled on your SQL Server Agent

There are four error numbers that we can setup alerts for, and that will provide us with notifications for any policy using any evaluation mode.  Here are the error numbers we want to setup alerts for.

• On Change: Prevent (Automatic) – 34050
• This occurs when a user runs a transaction that raises a DDL event that violates a policy
• On Change: Prevent (On Demand) – 34051
• This evaluation method can also be run in a manual On Demand fashion, in which it raises a different error number
• On Schedule – 34052
• On Change: Log Only – 34053

Let’s take a look at creating an alert for an “On Schedule” error.  Right click alerts under SQL Server Agent in SSMS and select New Alert.  In the Alert Properties box we need to give the alert a name and enable it.  We need to select which databases we want it to apply to, and select that the error number to alert on will be 34052.

Now we need to select the Response page in the left hand pane.  Here we want to select “Notify Operators” and check the email column for the operator we want to be notified.

Last we need to select the Options page in the left hand pane.  Here we want to select to include the error text for email notifications.

Follow these steps to create alerts for the remaining three error number and you will now be notified of any policy violations that occur.