Introduction to Policy-Based Management in SQL Server 2008

Policy-Based Management in SQL Server 2008 allows the database administrator to define policies that tie to database instances and objects. These policies allow the Database Administrator (DBA) to specify rules for which objects and their properties are created, or modified. An example of this would be to create a database-level policy that disallows the AutoShrink property to be enabled for a database. Another example would be a policy that ensures the name of all table triggers created on a database table begins with tr_.



As with any new SQL Server technology (or Microsoft technology in general), there is a new object naming nomenclature associated with Policy-Based Management. Below is a listing of some of the new base objects.

PolicyA Policy is a set of conditions specified on the facets of a target. In other words, a Policy is basically a set of rules specified for properties of database or server objects.

TargetA Target is an object that is managed by Policy-Based Management. Includes objects such as the database instance, a database, table, stored procedure, trigger, or index.

FacetA Facet is a property of an object (target) that can be involved in Policy Based Management. An example of a Facet is the name of a Trigger or the AutoShrink property of a database.

ConditionA Condition is the criteria that can be specify for a Target’s Facets. For example, you can set a condition for a Fact that specifies that all stored procedure names in the Schema ‘Banking’ begin with the name ‘bnk_’.



You can also assign a policy to a category. This allows you manage a set of policies assigned to the same category. A policy belongs to only one category.

Policy Evaluation Modes
A Policy can be evaluated in a number of different ways:



On demand - The policy is evaluated only when directly ran by the administrator.
On change: prevent - DDL triggers are used to prevent policy violations.
On change: log only - Event notifications are used to check a policy when a change is made.
On schedule - A SQL Agent job is used to periodically check policies for violations.
Advantages of Policy Based ManagementPolicy-Based Management gives you much more control over your database procedures as a DBA. You as a DBA have the ability to enforce your paper policies at the database level. Paper polices are great for defining database standards are guidelines. However, it takes time and effort to enforce these. To strictly enforce them, you have to go over your database with a fine-toothed comb. With Policy-Based Management, you can define your policies and rest assured that they will be enforced.

Next TimeToday I took a look at the basic ideas behind Policy-Based Management in SQL Server 2008. In my next article I’ll take a look at how you can make these ideas a reality by showing you how you can create your own polices to use to administer your SQL Server.