Configuring Alerts for SQL Server Policy Based Management

By: Ken Simmons | Comments (4) | Print Ken is a database administrator, developer, SQL Server book author and Microsoft SQL Server MVP. Related Tips: More

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

Related Tips: More | Become a paid author

Last Update: 7/6/2010

Share:

Share

Tweet

 

Sponsor Information

Try the award winning SQL diagnostic manager as a free 14-day trial! Web-based SQL Server monitoring whenever, wherever. SQL Server Issues? Not sure where to turn for answers? Innovative SQL DBA consultants Solving SQL Server problems for millions of DBAs and Devs since 2006. Join now. Valuable SQL Server web casts on Performance Tuning, Development, Administration, Disaster Recovery, Replication and more...