Microsoft® SQL Server® 2012 Bible

543

Chapter 20: Policy Based Management

20

Facet

On

Demand

On

Schedule

On Change

Log Only

On Change

Prevent

UserDefi nedDataType 1 1 0 0

UserDefi nedFunction 1 1 1 1

UserDefi nedTableType 1 1 0 0

UserDefi nedType 1 1 0 0

Utility 1 1 0 0

View 1 1 0 0

Volume 1 1 0 0

WorkloadGroup 1 1 1 1

XmlSchemaCollection 1 1 0 0

The fourth key selection on the general page is Server Restriction. You can use this option

to defi ne the target servers based on criteria.

PBM generates DDL triggers that enforce the policy and roll back DDL operations that don’t comply with the policy.

Don’t manually delete or edit these DDL triggers. Also, servers that use PBM must have nested triggers enabled.

PBM also generates agent jobs for policy automation. They shouldn’t be deleted either.

To programmatically view the created policies, query the dbo.syspolicy_policies view

in the MSDB database:

select * from msdb.dbo.syspolicy_policies

You can export policies to XML and import them using the Policy context menu.

Evaluating Policies

Of course, policies would be purely academic if they never actually executed and evalu-

ated any objects. Policies may be set for On Schedule, On Demand, On Change, or Log Only.

Policies must be enabled using their context menu. On demand policies may not be enabled.

For policies set to On Demand, the policies may be evaluated for any object using the

object’s context menu ➪ Policies ➪ Evaluate. This opens the Evaluate Polices dialog for the

object, as shown in Figure 20-7.

The outcome of the policy evaluations is displayed in the Evaluation Results page, as seen

in Figure 20-8.

c20.indd 543c20.indd 543 7/30/2012 5:45:18 PM7/30/2012 5:45:18 PM

http://www.it-ebooks.info