SQL Server 2012 : Defining Policies (part 1) - Management Facets

- How To Install Windows Server 2012 On VirtualBox
- How To Bypass Torrent Connection Blocking By Your ISP
- How To Install Actual Facebook App On Kindle Fire
7/24/2014 4:27:54 AM

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.

Figure 1 You can also evaluate policies directly against an object, Under the Management node PBM's policies, conditions, and facets in Object Explorer.


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:

Figure 2 The Facet Properties' General page lists of all the facet properties and their descriptions. In this case, it's showing the properties for the Database facet.

  • 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.

Figure 3 The View Facets dialog, opened from any object's context menu, presents a browsable UI of every facet and property that can apply to that type of object, and even can export a new policy to match the current object's settings.

  •  SQL Server 2012 : Validating Server Configuration (part 2) - Evaluate the Policy, Using the Central Management Server
  •  SQL Server 2012 : Validating Server Configuration (part 1) - The Need for a Policy, Create Policy on a Local Server
  •  SQL Server 2012 : Encryption (part 2) - Certificate-Based Encryption, Transparent Data Encryption
  •  SQL Server 2012 : Encryption (part 1) - Encryption Primer, Password-Based Encryption
  •  SQL Server 2012 : Auditing in SQL Server (part 3) - Database Audit Specification Object, User-Defined Audit Event
  •  SQL Server 2012 : Auditing in SQL Server (part 2) - Server Audit Specification Object
  •  SQL Server 2012 : Auditing in SQL Server (part 1) - Auditing Objects, Server Audit Object
  •  SQL Server 2012 : Reordering Nodes within the Hierarchy - The GetReparentedValue Method,Transplanting Subtrees
  •  SQL Server 2012 : Querying Hierarchical Tables - The IsDescendantOf Method
  •  Protecting SQL Server Data : Obfuscation Methods (part 4) - Truncation,Encoding, Aggregation
    Top 10
    - Microsoft Visio 2013 : Adding Structure to Your Diagrams - Finding containers and lists in Visio (part 2) - Wireframes,Legends
    - Microsoft Visio 2013 : Adding Structure to Your Diagrams - Finding containers and lists in Visio (part 1) - Swimlanes
    - Microsoft Visio 2013 : Adding Structure to Your Diagrams - Formatting and sizing lists
    - Microsoft Visio 2013 : Adding Structure to Your Diagrams - Adding shapes to lists
    - Microsoft Visio 2013 : Adding Structure to Your Diagrams - Sizing containers
    - Microsoft Access 2010 : Control Properties and Why to Use Them (part 3) - The Other Properties of a Control
    - Microsoft Access 2010 : Control Properties and Why to Use Them (part 2) - The Data Properties of a Control
    - Microsoft Access 2010 : Control Properties and Why to Use Them (part 1) - The Format Properties of a Control
    - Microsoft Access 2010 : Form Properties and Why Should You Use Them - Working with the Properties Window
    - Microsoft Visio 2013 : Using the Organization Chart Wizard with new data
    - First look: Apple Watch

    - 3 Tips for Maintaining Your Cell Phone Battery (part 1)

    - 3 Tips for Maintaining Your Cell Phone Battery (part 2)
    programming4us programming4us