Policy based management is one of the new feature added from SQL Server 2008. With the help of this feature you can evaluate an instance or database or objects to bound with in certain conditions policies. If any of the objects or instances failed to bound with in the policy then it can either notified or you can directly change the values to bound within policy with the help of policy management there by making the administration simpler. So by using this feature you dont need to outline all these conditions and no need to have an auditing on the same.
PBM supported in the following editions of SQL Server 2008
- SQL Server 2008 Enterprise
- SQL Server 2008 Standard
- SQL Server 2008 Workgroup
- SQL Server 2008 Web
- SQL Server 2008 Express
- SQL Server 2008 Express with Tools
- SQL Server 2008 Express with Advanced services
Objects of Policy Based Management:
Policy based management is compromised of five objects, lets discuss on it.
Facets can be considered as the base for policy based management. Its a set of logical properties that model the behavior or characteristics for certain types of managed targets that you need to check against your database or server or configuration. There are 74 facets available in SQL 2008 and you can query the catalog view “dbo.syspolicy_management_facets” under msdb database to know more about each one of them. User defined facets cant be created in policy based management.
Conditions are Boolean expressions which specify whether the policy need to check a property or not with respect to the facets configured for a target. For e.g.) = or <> is a condition which specifies the policy to check for a property or not, based on this it will provide the result.
As the name itself implies that these are the targets on which we are going to check enforce the policies. These are the entities that are managed by policy based management such as an instance, database, objects etc. Its not necessary to select all the databases or instances as targets. Policy based management allows you to select a specified set of objects to comply within the policy.
Policies are created for a single condition and set to either enforce or check compliance. A Policy-Based Management condition and the expected behavior, for example, evaluation mode, target filters, and schedule. A policy can contain only one condition. Policies can be enabled or disabled. There are four ways to evaluate these policies out of which three can be automated.
- On-Demand : This mode evaluates the policy upon user execution. I.e when the policy needs to be checked, a user can invoke the policy to evaluate its target.
- On-Change : Prevent : This automated mode uses DDL triggers to prevent policy violations. E.g) Consider you have created a policy to check db recovery model to full, if anyone changes the recovery model then this mode will prevent it. NOTE: If the nested triggers server configuration option is disabled, On change: prevent will not work correctly.
- On change: log only : This automated mode uses event notification to evaluate a policy when a relevant change is made. This mode is similar to Prevent mode however this will only log the changes and dont prevent it from changing the value.
- On schedule : This automated mode uses a SQL Server Agent job to periodically evaluate a policy.
- Policy categories
Policy categories are user defined one and its used to organize the policies for easy policy administration. A policy belongs to one and only one policy category
Permissions Required to access PBM
Users who need to access policy based management should have PolicyAdministratorRole in MSDB database. Users in this role have complete privilege over all the policies exists. Users have privilege to create or edit policies and conditions, in addition to this they will be allowed to enable or disable the policies.
Please note that assigning users to this role will allow them to have elevated privilege that is they can create server triggers and schedule policy executions that can affect the operation of the instance of the Database Engine. Users in the PolicyAdministratorRole role can create policies that contain a condition that uses the ExecuteSql or ExecuteWql functions.
The following security principles apply
- A system administrator or database owner can subscribe a database to a policy or policy group.
- Members of the PolicyAdministratorRole role can enable or disable policies.
- Members of the PolicyAdministratorRole can create policies that they do not have permission to execute ad hoc, but which can be successful when the policies are run by other users that have sufficient permission.
- Ad hoc policy execution occurs in the security context of the user.
- Policies that have the On schedule evaluation mode, use SQL Server Agent jobs that are owned by the sa login.
Considerations for using PBM
Policy based management will some how affect some of the features process not to work properly. For example change data capture and transactional replication both use the systranschemas table, which does not have an index. If you enable a policy that all tables must have an index, enforcing compliance of the policy will cause these features to fail.
Policy based management is one of the new feature in SQL Server 2008. This feature allows the users to easily audit the entire Server, database configurations and makes the administration much simpler. You need to look into the considerations before creating a policy.