Preventing Bad Behavior with Policy Based Management

SQL Server 2008 supports Policy Based Management. When it was first announced it was called the Declarative Management Framework but PBM has stuck. It’s SQL Server’s version of Group Policy but luckily Active Directory is not involved at all. In fact, that was a requirement. I think Microsoft learned their lesson with Exchange 2000. It’s a SQL Server feature pure and simple. You can control settings made across many servers including policies based on security, performance or just enforcing naming conventions and the like.

Although Policy Based Management is a SQL Server 2008 feature, policies can be applied down-level to SQL Server 2000 and 2005 instances also. However, the policies must be stored on a SQL Server 2008 instance within the MSDB database. That server can be defined as a Central Management Server (CMS) to host the policies and to propagate them across to many target servers.

The three main objects for defining Policy Based Management are Facets, Conditions and Policies. Facets are defined by Microsoft and define the properties that we can control. For instance, in the Database facet one of the properties is AUTOSHRINK. This means we can control the value of the property across many databases across many servers. There is also a Stored Procedure facet. A property we may want to control is Name to enforce a naming convention across many databases. Another facet is the Surface Area Configuration facet. If you ever wondered where the Surface Area Configuration tool disappeared to, well, it became one of many facets in PBM.

In order to set up a Policy, we need to first create one or more Conditions. An example may be based on the Stored Procedure facet using the Name property to specify the Condition as Name LIKE ‘usp%’ with % as a wildcard. This means user Stored Procedures must begin with usp. Conditions are always positive, in that they define the “good” behavior. Another example is: AUTOSHRINK=OFF, for sure.

The next step is to create a Policy that uses the Condition(s). We need to define one of four Evaluation Modes for the Policy: On Demand, On Schedule, On change: log only, On change: prevent. On Demand will let you evaluate the Policy manually when you require in order to find out who is in compliance and who is not. On Schedule will evaluate regularly on a predetermined timeframe. On Change: log only uses Event Notifications to log non-compliance. On change: prevent will actually stop bad behavior. This is done using a generated DDL Trigger with a Rollback. Since DDL Triggers and Event Notifications are only available from 2005 onwards, these modes are not available for SQL Server 2000 Targets.

The Policy can then be enabled and applied to one or more Targets, for instance, for every user Database on every Server in a predefined Server Group. Server Groups are defined using SSMS Registered Servers window as is the CMS. You decide which servers go into which Server Groups. An example may be by SQL Server version or by location depending on the policy requirements.

Hopefully, there is enough flexibility to define meaningful Policies that will help you to control consistency across your many SQL Server instances.

Author: Brian Egler

Related Courses

Developing and Implementing a SQL Server 2008 Database (M6232)

Implementing and Maintaining SQL Server 2008 Analysis Services and Business Intelligence Solutions (M6234)

Maintaining, Troubleshooting, and Developing Solutions with Microsoft SQL Server 2008 Reporting Services (M6236)

SQL Server 2005 Administration (M2780)

SQL Server 2008 for Administration (M6231, M6232)

Writing Queries Using Microsoft SQL Server 2008 Transact-SQL (M2778)

In this article

Join the Conversation