Policy Based Management – SQL Server 2008

Leave a comment (4) Go to comments

Policy–based management is an approach to database administration based on policies and not tasks. With policy-based management, you can determine the policies, restrictions, and behaviors the SQL Servers in your organization will enforce.

Microsoft SQL Server provides a policy-based system for managing one or more instances of SQL Server along with tools for performance monitoring, troubleshooting, and tuning that enable administrators to more efficiently manage their databases and SQL Server instances.

Policy-Based Management consists of three components: policy management, policy administrators who create policies, and explicit administration.

The SQL Server 2008 Policy Management feature allows you to create and execute configuration policies against one or more database servers. With these policies, you can ensure that standard configuration settings are applied and maintained on each of the targeted servers and databases.

To use Policy-Based Management, SQL Server policy administrators use SQL Server Management Studio to create policies that manage entities on the server, such as the instance of SQL Server, databases, and other SQL Server Objects. Policy-Based Management consists of three components: policy management, policy administrators who create policies, and explicit administration. Administrators select one or more managed targets and explicitly check that the targets comply with a specific policy, or explicitly force the targets to comply with a policy.


Policy-based Management allows you to define what can or cannot be done on the server itself which can then be redistributed across your network of servers for more consistent management.

Policy-based Management is an approach to database administration based on policies and not tasks. With Policy-based Management, you can determine the policies, restrictions, and behaviors the SQL Servers in your organization will enforce. Define the policy using Management Studio and select one or more servers to enforce the policy. You then monitor from a central console, the SSMS.

The classic example is where users are not allowed to create tables in the DBO schema. Using Policy-based Management you can make sure all user created stored procedures begin with a USP_ prefix. Using Policy-based Management you can disallow the use of SQLMail. Policies can have a database or a server scope.

Policy-Based Management has three components:

  • Policy management – Policy administrators create policies.

  • 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.

  • Evaluation modes – How and when the policies are evaluated. There are four evaluation modes, three of which can be automated

Evaluation modes

  • On demand. This mode evaluates the policy when directly specified by the user.

  • On change: prevent. This automated mode uses DDL triggers to prevent policy violations.

  • On change: log only. This automated mode uses event notification to evaluate a policy when a relevant change is made.

  • On schedule. This automated mode uses a SQL Server Agent job to periodically evaluate a policy.

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. A target set is the set of targets that results from applying a set of target filters to the target hierarchy, for example, all the tables in the database owned by the HumanResources schema.

Policy-Based Management facet

A set of logical properties that model 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 by only the maker of the facet. A target type can implement one or more management facets, and a management facet can be implemented by one or more target types. Some properties of a facet can only apply to a specific version. For example, the Database Mail property of the Surface Area Configuration facet only applies to SQL ServerĀ® 2005 and later versions.

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.

Policy-Based Management policy

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.

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. At the database level, the following conditions apply:

  • Database owners can subscribe a database to a set of policy categories.

  • Only policies from its subscribed categories can govern a database.

  • All databases implicitly subscribe to the default policy category.

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:

  • The policy is enabled.

  • The target belongs to the target set of the policy.

  • The target or one of the targets ancestors subscribes to the policy group that contains this policy.

The execution modes that apply to a policy are determined by the characteristics of the Policy-Based Management facet that is used by the policy.

Policies are created and managed by using Management Studio.

The process includes the following steps:

  1. Select a Policy-Based Management facet that contains the properties to be configured.

  2. Define a condition that specifies the state of a management facet.

  3. Define a policy that contains the condition, additional conditions that filter the target sets, and the evaluation mode.

  4. Check whether an instance of SQL Server is in compliance with the policy.

For failed policies, Object Explorer indicates a critical health warning as a red icon next to the target and the nodes that are higher in the Object Explorer tree.

When Policy-Based Management policies are executed in one of the three automated evaluation modes, if a policy violation occurs, a message is written to the event log. To be notified when this message is written to the event log, you can create an alert to detect the message and perform an action.

The alert should detect the messages as shown in the following table.

Execution mode Message number
On change: prevent (if automatic) 34050
On change: prevent (if On demand) 34051
On schedule 34052
On change 34053
Demo Steps

Define the New Custom Policy

  • Click Start, and then click SQL Server Management Studio.
  • The Connect to Server dialog box appears. Click Connect.
  • In the Object Explorer pane, navigate to <Local Server>| Management | Policy Management | Facets | Table.
  • Right-click Table and then click New Condition.
  • The Create New Condition window opens. In the Name field, type Table Condition.
  • In the Expression box, in the Field list, click @HasIndex.
  • Make sure that the Operator list is set to =.
  • In the Value list, click True.
  • Click OK.
  • In the Object Explorer pane, right-click Policies, and then click New Policy.
  • The Create New Policy window opens. In the Name field, type Table Policy.
  • Select the Enabled check box.
  • In the Check condition list, click Table Condition.
  • In the Against targets box, in the Every Database list, click New condition.
  • The Create New Condition window opens. In the Name field, type AdventureWorks2008.
  • In the Expression box, in the Field list, click @Name.
  • Make sure that the Operator list is set to =.
  • In the Value field, type ‘AdventureWorks2008′.
  • Click OK.
  • In the Execution Mode list, click On Change – Prevent.
  • In the Server restrictions list, make sure the option is set to None.
  • Click OK.
  • That it, policy has been created.
EOF - Policy Based Management – SQL Server 2008, SQL Server 2012 , 2008 R2 , 2008 , 2005 , 2000
Leave a comment

4 Comments.

  1. Conceptually and delivery is good.

  2. Can we have the Images too for Demo?

Leave a Reply

Your email address will not be published. Required fields are marked *


*


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Notify me of followup comments via e-mail. You can also subscribe without commenting.

Trackbacks and Pingbacks: