Blog Post

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.
#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"}


$NumberOfPolicyErrors=0


 #Sql Server to check policies on

$srv=New-Object "Microsoft.SqlServer.Management.Smo.Server" "YourSqlServerName\YourSqlServerInstanceName"


foreach ($db in $srv.Databases)

{

                             $DatabaseName=$db.Name


                              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

                             }

                             else

                             {

                                                          $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

Rate

Share

Share

Rate