Configuring Alerts for SQL Server Policy Based Management


By:   |   Updated: 2010-07-06   |   Comments (5)   |   Related: 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 Updated: 2010-07-06


get scripts

next tip button



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





Comments For This Article




Monday, November 26, 2012 - 1:44:20 PM - David Back To Top (20545)

hello I fix the error 34052, SQL.?

I'm new to the world of databases. regards


Wednesday, July 13, 2011 - 7:36:09 AM - Tom Fox Back To Top (14162)

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.


Tuesday, July 12, 2011 - 9:49:13 PM - Ken Simmons Back To Top (14160)

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.


Tuesday, July 12, 2011 - 9:45:19 PM - Ken Simmons Back To Top (14159)

Thursday, July 07, 2011 - 9:12:38 AM - Tom Fox Back To Top (14142)

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?



download





Recommended Reading

Using Policy Based Management in SQL Server

Why SYSPOLICY_PURGE_HISTORY job fails in SQL Server 2008 Failover Cluster Instance

Monitor SQL Server Agent Jobs with Policy Based Management

Enforce SQL Server Database Naming Conventions Using Policy Based Management

Internal SQL Server Certificates have expired is this an issue








get free sql tips
agree to terms


Learn more about SQL Server tools