Setup Alerts for PBM

Ryan Adams, 2011-12-07

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.

 

Rate

Share

Share

Rate

Related content

Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.

Robert Davis

2009-02-23

1,567 reads

Networking – Part 4

You may want to read Part 1 , Part 2 , and Part 3 before continuing. This time around I’d like to talk about social networking. We’ll start with social networking. Facebook, MySpace, and Twitter are all good examples of using technology to let…

Andy Warren

2009-02-17

1,530 reads

Speaking at Community Events – More Thoughts

Last week I posted Speaking at Community Events – Time to Raise the Bar?, a first cut at talking about to what degree we should require experience for speakers at events like SQLSaturday as well as when it might be appropriate to add additional focus/limitations on the presentations that are accepted. I’ve got a few more thoughts on the topic this week, and I look forward to your comments.

Andy Warren

2009-02-13

360 reads