Article Topics

All Articles

Performance Tuning

Audit

Business Intelligence

Clustering

Reporting Services

Developer

General DBA

ASP.NET / ADO.NET

Write for Us

Share you SQL Server knowledge with others and raise your profile in the community More...

Configure and Manage Policy Based Management in SQL Server 2008

By : Ashish Kumar Mehta
Jul 16, 2008

SQL Server 2008 introduces a new feature called Policy Based Management (aka Declarative Management Framework (DMF)). Database Administrators have always had a tough time to make sure that all the SQL Servers they administer are configured according to the policies and standards of organization. Using the Policy Based Management feature DBA’s can now manage one or more instances of SQL Server 2008 an check for policy compliance or non compliance issues. The policies can be created to manage all the entities of SQL Server 2008, such as SQL Server Configurations, Databases, Tables, Users, Roles, or any other SQL Server objects etc.

How to Configure and Manage Policy Based Management

1. Connect to SQL Server 2008 Instance using SQL Server Management Studio
2. In the Object Explorer, Click on Management ? Policy Management and you will be able to see Policies, Conditions & Facets as child nodes

3. Click on Facets Node and expand it to see 74 predefined set of SQL Server Facets which are useful when you want to create policies for SQL Server 2008

4. Double Click on Database Maintenance Facet to see all the properties that can be managed when we create a policy using this facet

How to Create a Condition
In Policy Based Management the initial step is to create a condition. New conditions can be created by right clicking the Conditions child node under Management | Policy Management.

Once the Create New Condition popup window opens, you need to provide the Name, Facet and the Expression values as shown in the image below. In this condition we are setting an expression that all the databases on the current SQL Server 2008 Instance should have the Recovery Model configured as FULL

Next Page>>