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
