Printed 2015/10/08 12:06PM

SQL Server Policy Based Management Alerts

By peter.skoglund, 2012/12/04

SQL Server Policy Based Management Alerts


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)
On change: prevent (on demand)
On schedule
On change
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.
·     ·         


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.


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.

#Policy store
sl "SQLSERVER:SQLPolicy\YourPolicyStoreSqlServerName\YourPolicyStoreSqlServerInstanceName\Policies"

 #filter multiple policies from a policy store based on their PolicyCategory
 $policies =gci | Where-Object {$_.PolicyCategory -eq "YourPolicyCategory"}


 #Sql Server to check policies on
$srv=New-Object "Microsoft.SqlServer.Management.Smo.Server" "YourSqlServerName\YourSqlServerInstanceName"

foreach ($db in $srv.Databases)

                              foreach ($policy in $policies)
                                                          $resultList =   Invoke-PolicyEvaluation  -Policy $policy  -TargetServer "YourSqlServerName\YourSqlServerInstanceName" -TargetExpression "Server[@Name='YourPolicyStoreSqlServerName\YourPolicyStoreSqlServerInstanceName']/Database[@Name='$DatabaseName']"
                                                          foreach ($res in $resultList)
                                                                                       If ($FALSE  -eq [boolean]::Parse($res.Result))
                                                                                                                    If($NumberOfPolicyError -eq 0)
                                                                                                                                                 $Message=  "Policy error on " + $DatabaseName
                                                                                                                                                 Write-Output $Message | Format-Table -Wrap -AutoSize
                                                                                                                    $Message=  $policy.Name   +" " + $res.Result
                                                                                                                    Write-Output $Message | format-table -auto

                                                                                                                    $NumberOfPolicyError= $NumberOfPolicyError+ 1

                             If($NumberOfPolicyError -gt 0)
                                                          $Message=  "Policy error count for " + $DatabaseName + "=" + $NumberOfPolicyError
                                                          Write-Output $Message | format-table -auto

                                                          $NumberOfPolicyError= 0
                                                          $Message=  "Non violated policies on " + $DatabaseName
                                                          Write-Output  $Message | format-table -auto
                             Write-Output ""  |  format-table -auto

For easier reading of the sql server agent job history, I use the advanced option "Include step output in history"

Include step output in history

Check for error

The second step Check for error, uses PowerShell too and the code for the step is very similar to step one except throwis used to cause the Sql Server agent job to fail if any error is found.
 Check for error

SQL Server agent job history output

The result of SQL Server agent job history for invoking the policy evaluation on a database server

Agent job history

More reading

Copyright © 2002-2015 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.