The American Heritage Dictionary of the English Language (Fourth Edition) defines facet as: One of numerous aspects, as of a subject. The definition of Aspect (from the same dictionary) is: Appearance to the eye, especially from a specific vantage point. The definition of Facet in SQL Server Books Online is: 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.

Though Policy-Based Management relies heavily on facets, they're really concept that goes beyond PBM. When I was a kid I'd get these pictures that had hidden items. To find the items you had to use different pieces of colored translucent paper (like red or green). Just looking at the picture you couldn't see the "hidden" elements. But placing the red sheet over the picture revealed the hidden treasures. Facets are sort of the same way.

Another analogy I've used to describe facets is Internet Explorer. Think back to the early days of IE and you wanted to change the way IE handled ActiveX controls. You'd navigate to Tools -> Options, go to the Advance tab and scroll through a long list of settings. No one every really knew if their browser settings were secure or not. Think of the handling of ActiveX controls as a property of the browser with the values Do not Allow, Prompt, Always Allow. Requiring a user to reason about this, and many other properties, is insane. My mom, for example, has no idea what this means or what the proper setting should be. In IE 5.5 the IE team introduced a security facet. They didn't call it that but that's what it is. They introduced the concept of a Security Level with the values: High, Medium-High, and Medium. Effectively this is a new logical property in IE. I call it a logic property as it has no intrinsic meaning - it doesn't really control anything. Ah, but what it does do is incredibly powerful (yes, I'm contradicting myself). This logical property is really an aggregation of several physical properties (e.g. ActiveX Controls) a proxy for a group of settings. By creating the logical property the administration of IE is greatly simplified. My mom can understand Medium-high security - more importantly I can walk her through the steps to properly configure IE. Facets in SQL Server are very similar concept.

Take Database, for example. There are lots of physical properties for a database: Name, Collation, Compatibility Level, Auto Close, Encryption Enabled, Log File location, Data File Location, etc. In fact, a database has something like 60 properties. Wow, how does one reason about 60 properties? The facet allows us to do three powerful things: 1) create a single view of all of the physical properties for a database, 2) create specific views of database properties; for example, security, compliance, performance, etc and 3) create logical properties which are derived from one or more physical properties. There was a key point I just introduced: facets are over objects (target types) in the system and an object (target type) can have more than one facet. Databases, tables, views, logins, can have facets. in SQL Server 2008 we didn't cover every object (target type). For example, we didn't cover Replication, Agent, or DB Mail. But more on that later. So just think of a facet as a collection of related properties for a given object or similar set of objects.

The implementation of a facet is .Net code - for us it's C#. In SQL Server 2008 users cannot create their own facets, although we designed the system with extensibility in mind.

Let's take a closer look at how facets are exposed in the Management Studio. There are two ways to look at a facet: 1) the facet definition and 2) an object (target type) with respect to a particular facet.

Facet Definition

The facet definitions, meaning the description and properties that make up the facet, can be found in OE. See the picture below.

ObjectExplorerFacetView

Right-Clicking any facet and selecting Properties displays the Facet Properties dialog. In this dialog you'll get a description of the facet, the target types it applies to and a list of all of the properties (with description) that make up the facet. The sample below is for the Database facet.

DatabaseFacetProperties

This covers the first view of facets: the facet definition. Now let's look at viewing a particular target in the context of a facet. The screen shot below shows the context menu for a database.

DatabaseContextMenu

Look about halfway down the context menu and you'll see menu item called Facets. Selecting this menu item launches the Facet dialog for database. The screen shot below shows the dialog for the AdventureWorks sample database.

DatabaseViewFacets

The Facet combo box is expanded. This allows you to choose which facet you want to view for the selected object. Only the facets that apply to the selected object type are displayed. In this screen shot the Database facet is selected. This shows you all of the properties on a database and the current value for each property for the selected database.

Two other key things to point out: 1) You'll notice that some properties are bold while others are grayed out. The bolded properties are read/write and can be updated; the grayed out properties are read-only. This means, instead of going to the standard Database properties dialog you can come here and change a database property. 2) You'll notice the button in the bottom right labeled Export Current State as Policy...". Let's suppose you've configured the database exactly as you like it but you want a create a policy that monitors any changes from the original state. This option creates a policy and condition for the selected facet using for the current state. It's a very nice shortcut for creating policies, saving you a bunch of time typing in properties and values.

