Policy Management

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.  

  1. Policy Management  — Creation of conditions and policies
  2. Explicit Administration — One to many managed targets
  3. 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  — The majority of the t-sql in this document can be found below.  

Slide Deck: sql-connections-conference-slides-policy-management2  


USE msdb;
GO
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))
     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
     OREDER BY dmf.name, apemode.modename

— Demo 1
— Greenfield development
— Standard Naming convention
— Create Conditions
— Create Policy 


use DemoDB
go

-- Successful Object Creation
-- Table
CREATE TABLE UserTable_test_me3 (test int)
GO

-- Procedure
CREATE Procedure PRC_Test_proc AS SELECT GETDATE()
GO

-- View
CREATE VIEW UserView_Test AS SELECT test from UserTable_test_me3
GO

-- Failed Object Creation due to Policy enforcement
-- Table
CREATE TABLE test_me3 (test int)
GO

Create Procedure Test_proc AS select GETDATE()
GO

Create View VW_Test as select test from UserTable_test_me3
GO

Create View Test as select test from UserTable_test_me3
GO

-- Fails
EXEC sp_rename 'UserTable_test_me3', 'test_me3'
GO 

--  Check MSDB for policy execution Failures

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 AND a.execution_date >= GETDATE() -1
    ORDER BY a.execution_date DESC, c.name ASC, a.target_query_expression ASC;
GO

-- Existing environment somewhat different ::

select * from msdb.dbo.syspolicy_policies_internal order by date_created desc;
GO
-- Delete the policy
 -- Can use SSMS to do this as well.
Declare @Object_set_ID_Delete INT
set @Object_set_ID_Delete = (select object_set_id from msdb.dbo.syspolicy_policies_internal where name = 'Standard Naming Convention')

EXEC msdb.dbo.sp_syspolicy_delete_policy @name= 'Standard Naming Convention'
EXEC msdb.dbo.sp_syspolicy_delete_object_set @object_set_id = @Object_set_ID_Delete
GO
-- Delete the Condition
 -- Can use SSMS to do this as well.
 EXEC msdb.dbo.sp_syspolicy_delete_condition @name = 'Standard - Naming Convention'

-- Create an Existing environment

Create Database Demo_existing
GO

USE Demo_existing
GO
create table test_me3 (test int)
GO

Create Procedure Test_proc AS select GETDATE() as The_Time_is_Now
GO

Create View VW_Test as select test from test_me3
GO

Create View Test as select test from test_me3
GO

—  Re-Create Condition and Policy
— Using t-sql versus SSMS
  

Declare @condition_id int
EXEC msdb.dbo.sp_syspolicy_add_condition @name=N'Standard - Naming Convention', @description=N'Enforcement of Standard Naming Convention

The Object you attempted to create was rolled back.

This Policy prevents the following naming standards ::

Object names MAY NOT begin with "tbl" OR "sp" OR "vw"

AND

Ojbect Names MUST begin with "UserTable" OR "PRC" OR "UserView"

', @facet=N'IMultipartNameFacet', @expression=N'<Operator>
  <TypeClass>Bool</TypeClass>
  <OpType>AND</OpType>
  <Count>2</Count>
  <Group>
    <TypeClass>Bool</TypeClass>
    <Count>1</Count>
    <Operator>
      <TypeClass>Bool</TypeClass>
      <OpType>OR</OpType>
      <Count>2</Count>
      <Operator>
        <TypeClass>Bool</TypeClass>
        <OpType>OR</OpType>
        <Count>2</Count>
        <Operator>
          <TypeClass>Bool</TypeClass>
          <OpType>NOT_LIKE</OpType>
          <Count>2</Count>
          <Attribute>
            <TypeClass>String</TypeClass>
            <Name>Name</Name>
          </Attribute>
          <Constant>
            <TypeClass>String</TypeClass>
            <ObjType>System.String</ObjType>
            <Value>tbl%</Value>
          </Constant>
        </Operator>
        <Operator>
          <TypeClass>Bool</TypeClass>
          <OpType>NOT_LIKE</OpType>
          <Count>2</Count>
          <Attribute>
            <TypeClass>String</TypeClass>
            <Name>Name</Name>
          </Attribute>
          <Constant>
            <TypeClass>String</TypeClass>
            <ObjType>System.String</ObjType>
            <Value>vw%</Value>
          </Constant>
        </Operator>
      </Operator>
      <Operator>
        <TypeClass>Bool</TypeClass>
        <OpType>NOT_LIKE</OpType>
        <Count>2</Count>
        <Attribute>
          <TypeClass>String</TypeClass>
          <Name>Name</Name>
        </Attribute>
        <Constant>
          <TypeClass>String</TypeClass>
          <ObjType>System.String</ObjType>
          <Value>sp%</Value>
        </Constant>
      </Operator>
    </Operator>
  </Group>
  <Group>
    <TypeClass>Bool</TypeClass>
    <Count>1</Count>
    <Operator>
      <TypeClass>Bool</TypeClass>
      <OpType>OR</OpType>
      <Count>2</Count>
      <Operator>
        <TypeClass>Bool</TypeClass>
        <OpType>OR</OpType>
        <Count>2</Count>
        <Operator>
          <TypeClass>Bool</TypeClass>
          <OpType>LIKE</OpType>
          <Count>2</Count>
          <Attribute>
            <TypeClass>String</TypeClass>
            <Name>Name</Name>
          </Attribute>
          <Constant>
            <TypeClass>String</TypeClass>
            <ObjType>System.String</ObjType>
            <Value>UserTable%</Value>
          </Constant>
        </Operator>
        <Operator>
          <TypeClass>Bool</TypeClass>
          <OpType>LIKE</OpType>
          <Count>2</Count>
          <Attribute>
            <TypeClass>String</TypeClass>
            <Name>Name</Name>
          </Attribute>
          <Constant>
            <TypeClass>String</TypeClass>
            <ObjType>System.String</ObjType>
            <Value>UserView%</Value>
          </Constant>
        </Operator>
      </Operator>
      <Operator>
        <TypeClass>Bool</TypeClass>
        <OpType>LIKE</OpType>
        <Count>2</Count>
        <Attribute>
          <TypeClass>String</TypeClass>
          <Name>Name</Name>
        </Attribute>
        <Constant>
          <TypeClass>String</TypeClass>
          <ObjType>System.String</ObjType>
          <Value>PRC%</Value>
        </Constant>
      </Operator>
    </Operator>
  </Group>
</Operator>', @is_name_condition=0, @obj_name=N'', @condition_id=@condition_id OUTPUT
Select @condition_id

GO 

—-
—   Condition was created with an ID of XX.
—- 

Declare @object_set_id int
EXEC msdb.dbo.sp_syspolicy_add_object_set @object_set_name=N'Standard Naming Convention_ObjectSet_1', @facet=N'IMultipartNameFacet', @object_set_id=@object_set_id OUTPUT
Select @object_set_id

Declare @target_set_id int
EXEC msdb.dbo.sp_syspolicy_add_target_set @object_set_name=N'Standard Naming Convention_ObjectSet_1', @type_skeleton=N'Server/Database/StoredProcedure', @type=N'PROCEDURE', @enabled=False, @target_set_id=@target_set_id OUTPUT
Select @target_set_id

EXEC msdb.dbo.sp_syspolicy_add_target_set_level @target_set_id=@target_set_id, @type_skeleton=N'Server/Database/StoredProcedure', @level_name=N'StoredProcedure', @condition_name=N'', @target_set_level_id=0
EXEC msdb.dbo.sp_syspolicy_add_target_set_level @target_set_id=@target_set_id, @type_skeleton=N'Server/Database', @level_name=N'Database', @condition_name=N'', @target_set_level_id=0

EXEC msdb.dbo.sp_syspolicy_add_target_set @object_set_name=N'Standard Naming Convention_ObjectSet_1', @type_skeleton=N'Server/Database/Synonym', @type=N'SYNONYM', @enabled=False, @target_set_id=@target_set_id OUTPUT
Select @target_set_id

EXEC msdb.dbo.sp_syspolicy_add_target_set_level @target_set_id=@target_set_id, @type_skeleton=N'Server/Database/Synonym', @level_name=N'Synonym', @condition_name=N'', @target_set_level_id=0
EXEC msdb.dbo.sp_syspolicy_add_target_set_level @target_set_id=@target_set_id, @type_skeleton=N'Server/Database', @level_name=N'Database', @condition_name=N'', @target_set_level_id=0

