SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

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.

 

Ryan Adams

Ryan Adams is a Senior Premier Field Engineer for Microsoft. He works directly with customers to help them realize their business potential and accelerate their digital transformation on premises and in the cloud. Previously, Ryan was a Microsoft Data Platform MVP and spent 19 years working for a fortune 100 company. His passion is the SQL Server Engine, High Availability, and Disaster Recovery. He also served on the Board of Directors for the North Texas SQL Server User Group, was President of the PASS Performance Virtual Chapter, was a PASS Regional Mentor, and served as a Director for the PASS organization.

Comments

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

Loading comments...