The final thing a facet does is define the supported evaluation modes for policies. All facets for the Database Engine can be checked on schedule. Check on Change: Prevent and Check on Change: Log depend upon the eventing model of the Database Engine. Because the supported evaluation modes are on the facet all of the properties in the facet must support support the same set of events. This means for a facet to support Check on Change: Prevent all of the properties in the facet must raise a DDL event.

The three tables below are a great reference to print out and keep handy when using PBM.

This table maps each facet to the supported evaluation mode(s):

Facet Name CoC: Prevent CoC: Log CoS
Application Role X X X
Asymmetric Key X X X
Audit     X
Backup Device     X
Broker Priority     X
Broker Service     X
Certificate     X
Credential     X
Cryptographic Provider     X
Data File     X
Database     X
Database Audit Specification     X
Database Ddl Trigger     X
Database Maintenance     X
Database Option   X X
Database Performance     X
Database Role X X X
Database Security     X
Default     X
Endpoint X X X
File Group     X
Full Text Catalog     X
Full Text Index     X
Full Text Stop List     X
Index     X
Linked Server     X
Log File     X
Login     X
Login Options X X X
Message Type     X
Multipart Name X X X
Name     X
Partition Function     X
Partition Scheme     X
Plan Guide     X
Remote Service Binding     X
Resource Governor     X
Resource Pool X X X
Rule     X
Schema X X X
Server     X
Server Audit     X
Server Audit Specification     X
Server Configuration   X X
Server Ddl Trigger     X
Server Information     X
Server Performance     X
Server Security     X
Server Settings     X
Server Setup     X
Service Contract     X
Service Queue     X
Service Route     X
Statistic     X
Stored Procedure X X X
Surface Area   X X
Surface Area for AS      
Surface Area for RS      
Symmetric Key     X
Synonym     X
Table     X
Table Options X X X
Trigger     X
User     X
User Defined Aggregate     X
User Defined Data Type     X
User Defined Function X X X
User Defined Table Type     X
User Defined Type     X
User Options X X X
View     X
View Options X X X
Workload Group X X X
Xml Schema Collection     X

The tables below map the relationship between Facets and Target Types. The table on the left shows the facets for each target type. The table on the right shows the target types supported by each facets.

