Policy Management Report

Policy-Based Management

What is it?
New to SQL Server2008, allows you to define and enforce policies
A policy can force developers to follow certain guidelines.
Here are a couple of examples of policies (we’ll be setting these up in class)
All stored procedures must begin with the letters “Usp”
All tables must have primary keys




Policy management consists of four components:
Target
An object which can be managed.
An example is a stored procedure or a table
Facet
A predefined set of properties that can be managed
Condition
A condition is something that will be evaluated to either True of False. A condition can check one or more statements using and/or.
Using our example from above, all stored procedures must begin with the letters “Usp”.
Policy
A condition to be checked and enforced.



A policy has four evaluation modes:
On Demand
On Demand lets the admin check the policy and receive a list of violations
On Schedule
On Schedule lets the admin schedule the checking of a policy at specific intervals.
On Change - Log Only
On Change - Log makes an entry into the database log every time a change occurs that triggers a violation.
On Change - Prevent
On Change - Prevent prevents a change that would violate the policy.



The following SQL snippet displays a 1 if the table named ‘Person’ has a primary key and nothing if it doesn’t.

select 1
from sys.tables t
inner join sys.indexes i
on i.object_id = t.object_id
where i.is_primary_key = 1
and t.name = 'Person';

The following snippet evaluates to 1 if the table being evaluated has a primary key and to nothing if it doesn’t. This is entered in the field area.

The oprator is =
and the value is 1

The condition therefore returns true if the table being evaluated has a primary key and false if it doesn’t.

ExecuteSql('Numeric','select 1 from sys.tables t inner join sys.indexes i on i.object_id = t.object_id where i.is_primary_key = 1 and t.name = @@ObjectName')