Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006
join MSSQLTips for free SQL Server tips














































Configuring Alerts for SQL Server Policy Based Management

MSSQLTips author Ken Simmons By:   |   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.

configure Policy-Based Management to enforce standards on my SQL Servers

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.

create a SQL Agent Alert that will capture that error and send you a notification

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.

Give the Alert a meaningful name such as "Scheduled Policy Violation"

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.

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



Last Update: 7/6/2010


About the author
MSSQLTips author Ken Simmons
Ken Simmons is a database administrator, developer, SQL Server book author and Microsoft SQL Server MVP.

View all my tips
Related Resources


print tip Print  
Become a paid author




Recommended For You








Learn more about SQL Server tools
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
*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.