In Novemeber, I had the opportunity to present at DevConnections. I spoke on two topics, Policy Management and Plan Guides. Both of these are new / unique to SQL Server 2008 and I really love both of the additions to the SQL Server platform.
In this blog post I have attached two files; one of them is my powerpoint slide deck and the other is the script I used for the demo’s.
This was a very difficult talk to give as the concept is/was relatively new and the amount of information available was scarce when I was developing the content.
So, what is Policy Management? Microsoft’s definition is as follows : “Policy-Based Management is a policy-based system for managing one or more instances of SQL Server 2008. Use this with 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.” What this definition leaves out is something of great importance. This new feature CAN be used with down versions, providing that the facet you want to throw a policy around is availalable in that version. Meaning, if you want to monitor datafiles or schema changes in a SQL Server 2005 instance, you can!
I found there to be three components to Policy Management.
- Policy Management — Creation of conditions and policies
- Explicit Administration — One to many managed targets
- Automated Administration — Automate enforcement of policies
For each of these there are components – Refer to the slide deck and Microsoft’s site for more information.
One of the biggest helps that I had when leaning Policy Management was the following CTE:
The results of this query provide all of the available facet’s and which mode then can be run for. This is a huge help as going through the gui to see if something is availalble for a certain type of mode can take a while.
USE MSDB;
with automatedpolicyexecutionmode (modeid, modename)
as
(select * from (values (0, ‘On Demand’),
(1,’Enforce Compliance’),
(2, ‘Check on Change and Log’),
(4, ‘Check on Schedule and Log’))
as em(modeid, modename))
So far I have successfully implemented policies against a host of the facets offered. Microsoft is going to continue to build on this inital release as time goes on; so look to new additions of facets as time goes on.
Enjoy Policy Based management and may it save you a great deal of time, decrease your daily / weekly checklist of items and enable you to provide further value to your organization.
SELECT dmf.management_facet_id as facetid, dmf.name as facetname, apemode.modename
FROM syspolicy_management_facets as dmf
INNER JOIN automatedpolicyexecutionmode as apemode on dmf.execution_mode & apemode.modeid=apemode.modeid
ORDER BY dmf.name, apemode.modename
If you walk through the slide deck and the demo script; you can quickly see how easy it is to create a policy. Once you have one created (IN DEVELOPMENT), =), then evaluate the policy in two ways. The first way you will want to ensure that the evaluation is successful. The second time, break it, so that the evaluation of the policy is false. Once you have done this, run the query below.
USE MSDB;
GO
SELECT a.execution_date AS ‘Date Run’ ,
c.name AS ‘Policy’ ,
a.target_query_expression AS ‘Policy Failure Targets’ ,
d.name as ‘Condition’,
d.description as ‘Condition Description’,
d.facet as ‘Condition Facet’
FROM syspolicy_policy_execution_history_details_internal a
INNER JOIN syspolicy_policy_execution_history_internal b ON a.history_id = b.history_id
INNER JOIN syspolicy_policies_internal c ON b.policy_id = c.policy_id
INNER JOIN syspolicy_conditions d ON c.condition_id = d.condition_id
WHERE a.result = 0
ORDER BY a.execution_date DESC, c.name ASC, d.name ASC
Attachements for this post ::
Script: policymanagementscript2
Slide Deck: sql-connections-conference-slides-policy-management2
Posted by datarealized 
Posted by datarealized 
