Implement policy by using automated evaluation modes
In this tutorial we will learn how to implement policy by using automated evaluation modes.
However, Policy-Based Management is a policy based system for managing one or more instances of SQL Server. Use it to create conditions that contain condition expressions. Then, create policies that apply the conditions to database target objects.
Use SQL Server management Studio to create and manage policies, to:
- Firstly, select a Policy-Based Management facet that contains the properties to be configured.
- Secondly, define a condition that specifies the state of a management facet.
- Thirdly, define a policy that contains the condition, additional conditions that filter the target sets, and the evaluation mode.
- Lastly, check whether an instance of SQL Server is in compliance with the policy.
Three Policy-Based Management components
Policy-Based Management has three components:
- Firstly, Policy management. Policy administrators create policies.
- Secondly, Explicit administration. Administrators select one or more managed targets and explicitly check that the targets comply with a specific policy, or explicitly make the targets comply with a policy.
- Thirdly, Evaluation modes. There are four evaluation modes.
- Then, On demand. This mode evaluates the policy when directly specified by the user.
- After that, On change: prevent. This automated mode uses DDL triggers to prevent policy violations.
- Next, On change: log only. This automated mode uses event notification to evaluate a policy when a relevant change is made.
- Lastly, On schedule. This automated mode uses a SQL Server Agent job to periodically evaluate a policy.
Terms
- Firstly, Policy-Based Management managed target Entities that are managed by Policy-Based Management, such as an instance of the SQL Server Database Engine, a database, a table, or an index. All targets in a server instance form a target hierarchy. However, a target set is the set of targets that results from applying a set of target filters to the target hierarchy.
- Secondly, Policy-Based Management facet A set of logical properties that model the behavior or characteristics for certain types of managed targets. However, the number and characteristics of the properties are built into the facet and can be added or removed by only the maker of the facet. Further, a target type can implement one or more management facets, and a management facet can be implemented by one or more target types.
Policy-Based Management condition
A Boolean expression that specifies a set of allowed states of a Policy-Based Management managed target with regard to a management facet. However, SQL Server tries to observe collations when evaluating a condition. When SQL Server collations do not exactly match Windows collations, test your condition to determine how the algorithm resolves conflicts.
Policy-Based Management policy
A Policy-Based Management condition and the expected behavior, for example, evaluation mode, target filters, and schedule. However, a policy can contain only one condition. Policies can be enabled or disabled. Policies are stored in the msdb database.
Policy-Based Management policy category
A user-defined category to help manage policies. Users can classify policies into different policy categories. A policy belongs to one and only one policy category. Policy categories apply to databases and servers. However, at the database level, the following conditions apply:
- Firstly, database owners can subscribe a database to a set of policy categories.
- Secondly, only policies from its subscribed categories can govern a database.
- Thirdly, all databases implicitly subscribe to the default policy category.
- Lastly, at the server level, policy categories can be applied to all databases.
Effective policy
The effective policies of a target are those policies that govern this target. A policy is effective with regard to a target only if all the following conditions are satisfied:
- Firstly, the policy is enabled.
- Secondly, the target belongs to the target set of the policy.
- Thirdly, the target or one of the targets ancestors subscribes to the policy group that contains this policy.
Reference: Microsoft Documentation