Categories
DBA

Configuring Alerts to Notify Policy deviations in Policy Based Management

In this article we are going to configure alerts for policy management. Alerts are triggered when a violation takes place in a particular policy that uses any of the three automated evaluation mode. Each of these evaluation mode will return an error number starting with 340*. You can use these alerts to notify either policy administrator or the database administrator who needs to make sure these values are under company standards. Upon receiving these alerts they can work with the user who has violated the policies or company standards. Error number for each of these evaluation mode is given in the below table.

Evaluation Mode
Error Number
On change: prevent (if automatic)
34050
On change: prevent (if On demand)
34051
On schedule
34052
On change
34053

Ok, Now its time to test these alerts. Ive already configured a policy (On change: prevent) in my local machine to check the table names which should be suffixed with tbl_, if this naming convention fails then it should throw a error. Here in this case Im going to evaluate the policy as On change: prevent (if automatic) hence the error number will be 34050 (from the table above). Lets create a alert and an operator to check this.

Operator:

Ive created a operator named Policycheck and provided a mail address to send the alerts to that mail ID. Im going to use this operator in the alert that is going to be configured for PBM

pbmalert1

Alert:

The next step in configuring the PBM alert is to create a alert and provide the error number. Ive created a alert named Tbl-Policy Check. In my testing case the error number will be 34050 since Ive used On Change : Prevent mode with automatic evaluation. For more on error number see the table above.

pbmalert2

In the response page of the alert Ive selected the operator which is created in the step1 and assigned to mail to that operator when this alert is invoked, i.e. when the policy fails this alert will be invoked.

pbmalert3

Testing Policy Based Management Alert

Ok now we have created the alert for error number 34050, lets create a table without the naming convention mentioned above. When I create a that table PBM should automatically prevent the table creation and invoke the alert. Upon invoking the alert it should send a mail to the operator specified. When I created the table PBM thrown a error below

pbmalert4

You can also find that an event is inserted in the application log of event viewer.

pbmalert5

Cool, PBM has prevented the table creation. Ok lets check whether I got the mail for this failure, great I got the mail below from the operator

pbmalert6

Consideration on PBM Alerts

Be aware of the following additional considerations about alerts:

  • Alerts are raised only for policies that are enabled. Because On demand policies cannot be enabled, alerts are not raised for policies that are executed on demand.
  • If the action you want to take includes sending an e-mail message, you must configure a mail account.
  • Alert security: When policies are evaluated on demand, they execute in the security context of the user. To write to the error log, the user must have ALTER TRACE permissions or be a member of the sysadmin fixed server role. Policies that are evaluated by a user that has less privileges will not write to the event log, and will not fire an alert. The automated execution modes execute as a member of the sysadmin role. This allows the policy to write to the error log and raise an alert.

Policy based management is a new feature that allows the administration simpler, In addition to this if you configure the alerts for PBM then it will make the administration much more simpler to make the standards correct. Most of the critical business needs to be notified when there is a deviation in their standards, you can implement policy based management with alerts which will help the administrators to resolve this as soon as possible.

 

Leave a Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

*