EXEC msdb.dbo.sp_syspolicy_add_target_set @object_set_name=N'Standard Naming Convention_ObjectSet_1', @type_skeleton=N'Server/Database/Table', @type=N'TABLE', @enabled=False, @target_set_id=@target_set_id OUTPUT
Select @target_set_id

EXEC msdb.dbo.sp_syspolicy_add_target_set_level @target_set_id=@target_set_id, @type_skeleton=N'Server/Database/Table', @level_name=N'Table', @condition_name=N'', @target_set_level_id=0
EXEC msdb.dbo.sp_syspolicy_add_target_set_level @target_set_id=@target_set_id, @type_skeleton=N'Server/Database', @level_name=N'Database', @condition_name=N'', @target_set_level_id=0

EXEC msdb.dbo.sp_syspolicy_add_target_set @object_set_name=N'Standard Naming Convention_ObjectSet_1', @type_skeleton=N'Server/Database/UserDefinedFunction', @type=N'FUNCTION', @enabled=False, @target_set_id=@target_set_id OUTPUT
Select @target_set_id

EXEC msdb.dbo.sp_syspolicy_add_target_set_level @target_set_id=@target_set_id, @type_skeleton=N'Server/Database/UserDefinedFunction', @level_name=N'UserDefinedFunction', @condition_name=N'', @target_set_level_id=0
EXEC msdb.dbo.sp_syspolicy_add_target_set_level @target_set_id=@target_set_id, @type_skeleton=N'Server/Database', @level_name=N'Database', @condition_name=N'', @target_set_level_id=0

EXEC msdb.dbo.sp_syspolicy_add_target_set @object_set_name=N'Standard Naming Convention_ObjectSet_1', @type_skeleton=N'Server/Database/UserDefinedType', @type=N'TYPE', @enabled=False, @target_set_id=@target_set_id OUTPUT
Select @target_set_id

EXEC msdb.dbo.sp_syspolicy_add_target_set_level @target_set_id=@target_set_id, @type_skeleton=N'Server/Database/UserDefinedType', @level_name=N'UserDefinedType', @condition_name=N'', @target_set_level_id=0
EXEC msdb.dbo.sp_syspolicy_add_target_set_level @target_set_id=@target_set_id, @type_skeleton=N'Server/Database', @level_name=N'Database', @condition_name=N'', @target_set_level_id=0

EXEC msdb.dbo.sp_syspolicy_add_target_set @object_set_name=N'Standard Naming Convention_ObjectSet_1', @type_skeleton=N'Server/Database/View', @type=N'VIEW', @enabled=False, @target_set_id=@target_set_id OUTPUT
Select @target_set_id

EXEC msdb.dbo.sp_syspolicy_add_target_set_level @target_set_id=@target_set_id, @type_skeleton=N'Server/Database/View', @level_name=N'View', @condition_name=N'', @target_set_level_id=0
EXEC msdb.dbo.sp_syspolicy_add_target_set_level @target_set_id=@target_set_id, @type_skeleton=N'Server/Database', @level_name=N'Database', @condition_name=N'', @target_set_level_id=0

EXEC msdb.dbo.sp_syspolicy_add_target_set @object_set_name=N'Standard Naming Convention_ObjectSet_1', @type_skeleton=N'Server/Database/XmlSchemaCollection', @type=N'XMLSCHEMACOLLECTION', @enabled=False, @target_set_id=@target_set_id OUTPUT
Select @target_set_id

EXEC msdb.dbo.sp_syspolicy_add_target_set_level @target_set_id=@target_set_id, @type_skeleton=N'Server/Database/XmlSchemaCollection', @level_name=N'XmlSchemaCollection', @condition_name=N'', @target_set_level_id=0
EXEC msdb.dbo.sp_syspolicy_add_target_set_level @target_set_id=@target_set_id, @type_skeleton=N'Server/Database', @level_name=N'Database', @condition_name=N'', @target_set_level_id=0
GO
Declare @policy_id int
EXEC msdb.dbo.sp_syspolicy_add_policy @name=N'Standard Naming Convention', @condition_name=N'Standard - Naming Convention', @execution_mode=1, @is_enabled=True, @policy_id=@policy_id OUTPUT, @object_set=N'Standard Naming Convention_ObjectSet_1'
Select @policy_id

