SQL Server 2008 introduced a powerful feature called Policy-Based Management, which allows database administrators to define and enforce policies for managing SQL Server instances and databases. This feature provides several advantages, including reliable system configuration, simplified administration tasks, and improved compliance management.

Basics of Policy Management

Policy-Based Management in SQL Server consists of three main components: policy administrators, policy management, and explicit administration. Policy administrators are responsible for creating policies, which are sets of rules that define the configuration and management of SQL Server objects and properties. These policies can be applied to various entities, known as targets, such as tables, databases, and indexes.

Policy-Based Management uses a set of key terms to define its functionality. A target is a type of entity that can be managed by Policy-Based Management, while a facet represents a property that can be managed. Conditions are criteria that specify the state of a facet, and policies are sets of rules that apply to server objects or database properties.

Practical Example of Policy Management

Let’s explore a practical example of using Policy-Based Management in SQL Server. We’ll focus on the facet of statistics and create a policy to manage the IsAutoCreated property of the database.

First, we need to navigate to the Policy-Based Management node in SQL Server Management Studio and expand the facets node. Here, we can see a list of available facets, each representing a different database object. By double-clicking on a facet, we can view its properties.

Next, we’ll create a condition to ensure that the IsAutoCreated property is set to true everywhere. This condition will be used in our policy to enforce this requirement. Once the condition is created, we can proceed to create the policy.

When creating the policy, we need to specify the target databases where the policy will be applied. We can choose to evaluate the policy manually or schedule it as a recurring task. Once the policy is created, we can evaluate it using SQL Server Management Studio to check its compliance status.

If the policy is not compliant, we can fix the non-complying property by changing it to comply with the policy. This can be done manually or using SQL Server Agent with PowerShell. Once the property is fixed, the policy will evaluate as compliant.

Summary

Policy-Based Management in SQL Server provides database administrators with greater control over system configuration and management. By defining and enforcing policies, administrators can ensure reliable and compliant database operations. While enforcing these policies may require some effort, Policy-Based Management simplifies the process and helps maintain database standards.