Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Ryan Adams

Ryan Adams has worked for Verizon for 15 years. His primary focus is the SQL Server Engine, high availability, and disaster recovery. Previously he was a Senior Active Directory Architect and designed the company's worldwide Active Directory infrastructure. He serves on the Board of Directors for the North Texas SQL Server User Group and is President of the PASS Performance Virtual Chapter. He also serves as a Regional Mentor for PASS and holds the following certifications: MCP MCSA MCSE MCDBA MCTS MCITP.

Setup Alerts for PBM

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.

 

Comments

Leave a comment on the original post [www.ryanjadams.com, opens in a new window]

Loading comments...