Microsoft Training Blog

Policy Based Management in SQL Server 2008

SQL Server 2008 has the ability to manage some aspects of your SQL Server via policies.  There are several benefits to doing your management through Policy Based Management (PBM).  First is the lessening of DBA burden to constantly check for standards adherence.  Far too much of a DBA’s time is spent ensuring that objects are created based on the organizations agreed upon standards.  Second, policies ensure consistency.  Whether the issue is a naming convention, indexing, data file placement or options choices, it is important for the sake of efficiency that everyone can know what to expect.  Third, policies give pervasive insight into server and database level objects and settings for the purposes of planning, troubleshooting and documentation.

Policy Based Management consists of four subjects.

1. Facets.  These are the objects themselves (Tables, Databases, and Logins etc.) and what can be managed about the object.  What can be managed will vary based on the type of object.  e.g. Table Facets expose the ability to manage indexes associated with tables, columns in the table, etc.
2. Conditions.  Conditions define a value against which the facet is checked, in other words the standard you desire.  e.g. Tables must have indexes or Stored Procedure names must begin with usp_.
3. Policies.  One or more Conditions make up a Policy.  Policies define the Conditions to check and the group of objects against which to check them.  e.g.   All Tables in the Customers database, or specified Logins on the Production instance.
4. Evaluation Modes.  There are four Evaluation Modes.  These determine how to evaluate your Conditions against you extant environment.  The four modes are:  

• On Demand.  This allows you to create and view a report showing the condition of your measured objects in light of the Conditions you have defined.  e.g. Which Tables have indexes and which do not.  On Demand only evaluates the objects and display the results, it does not change anything.
• On Schedule.  Same as On Demand but rather than running manually as the result of  SQL
• On Change: Log.  This mode evaluates the creation of new and/or the modification of extant objects.  When a creation or modification event occurs that falls under the auspice of the Policy, the Policy is evaluated and the results are sent to a report.
• On Change: Prevent.  Like On Change: Log, the Policy is evaluated at the creation or modification of an object correlating to a Facet in the Policy.  If the creation or modification will violate the Condition, the change is rejected by SQL Server and the transaction is rolled back.

Of course, this article does not explain how to implement Policy Based Management, but it introduces some of the capabilities of the technology. PBM should create savings due to decreased management costs. With extra money to spend from those savings and the increased free time your DBA will have, you can send them to a SQL Server 2008 class at QuickStart to learn how to implement PBM as well as many other improving technologies.

Interested in learning about SQL Server 2008 training and other QuickStart course? Please contact us today. 

Please also share your SQL Server 2008 questions and comments below.

Thank you!

Jeff Rathjen, Systems Engineer, QuickStart Intelligence