Creating Policies
If the facet property is the fact and
the condition the brain, the policy is the muscle. Policies define how
and where the condition is applied and enforced.
You can create a new policy using the New Policy
menu item in Object Explorer's Management → Policy Management →
Policies and then selecting the New Policy option from the context
menu. Alternatively, you can go to the File menu, browse to the New
submenu, and then select Policy from the list. The Create New Policy
dialog, as shown in Figure 6, has a general page and a description page.
You can open an existing policy by double-clicking the policy or by using the Property command in its context menu.
The general page has four key selections. The
first is the condition; a policy may check only a single condition. The
ellipsis opens the condition.
The second selection, targets, defines which
objects will be tested. The options vary depending on the type of
object the facets apply to.
Evaluation mode is the third key selection. There are four Evaluation modes for a policy:
- On Demand: The policy is evaluated only manually. There's no schedule, automatic testing, or enforcement.
- On Schedule: The policy is evaluated and any violations are logged but not corrected.
- On Change — Log Only: The policy is evaluated when the facet is changed, and violations are logged.
- On Change — Prevent: The policy is evaluated when the facet is changed, and violations are rolled back. This action rolls back a violation only after the action has occurred; meaning a large transaction that violates a policy may take a long time to roll back changes.
Every facet may be set to On Demand or On
Schedule, but On Change is limited. PBM relies on DDL eventing to do
CheckOnChange, and not all objects support DDL eventing. The following
query reports which Execution modes are available for which facets:
SELECT name as Facet,
Max(CASE WHEN execution_mode & 0 = 0 Then 1 else 0 End)
as ‘On Demand',
Max(CASE WHEN execution_mode & 4 = 4 Then 1 else 0 End)
as ‘On Schedule',
Max(CASE WHEN execution_mode & 2 = 2 Then 1 else 0 End)
as ‘On Change Log Only',
Max(CASE WHEN execution_mode & 1 = 1 Then 1 else 0 End)
as ‘On Change Prevent'
FROM msdb.dbo.syspolicy_management_facets
GROUP BY name
ORDER BY name
Result is shown in Table 1:
Table 1 Facet Listing
The fourth key selection on the general page is
Server Restriction. You can use this option to define the target
servers based on criteria.
Caution
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.
