SQL Server 2012 : Defining Policies (part 2) - Conditions

7/24/2014 4:28:53 AM


Conditions are the second step in the chain and provide the logical connection between facet properties and policies. Most of the key policy design decisions are made while creating conditions.

To begin building a new condition, use either the Management → Policy Management → Conditions context menu and choose New Condition or Object Explorer Management → Policy Management → Facets → Database context menu.

You can open an existing condition by double-clicking the condition or by using the Property command in its context menu. A condition may have multiple expressions, but each condition is based on only one facet, so every property in all the expressions in a condition must belong to the same facet.

Condition expressions use facet properties in boolean expressions that can be evaluated as true or false. The expression consists of a facet property, a comparison operator (such as =, !=, in, not in, like, not like), and a value.

To construct a condition that tests a database's autoshrink property, the expression would use the database facet and the @AutoShrink property, as shown in Figure 4. In this case the full expression is:

Figure 4 This condition includes an expression that tests the Database Facet's @AutoShrink. The condition evaluates as True if @AutoShrink = False.

@AutoShrink = False

Best Practice
Think of condition expressions as positive statements. Instead of thinking, “No database should be set to autoshrink,” think “All databases should have autoshrink set to False.”

The ellipsis button under Field and Value opens the Advanced Edit dialog box, as shown in Figure 5. The Cell value is typically a property, function, or a literal value; however, it is possible to build more advanced expressions that reference DMV or system tables.

Figure 5 Use the Advanced Edit dialog to create each side of the expression. In this case it shows the left side of the AutoShrink expression.


A condition may include multiple expressions, in which case the AndOr column defines how they are evaluated.

Best Practice
In the entire policy design scheme, the only place that enables multiples is designing multiple expressions within a single condition. Therefore, if every expression should indeed be tested, encapsulating multiple expressions in a single condition can reduce the number of conditions and policies.

The open condition's description page may be used to record a description of the condition, and the dependent policies page lists the policies based on the condition. After the condition is created, it may be enforced by one or more policies. To programmatically view the created conditions, query the dbo.syspolicy_conditions view in the MSDB database:

select * from msdb.dbo.syspolicy_conditions
To build advanced conditions that check factors other than the built-in facets, look into the executeSQL and executeWMI functions.
  •  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
    Most View
    Customizing the Windows Vista Interface : Customizing the Start Menu for Easier Program and Document Launching (part 1)
    Windows 8 : Storage Spaces (part 3) - A More Resilient Space: Two Disks, Two-Way Mirroring
    Windows Server 2008 and Windows Vista : Working with GPOs - Group Policy Modeling
    Windows Server 2003 : Installing and Configuring Printers
    Haswell Ultrabooks Shootout Featherweight Battle Royale (Part 1) - Acer Aspire S7, ASUS ZENBOOK UX301
    Windows 7 : World Wide Zune: A Look at the Zune Online Services
    How To Buy…A Media Streaming Device (Part 1)
    Samsung Series 5 Ultra – Windows 8 Touches Ground
    Windows 8 : Accessing System Image Backup and Recovery Functionality with Windows Backup, Cloud Backup
    Google Nexus 7 Tablet - Simple, Beautiful and Beyond Smart
    Popular Tags
    Microsoft Access Microsoft Excel Microsoft OneNote Microsoft PowerPoint Microsoft Project Microsoft Visio Microsoft Word Active Directory Biztalk Exchange Server Microsoft LynC Server Microsoft Dynamic Sharepoint Sql Server Windows Server 2008 Windows Server 2012 Windows 7 Windows 8 Adobe Indesign Adobe Flash Professional Dreamweaver Adobe Illustrator Adobe After Effects Adobe Photoshop Adobe Fireworks Adobe Flash Catalyst Corel Painter X CorelDRAW X5 CorelDraw 10 QuarkXPress 8 windows Phone 7 windows Phone 8 BlackBerry Android Ipad Iphone iOS
    Top 10
    Review : Acer Aspire R13
    Review : Microsoft Lumia 535
    Review : Olympus OM-D E-M5 Mark II
    TomTom Runner + MultiSport Cardio
    Timex Ironman Run Trainer 2.0
    Suunto Ambit3 Peak Sapphire HR
    Polar M400
    Garmin Forerunner 920XT
    Sharepoint 2013 : Content Model and Managed Metadata - Publishing, Un-publishing, and Republishing
    Sharepoint 2013 : Content Model and Managed Metadata - Content Type Hubs