I’ve grown up reading Tom Clancy and probably most of you have at least seen Red October, so this book caught my eye when browsing used books for a recent trip. It’s a fairly human look at what’s involved in sailing on a Trident missile submarine…
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.