-- The next piece will Update the Targets for the policy
GO
EXEC msdb.dbo.sp_syspolicy_update_target_set @target_set_id=128, @enabled=TRUE
EXEC msdb.dbo.sp_syspolicy_update_target_set_level @target_set_id=128, @type_skeleton=N'Server/Database/StoredProcedure', @condition_name=N''
EXEC msdb.dbo.sp_syspolicy_update_target_set_level @target_set_id=128, @type_skeleton=N'Server/Database', @condition_name=N''

EXEC msdb.dbo.sp_syspolicy_update_target_set @target_set_id=129, @enabled=TRUE
EXEC msdb.dbo.sp_syspolicy_update_target_set_level @target_set_id=129, @type_skeleton=N'Server/Database/Synonym', @condition_name=N''
EXEC msdb.dbo.sp_syspolicy_update_target_set_level @target_set_id=129, @type_skeleton=N'Server/Database', @condition_name=N''

EXEC msdb.dbo.sp_syspolicy_update_target_set @target_set_id=130, @enabled=TRUE
EXEC msdb.dbo.sp_syspolicy_update_target_set_level @target_set_id=130, @type_skeleton=N'Server/Database/Table', @condition_name=N''
EXEC msdb.dbo.sp_syspolicy_update_target_set_level @target_set_id=130, @type_skeleton=N'Server/Database', @condition_name=N''

EXEC msdb.dbo.sp_syspolicy_update_target_set @target_set_id=131, @enabled=TRUE
EXEC msdb.dbo.sp_syspolicy_update_target_set_level @target_set_id=131, @type_skeleton=N'Server/Database/UserDefinedFunction', @condition_name=N''
EXEC msdb.dbo.sp_syspolicy_update_target_set_level @target_set_id=131, @type_skeleton=N'Server/Database', @condition_name=N''

EXEC msdb.dbo.sp_syspolicy_update_target_set @target_set_id=132, @enabled=TRUE
EXEC msdb.dbo.sp_syspolicy_update_target_set_level @target_set_id=132, @type_skeleton=N'Server/Database/UserDefinedType', @condition_name=N''
EXEC msdb.dbo.sp_syspolicy_update_target_set_level @target_set_id=132, @type_skeleton=N'Server/Database', @condition_name=N''

EXEC msdb.dbo.sp_syspolicy_update_target_set @target_set_id=133, @enabled=TRUE
EXEC msdb.dbo.sp_syspolicy_update_target_set_level @target_set_id=133, @type_skeleton=N'Server/Database/View', @condition_name=N''
EXEC msdb.dbo.sp_syspolicy_update_target_set_level @target_set_id=133, @type_skeleton=N'Server/Database', @condition_name=N''

EXEC msdb.dbo.sp_syspolicy_update_target_set @target_set_id=134, @enabled=TRUE
EXEC msdb.dbo.sp_syspolicy_update_target_set_level @target_set_id=134, @type_skeleton=N'Server/Database/XmlSchemaCollection', @condition_name=N''
EXEC msdb.dbo.sp_syspolicy_update_target_set_level @target_set_id=134, @type_skeleton=N'Server/Database', @condition_name=N''
GO
EXEC msdb.dbo.sp_syspolicy_update_policy @policy_id=62, @execution_mode=1, @schedule_uid=N'00000000-0000-0000-0000-000000000000'
GO

–Verify that the new policy is working as intended 

-- Will FAIL 
<pre>create table TEST_Post_NewPolicy (testcol int)
GO

-- Will suceed
create table UserTable_Post_NewPolicy (testcol int)
GO
-- Find an object to alter
Select * from sysobjects where uid = 1 and xtype in ('v','u','p')

-- Alter object to another invalid naming convention

-- Will FAIL
Alter Procedure Test_proc
AS select GETDATE()  as [When]

--Will Suceed
Drop Procedure Test_proc

-- Alter an Invalid object to a valid naming convention

-- Successful
EXEC sp_rename 'Test', 'UserView_Test'
GO 

-- Check the execution history

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 AND a.execution_date >= GETDATE() -1
     ORDER BY a.execution_date DESC, c.name ASC, a.target_query_expression ASC;
GO

With the sql above and the slide deck that is attached to this post, you should be well on your way to creating your own Policies.

Advertisements
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: