Policy-Based Management Overview

  • Article

This posting provides an overview of Policy-Based Management (PBM). You can read more about PBM in SQL Server 2008 Books On-line. Many of the ad hoc problems DBAs experience are caused by unwanted configuration changes or configuration changes that don't comply with the "standard". Some shops have implemented custom consistency checks. On a routine schedule these consistency checks run against the production environment comparing specific configuration properties against the desired state. This information, however, is locked away in custom scripts which makes it more difficult to maintain and reuse.

PBM allows the DBA to declare the desired state of the system and check the system for compliance with that state. Think how group policy controls Windows system settings like password strength and expiration. PBM provides similar capabilities to the DBA.

Let's walk through an example: suppose the DBA needs to enforce a naming standard on stored procedures. In simple English the DBA states their intent as follows: All stored procedures in the database must start with "usp_" - ("usp" stands for user stored procedure). The picture below maps this policy to the main PBM concepts.

PBM_HighLevel_Concept_Map

Starting from the bottom and working our way up the stack:

  • Target Type: These are entities to be managed by PBM. The list of entities should be familiar to a DBA. They include: database, table, view, login, user, stored procedure, etc. In our example the target type we're interested in is Stored Procedure.
  • Management Facet: A set of logical properties that model the behavior or characteristics for certain types of target types. Our policy will be written over the Multi-Part Name facet which contains two properties: Name and Schema. This facet models all schema-bound target types (tables, views, etc). In our example we only care about stored procedures.
  • Condition: A Boolean expression that specifies a set of allowed states of a target type with regard to a management facet. Said another way, the condition is stated as the desired state of the target type. We want the name of our stored procedures to start with usp_ - since underscore is a wildcard we need to escape it with brackets. The percent at the end wildcards the rest of the name which we don't care about.
  • Policy: A policy includes the condition and the expected behavior, for example, evaluation mode. We already built our condition, our expected behavior is only stored procedures that meet our condition can be created in the database. This translates to an evaluation mode of "Check on Change - Prevent"; meaning whenever a stored procedure is created or altered the transaction will be checked for compliance with the policy. If the transaction does not comply with the policy it will be blocked from completing. There are other supported evaluation modes which we'll explore in a future posting.

In this example we've introduced the key concepts in PBM: Target Types, Facets, Conditions, and Policies. In a future posting we'll look at the actual GUI in Management Studio for creating policies. We'll also explore advanced topics such as target filters and categories.

_____________

About the Author:
Dan Jones is a PM on the SQL Server Manageability team at Microsoft.