http://www.sqlservercentral.com/blogs/sql-development-wizard/2012/12/04/sql-server-policy-based-management-alerts/

Printed 2014/04/18 08:02PM

SQL Server Policy Based Management Alerts

By peter.skoglund, 2012/12/04


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

http://www.mssqltips.com/sqlservertip/2214/policy-based-management/
http://www.mssqltips.com/sqlservertip/1493/evaluate-and-implement-policies-in-multiple-sql-server-instances/
http://www.mssqltips.com/sqlservertip/2054/configuring-alerts-for-sql-server-policy-based-management/

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