Welcome to the world (new face) of Knowledge Sharing Network.
To track the older blog posts refer to our historical knowledge sharing site  and you will find this as your destination for SQL Server knowledge pool.

Follow SQLMaster on Twitter SqlServer-QA.net - Knowledge Sharing Network (@sqlmaster)

SQL Server 2008 Policy Based Management - managing and troubleshooting policy failures - SQL Server Knowledge Sharing Network (@sqlmaster)

SQL Server 2008 Policy Based Management - managing and troubleshooting policy failures

Policy Based Management (PBM) is an effective system for DBAs to manage one or more instances of SQL Server. Just to give a brief overview of what it consists, it consists 3 components:

  • Policy management for Administrators to create policies
  • Explicit administration for Administrators to select multiple targets to check whether they comply with a specific/multiple policy(ies)
  • Evaluation modes based upon the schedule and execution of  defined policies that is automated

Policy-Based Management relies on DDL triggers to detect and roll back DDL operations that do not comply with policies that use this evaluation mode. Removing the Policy-Based Management DDL triggers or disabling nest triggers, will cause this evaluation mode to fail or perform unexpectedly. A policy depends upon target, facet with the condition that was setup by the Administrators. How do you know whether a policy is effective or not? Its simple, such  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] and [The target or one of the targets ancestors subscribes to the policy group that contains this policy].

In this case I would like to refer the important objects that exists within the MSDB database for displaying information about Policy-Based Management policies, conditions, expressions, groups, and filters, obviously these views are owned by the dbo schema (by default). These are:

syspolicy_conditions (Transact-SQL)

syspolicy_policies (Transact-SQL)

syspolicy_system_health_state (Transact-SQL)

The above 3 views that I use regularly to see required information about the policies that are defined within the Central Management server. As you can see the above are referenced from BOL for your information in order to see what they do and what for they are used. Now coming the important part of troubleshooting the policies that are used to make the SQL platform effective, such Policy-Based Management records errors to both the Windows event log and, for scheduled policies, the SQL Server Agent log. If policies are not enabled or do not effect the expected target, the failure is not considered an error and is not logged. 

So how do you troubleshoot to see what happened for the failure of any policy application, in any such troubleshooting exercise it is better to look for root cause on step by step. As you may aware that a policy will be using the mode of On change: log or On change_prevent and to get this affective you must ensure that such policy is 'not' disabled or the target does not subscribe to the policy group that contains the policy. Only in the case of  On change: prevent evaluation mode, the Service Broker eventing mechanism is not monitoring the correct events and it might fail due to the failure in the evaluation engine. Once you find the basic steps you can obtain the following troubleshooing process (as clearly referred in BOL):

  1. To see whether the policy executed with exception messages, check the policy execution history in the msdb.dbo.syspolicy_policy_execution_history view.
  2. To see whether the policy executed for the specific target, check the policy execution history for the specific target in question in the msdb.dbo.syspolicy_policy_execution_history_details view.
  3. To verify that the policy applies to the target, use the View Policies page in SQL Server Management Studio.
  4. Check the Windows event log for a Policy-Based Management error.

So in any case you must ensure that SQLAgent service must be up and running the central monitoring server to ensure all the Policies can be effective, in order to ensure that this is not happening again and again make sure the Service account that is used to start this service has got correct password set and set to NOT EXPIRED on the account status within the Active Directory. By now you would agree that monitoring and troubleshooting of such management aspect is simple/easy when you adopt the process correctly.


Published Friday, January 9, 2009 7:35 AM by ssqa.net


No Comments