THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Peter DeBetta's SQL Programming Blog

Peter DeBetta's blog about programming in SQL Server using technologies such as T-SQL, .NET, CLR, C#, VB, Visual Studio, and SQL Server Management Studio.

Policy Based Management Evaluation Modes

Dan Jones wrote a great post about Facets from the new Policy-Based Management feature of SQL Server 2008. At one point in the post, he listed all of the available facets and their supported evaluation modes. Since SQL Server 2008 is not RTM, and since facets can be added in the future, I thought I'd write a query that would list the facets and supported evaluation modes.

Note that the On Demand mode is always supported and has therefore been left out of the query.

;WITH EM (EvalModeID, EvalModeName)
AS
SELECT *
   FROM
       (VALUES 
           (1, 'Check on Change: Prevent'),
           (2, 'Check on Change: Log'),
           (4, 'Check on Schedule')) AS EvalModes (EvalModeID, EvalModeName)
)
, FEM (FacetID, FacetName, EvaluationMode, IsSupported)
AS
SELECT
       pmf.management_facet_id
       , pmf.name
       , EM.EvalModeName
       ,
   FROM msdb.dbo.syspolicy_management_facets AS pmf
       INNER JOIN EM ON pmf.execution_mode & EM.EvalModeID = EM.EvalModeID
)
SELECT FacetID
   , FacetName
   , [Check on Change: Prevent]
   , [Check on Change: Log]
   , [Check on Schedule]
FROM FEM
PIVOT 
COUNT(IsSupported)
   FOR EvaluationMode IN ([Check on Change: Prevent], [Check on Change: Log], [Check on Schedule])
)AS FEMP
ORDER BY FacetName

The results for the existing 72 facets are as follows:

FacetID

FacetName

Check on Change: Prevent

Check on Change: Log

Check on Schedule

1

ApplicationRole

1

1

1

2

AsymmetricKey

1

1

1

3

Audit

0

0

1

4

BackupDevice

0

0

1

5

BrokerPriority

0

0

1

6

BrokerService

0

0

1

7

Certificate

0

0

1

8

Credential

0

0

1

9

CryptographicProvider

0

0

1

10

Database

0

0

1

11

DatabaseAuditSpecification

0

0

1

12

DatabaseDdlTrigger

0

0

1

13

DatabaseRole

1

1

1

14

DataFile

0

0

1

15

Default

0

0

1

16

Endpoint

1

1

1

17

FileGroup

0

0

1

18

FullTextCatalog

0

0

1

19

FullTextIndex

0

0

1

20

FullTextStopList

0

0

1

21

IDatabaseMaintenanceFacet

0

0

1

22

IDatabaseOptions

0

1

1

23

IDatabasePerformanceFacet

0

0

1

24

IDatabaseSecurityFacet

0

0

1

25

ILoginOptions

1

1

1

26

IMultipartNameFacet

1

1

1

27

INameFacet

0

0

1

31

Index

0

0

1

32

IServerAuditFacet

0

0

1

33

IServerConfigurationFacet

0

1

1

34

IServerInformation

0

0

1

35

IServerPerformanceFacet

0

0

1

36

IServerSecurityFacet

0

0

1

38

IServerSettings

0

0

1

37

IServerSetupFacet

0

0

1

41

ISurfaceAreaFacet

0

1

1

28

ITableOptions

1

1

1

29

IUserOptions

1

1

1

30

IViewOptions

1

1

1

42

LinkedServer

0

0

1

43

LogFile

0

0

1

44

Login

0

0

1

45

MessageType

0

0

1

46

PartitionFunction

0

0

1

47

PartitionScheme

0

0

1

48

PlanGuide

0

0

1

49

RemoteServiceBinding

0

0

1

50

ResourceGovernor

0

0

1

51

ResourcePool

1

1

1

52

Rule

0

0

1

53

Schema

1

1

1

54

Server

0

0

1

55

ServerAuditSpecification

0

0

1

56

ServerDdlTrigger

0

0

1

57

ServiceContract

0

0

1

58

ServiceQueue

0

0

1

59

ServiceRoute

0

0

1

60

Statistic

0

0

1

61

StoredProcedure

1

1

1

62

SymmetricKey

0

0

1

63

Synonym

0

0

1

64

Table

0

0

1

65

Trigger

0

0

1

66

User

0

0

1

67

UserDefinedAggregate

0

0

1

68

UserDefinedDataType

0

0

1

69

UserDefinedFunction

1

1

1

70

UserDefinedTableType

0

0

1

71

UserDefinedType

0

0

1

72

View

0

0

1

73

WorkloadGroup

1

1

1

74

XmlSchemaCollection

0

0

1

 

Published Monday, June 16, 2008 4:36 PM by Peter W. DeBetta

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Javal said:

This is a great advance post. Are you sure On Demand mode is always supported?

July 31, 2008 5:57 AM

Leave a Comment

(required) 
(required) 
Submit

About Peter W. DeBetta

Peter DeBetta works for Microsoft as an Application Platform Technology Strategist, specializing in strategies, design, implementation, and deployment of Microsoft SQL Server and .NET solutions. Peter writes courseware, articles, and books – most recently the title Introducing SQL Server 2008 from Microsoft Press. Peter speaks at conferences around the world, including TechEd, SQL PASS Community Summit, DevTeach, SQL Connections, DevWeek, and VSLive!

When Peter isn’t working, you can find him singing and playing guitar (click here to hear an original song by Peter), taking pictures, or simply enjoying life with his wife, son, and daughter.
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement