Configuring Alerts for SQL Server Policy Based Management

By: Ken Simmons   |   Read Comments (5)   |   Related Tips: More > Policy Based Management

Problem

I have configured Policy-Based Management to enforce standards on my SQL Servers. This is fine for the policies where you can prevent the changes; however, many policies can only be logged and not prevented. How can I receive notifications when the policies I have implemented have been violated so I don't have to manually look at each server?

Solution

Whenever a policy violation is logged, an error is recorded in the event log. You can use SQL Agent Alerts to capture these errors and trigger an event such as emailing an Operator. The error number that is recorded depends on which evaluation mode was used to execute the policy. You can see the different error numbers for each evaluation mode in the following table.

Evaluation Mode	Error Number
On change: prevent (automatic)	34050
On change: prevent (on demand)	34051
On schedule	34052
On change	34053

You should note that in order for an error to fire, the policy must be enabled. Even if you manually evaluate a policy, if it is not enabled, no error number will be logged. You can see an example of a scheduled policy that has been violated and logged in the following image.

Now, all you have to do is create a SQL Agent Alert that will capture that error and send you a notification. Expand the SQL Server Agent node in SQL Server Management Studio, right-click the Alerts folder, and select New Alert from the context menu to display the New Alert dialog box shown in the following image.

Give the Alert a meaningful name such as "Scheduled Policy Violation", select the Error number option, and enter the error number in the text box. Next, select the Response page shown in the following image to finish the configuration.

Select the Notify operators check box along with the notification method you would like. Alternatively, you could perform some other action here by executing a job in response to the violation. You can repeat the process for each evaluation mode which you would like to recieve an alert if a violation occurs.

Next Steps

In order for these Alerts to work you have to configure Database Mail, create an Operator, and enable Database Mail in SQL Server Agent. You can review the following links for more information on configuring those prerequisites.

• Setting up Database Mail for SQL 2005
• SQL Server Database Mail and SQL Agent Mail setup by using a script
• How to setup SQL Server alerts and email operator notifications

You can review the following tips for more information on Policy-Based Management

• Using Policy-Based Management in SQL Server 2008
• Evaluating and Implementing Policies in Multiple Instances in SQL Server 2008

Last Update: 7/6/2010

About the author

Ken is a database administrator, developer, SQL Server book author and Microsoft SQL Server MVP.

View all my tips

We Recommend

• More SQL Server DBA Tips...

Comments and Feedback:

Thursday, July 07, 2011 - 9:12:38 AM - Tom Fox	Read The Tip
Great article and I've been using this with some success.  One issue I have is that the email/page I receive states this:   DATE/TIME:         7/7/2011 9:10:58 AM DESCRIPTION:         Policy 'P_Database_Config' has been violated. COMMENT:         (None) JOB RUN:         (None)   Is it possible to get the instance that violated the policy as well?

Tuesday, July 12, 2011 - 9:45:19 PM - Ken Simmons	Read The Tip

Tuesday, July 12, 2011 - 9:49:13 PM - Ken Simmons

Read The Tip

I usually set my servers up so that when they send an email, it has the server name as the sender. You can see an example here (http://cybersql.blogspot.com/2008/06/script-to-setup-database-mail.html). If you can't do that, you could always add the server name in the Options page for the alert where it says "Additional notification message to send". I know the server name would be hard coded there, but you should be able to script the Alert where it dynamically hard codes whatever server it is being run on.

Wednesday, July 13, 2011 - 7:36:09 AM - Tom Fox	Read The Tip
Thanks for the reply! My servers are setup that way too, including the instance that is running PBM.  I noticed the Options page too, but can't find any documentation on how to script it. :/ Even still, I'm not sure it would know (at the alert level) that something was run elsewhere.

Monday, November 26, 2012 - 1:44:20 PM - David	Read The Tip
hello I fix the error 34052, SQL.? I'm new to the world of databases. regards

Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name

*Email

Notify for updates Signup for our newsletter

Comments *** NOTE *** - If you want to include code from SQL Server Management Studio (SSMS) in your post, please copy the code from SSMS and paste the code into a text editor like NotePad before copying the code below to remove the SSMS formatting.
*Enter Code

 

Sponsor Information

SQL Server having some performance issues? Idera SQL check. FREE SQL Server enhancement. SQL Developer Bundle: Cut out dull work with 12 tools for simpler, faster database development. Free trial Need SQL Server help and not sure where to turn? Reach out to expert consultants in the USA for a Health Check. Free SQL Server performance monitoring software! Improve performance by 65% today with IgniteFree. The SQL Server Security THREAT - It’s Closer Than You Think