Policies may be defined
interactively in Management Studio, loaded in from XML, or defined with
either T-SQL code or PowerShell with DMO.
What's New with PBM in SQL 2012
Policy-Based Management as a whole
hasn't changed much since SQL Server 2008 R2, but there are 8 new
facets available to you. The new facets for availability groups are the
basis of the flexible failover polices you can configure for AlwaysOn
Availability Groups, which you can read more about in Chapter 27
“Database Mirroring.” The following list calls out the new facets in
SQL Server 2012 and what properties they contain.
- Availability Database: Properties of databases in an availability group including their name, status and synchronization states
- Availability Group: Availability Group properties, such as
health check timeouts, failure condition level, and name of the replica
holding the role of primary in the group
- Availability Group State: State of the Availability Group
- Availability Replica: Properties of the Availability Replica object including operational state, quorum vote count, connection state and much more
- Search Property List: Properties of the Search Property Lists, used in Semantic Search
- Sequence: Properties of sequence objects
- Database Replica State: Properties of the physical database replicas participating in an availability group
- Server Role: Properties of the server role object
There are three types of PBM objects. In a sense
they function as three levels, with Policies built from Conditions,
which are built out of facets:
- 84 Facets: Defined only by Microsoft, are collections of
properties that represent a management dimension. For example, the
Table facet has 34 specific properties that can be checked about a
table. Examples of common facets include logins, a server, a linked
server, or an index. Inside each facet are anywhere from a handful to
dozens of properties, which can be referred to by a condition. (For a
full list of the facets, see Table 1 later in this chapter.)
- Conditions: Defined by the DBA, based on one facet, are the
anted states, or values, for facet properties. An example of a
condition is to check that a database's Recovery mode must be set to
Simple mode. Conditions can contain one or more clauses in them.
- Policies: Defined by the DBA, based on a single condition,
declare how and upon what object (server, database, and so on) the
condition should be enforced.
The UI for PBM is in Management Studio's Object Explorer under the Management node, as shown in Figure 1.
For many other objects within Management Studio, you can access PBM
features related to that object by selecting Facets from the object's
context menu.
Management Facets
A brilliant-cut diamond has 58 facets.
PBM has 84 management facets.. The easiest way to see all the facets is
to open the Facets node under Management → Policy Management (refer to Figure 1).
In database design terminology, there's a
many-to-many relationship between SQL Server object types and
properties. For example, database facet properties apply only to
databases, but the term facet can apply to 84 different types of SQL
Server objects ranging from ApplicationRoles to XmlSchemaCollections, including databases.
The Facet collection is the associative table between SQL Server object types and properties.
You can open a facet by double-clicking the facet
or by selecting the Properties option in its context menu. The Facet
Properties dialog, as shown in Figure 2, has three pages:
- General: Describes the property, lists the type of SQL
Server objects to which the facet properties may apply, and lists the
facet's properties.
- Dependent Policies: Lists the policies that use any dependent conditions of the facet.
- Dependent Conditions: Lists the conditions that use any property from the facet.
The last two pages are not without purpose. There
may potentially be a large number of conditions and policies; the
dependent policies and conditions pages are useful for quickly tracking
down a condition or policy.
It's worth spending some time browsing the facets
and exploring the properties of each facet available from the facet
context menu.
The Object Explorer → Management → Policy
Management → Facets context menu also includes New Condition and New
Policy. The only difference between these context menu items and New
Condition under Conditions or New Policy under Policies is that when
the new condition or policy is opened from the facet node, it
preselects the facet in the drop-down selection box. This fact is worth
noting because PBM is context-sensitive; meaning if you were to try and
evaluate a policy on an object from a context menu on a specific
object, such as a table, you will be presented only with the option to
evaluate policies relating to that particular facet.
There's not much action just looking at facets
because their purpose is to be evaluated by conditions. But you need to
be intimately familiar with the breadth of facets and their properties
to realize the types of policies that may be declared and enforced by
SQL Server.
Because the facet collection is actually a
many-to-many relationship between properties and object types, it makes
sense that there should be a way to see all the facets and properties
that apply to any given object. Indeed, every object in Object Explorer
that can have PBM applied has a Facet menu option in its context menu.
Open the View Facets dialog (shown in Figure 3)
and it presents a drop-down box to select a facet from the list of
facets that applies to the object, and a list of applicable properties.
If the object is an example of what you want, the View Facets dialog
can even export the current state to a new policy. Very cool.