Facets by Target Type   Target Types by Facet
ANALYSIS SERVICES   Application Role
Surface Area for AS   APPLICATION ROLE
APPLICATION ROLE   Asymmetric Key
Application Role   ASYMMETRIC KEY
Name   Audit
ASYMMETRIC KEY   AUDIT
Asymmetric Key   Backup Device
Name   BACKUP DEVICE
ASYMMETRIC KEY USER   Broker Priority
User Options   BROKER PRIORITY
AUDIT   Broker Service
Audit   BROKER SERVICE
BACKUP DEVICE   Certificate
Backup Device   CERTIFICATE
BROKER PRIORITY   Credential
Broker Priority   CREDENTIAL
BROKER SERVICE   Cryptographic Provider
Broker Service   CRYPTOGRAPHIC PROVIDER
CERTIFICATE   Data File
Certificate   DATA FILE
Name   Database
CERTIFICATE USER   DATABASE
User Options   Database Audit Specification
CREDENTIAL   DATABASE AUDIT SPECIFICATION
Credential   Database Ddl Trigger
CRYPTOGRAPHIC PROVIDER   DATABASE DDL TRIGGER
Cryptographic Provider   Database Maintenance
DATA FILE   DATABASE
Data File   Database Options
DATABASE   DATABASE
Database   Database Performance
Database Maintenance   DATABASE
Database Options   Database Role
Database Performance   ROLE
Database Security   Database Security
DATABASE AUDIT SPECIFICATION   DATABASE
Database Audit Specification   Default
DATABASE DDL TRIGGER   DEFAULT
Database Ddl Trigger   Endpoint
DATABASE ROLE   ENDPOINT
Name   File Group
DEFAULT   FILE GROUP
Default   Full Text Catalog
Name   FULL TEXT CATALOG
ENDPOINT   Full Text Index
Endpoint   FULL TEXT INDEX
FILE GROUP   Full Text Stop List
File Group   FULL TEXT STOP LIST
FULL TEXT CATALOG   Index
Full Text Catalog   INDEX
FULL TEXT INDEX   Linked Server
Full Text Index   LINKED SERVER
FULL TEXT STOP LIST   Log File
Full Text Stop List   LOG FILE
FUNCTION   Login
Multipart Name   LOGIN
User Defined Function   Login Options
GROUP USER   LOGIN
User Options   Message Type
INDEX   MESSAGE TYPE
Index   Multipart Name
Name   FUNCTION
LINKED SERVER   PROCEDURE
Linked Server   SYNONYM
LOG FILE   TABLE
Log File   TYPE
LOGIN   VIEW
Login   XML SCHEMA COLLECTION
Login Options   Name
MESSAGE TYPE   APPLICATION ROLE
Message Type   ASYMMETRIC KEY
PARTITION FUNCTION   CERTIFICATE
Partition Function   DATABASE ROLE
PARTITION SCHEME   DEFAULT
Partition Scheme   INDEX
PLAN GUIDE   RULE
Plan Guide   SCHEMA
PROCEDURE   SQL ASSEMBLY
Multipart Name   STORED PROCEDURE
Stored Procedure   SYMMETRIC KEY
REMOTE SERVICE BINDING   SYNONYM
Remote Service Binding   TABLE
REPORTING SERVICES   TRIGGER
Surface Area for RS   USER
RESOURCE GOVENOR   USER DEFINED FUNCTION
Resource Governor   USER DEFINED TYPE
RESOURCE POOL   VIEW
Resource Pool   XML SCHEMA COLLECTION
ROLE   Partition Function
Database Role   PARTITION FUNCTION
RULE   Partition Scheme
Name   PARTITION SCHEME
Rule   Plan Guide
SCHEMA   PLAN GUIDE
Name   Remote Service Binding
Schema   REMOTE SERVICE BINDING
SERVER   Resource Governor
Server   RESOURCE GOVENOR
Server Configuration   Resource Pool
Server Information   RESOURCE POOL
Server Performance   Rule
Server Security   RULE
Server Settings   Schema
Server Setup   SCHEMA
Surface Area   Server
SERVER AUDIT   SERVER
Server Audit   Server Audit
SERVER AUDIT SPECIFICATION   SERVER AUDIT
Server Audit Specification   Server Audit Specification
SERVER DLL TRIGGER   SERVER AUDIT SPECIFICATION
Server Ddl Trigger   Server Configuration
SERVICE CONTRACT   SERVER
Service Contract   Server Ddl Trigger
SERVICE QUEUE   SERVER DLL TRIGGER
Service Queue   Server Information
SERVICE ROUTE   SERVER
Service Route   Server Performance
SQL ASSEMBLY   SERVER
Name   Server Security
SQL USER   SERVER
User Options   Server Settings
STATISTIC   SERVER
Statistic   Server Setup
STORED PROCEDURE   SERVER
Name   Service Contract
SYMMETRIC KEY   SERVICE CONTRACT
Name   Service Queue
Symmetric Key   SERVICE QUEUE
SYNONYM   Service Route
Multipart Name   SERVICE ROUTE
Name   Statistic
Synonym   STATISTIC
TABLE   Stored Procedure
Multipart Name   PROCEDURE
Name   Surface Area
Table   SERVER
Table Options   Surface Area for AS
TRIGGER   ANALYSIS SERVICES
Name   Surface Area for RS
Trigger   REPORTING SERVICES
TYPE   Symmetric Key
Multipart Name   SYMMETRIC KEY
USER   Synonym
Name   SYNONYM
User   Table
USER DEFINED AGGREGATE   TABLE
User Defined Aggregate   Table Options
USER DEFINED DATA TYPE   TABLE
User Defined Data Type   Trigger
USER DEFINED FUNCTION   TRIGGER
Name   User
USER DEFINED TABLE TYPE   USER
User Defined Table Type   User Defined Aggregate
USER DEFINED TYPE   USER DEFINED AGGREGATE
Name   User Defined Data Type
User Defined Type   USER DEFINED DATA TYPE
VIEW   User Defined Function
Multipart Name   FUNCTION
Name   User Defined Table Type
View   USER DEFINED TABLE TYPE
View Options   User Defined Type
WINDOWS USER   USER DEFINED TYPE
User Options   User Options
WORKLOAD GROUP   ASYMMETRIC KEY USER
Workload Group   CERTIFICATE USER
XML SCHEMA COLLECTION   GROUP USER
Multipart Name   SQL USER
Name   WINDOWS USER
Xml Schema Collection   View
    VIEW
    View Options
    VIEW
    Workload Group
    WORKLOAD GROUP
    Xml Schema Collection
    XML SCHEMA COLLECTION

 

_____________

About the Author:
Dan Jones is a PM on the SQL Server Manageability team at Microsoft.