SQL Server Policy Based Management Alerts

SQL Server Policy Based Management Alerts

Problem

Policy-Based Management is used to enforce standards on SQL Servers and it's great. But not all policies support "on change prevent mode" and the rest are only logged in the event log. The error number that is recorded depends on which evaluation mode was used to execute the policy. The different error numbers for each evaluation mode in are below.

Evaluation Mode

Error Number

On change: prevent (automatic)

34050

On change: prevent (on demand)

34051

On schedule

34052

On change

34053

Policy Based Management error table

Here you can see an example of a policy error in the event log, notice what is missing? The faulting database name! By checking the event log you get the usual info, which policy that was violated, the sql server instance, date, time etc. but not which database that the error occurred on!

Policy Based Management error is recorded in the event log

Also the alert system for when policies have been violated is not so great; here are some examples on how you can handle it:

·Manually look at each server and check the event log

·Set up SQL Agent Alerts to capture these errors in the event log and trigger emailing the operator.

·Set up a Central Management Server and manually run and evaluate policies.

· ·…

Solution

One solution is to create a SQL Server agent job that invokes the policy evaluation on a sql server and logs the result to the history. The agent job is schedule to run regularly and if any policy errors occur the job is failed and by using notifications an email is sent to the dba. The history output for the SQL Server agent job customized to include the failing policy and database name, you can of course change it to whatever you like.

The details

Create a SQL Server agent job with two steps, both of type PowerShell as below

SQL Server agent job with two steps

Notice that each step needs to run as an account that has rights on the servers it accesses. I used a proxy account for that.

Invoke-PolicyEvaluation

The first step Invoke-PolicyEvaluation, uses PowerShell

Invoke-PolicyEvaluation step

Detailed code

Just replace "YourPolicyStoreSqlServerName" ,"YourPolicyStoreSqlServerInstanceName" and 'YourSqlServerName", "YourSqlServerInstanceName" and "YourPolicyCategory" in the below PowerShell code.