The Database Engine supports Policy-Based Management, a framework for managing one or more server instances, databases, or other database objects. Before you learn how this framework works, though, you need to understand some key terms and concepts of it.
1. Key Terms and Concepts
The following is a list of the key terms regarding Policy-Based Management, which is followed by a description of the concepts related to these terms:
- Managed target
- Target set
- Facet
- Condition
- Policy
- Category
The system manages entities called managed targets, which may be server instances, databases, tables, or indices. All managed targets that belong to an instance form a hierarchy. A target set is the set of managed targets that results from applying filters to the target hierarchy. For instance, if your managed target is a table, a target set could comprise all indices that belong to that table.
A facet is a set of logical properties that models the behavior or characteristics for certain types of managed targets. The number and characteristics of the properties are built into the facet and can be added or removed only by the maker of the facet. Some facets can be applied only to certain types of managed targets.
A condition is a Boolean expression that specifies a set of allowed states of a managed target with regard to a facet. Again, some conditions can be applied only to certain types of managed targets.
A policy is a condition and its corresponding behavior. A policy can contain only one condition. Policies can be enabled or disabled. They are managed by users through the use of categories.
A policy belongs to one and only one category. A category is a group of policies that is introduced to give a user more flexibility in cases where third-party software is hosted.
Database owners can subscribe a database to a set of categories. Only policies from the database’s subscribed categories can govern that database. All databases implicitly subscribe to the default policy category.
2. Using Policy-Based Management
This section presents an example that shows how you can use Policy-Based Management. This example will create a policy whose condition is that the index fill factor will be 60 percent for all databases of the instance. (For a description of the FILLFACTOR option, see Chapter 10.)
The following are the three main steps to implement Policy-Based Management:
- Create a condition based on a facet.
- Create a policy.
- Categorize the policy.
Generally, to create a policy, open SQL Server Management Studio, expand the server, and then expand Management | Policy Management.
The first step is to create a condition. Right-click Conditions and choose New Condition. In the Create New Condition dialog box (see Figure 15-5), type the condition name in the Name field (SetFillFactor in this example), and choose Server Configuration in the Facet drop-down list. (Setting a fill factor for all databases of an instance is server-bound and thus belongs to the server configuration.) In the Field column of the Expression area, choose @FillFactor from the drop-down menu and choose = as the operator. Finally, enter 60 in the Value field. Click OK.
The next step is to create a policy based on the condition. In the Policy Management folder, right-click Policies and choose New Policy. In the Name field of the Create New Policy dialog box, type the name for the new policy (PolicyFillFactor60 in this example). In the Check Condition drop-down list, choose the condition that you have created (SetFillFactor). (This condition can be found under the node called Server Configurations.) Choose On Demand from the Evaluation Mode drop-down list.
NOTE Policy administrators can run policies on demand, or enable automated policy execution by using one of the existing execution modes.
After you create a policy, you should categorize it. To categorize a policy, click the Description page in the Create New Policy dialog box. You can place policies in the Default category or in a more specific category. (You can also create your own category by clicking the New button.)
The process described in this section can be applied in the same way to dozens of different policies in relation to servers, databases, and database objects.
Source: Petkovic Dusan (2020), Microsoft SQL Server 2019: A Beginner’s Guide, Seventh Edition-McGraw-Hill Education.