THE SQL Server Blog Spot on the Web

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

Allen White

Discover Facets and Properties in PowerShell

When you're trying to set up scripts to deploy policies to your servers (because you ALWAYS automate with PowerShell, right?), it can be sometimes difficult to determine exactly what facets are there, and when you do know the facet name, what properties are available to you.

First, in a native PowerShell window you'll need to load the assemblies with the Policy-Based Management objects. Because PBM was originally called Dynamic Management Framework, the DLL carries the original name, so the following two lines will load the correct assemblies:


The next thing to do is to connect to our SQL Server instance, and load the instance's PolicyStore object, which is where the existing policies are kept.

$conn = New-Object Microsoft.SQlServer.Management.Sdk.Sfc.SqlStoreConnection("server=SQLTBWS\INST01;Trusted_Connection=true");
$PolicyStore = New-Object Microsoft.SqlServer.Management.DMF.PolicyStore($conn);

Now we can start exploring. We can pull the list of facets by referencing them within the PolicyStore object, and then store them in a variable called $facets. We can then pipe that variable into a select-object cmdlet and get the name and description of the available facets:

$facets = [Microsoft.SqlServer.Management.Dmf.PolicyStore]::Facets
$facets | select Name, Description

Now we know what facets are available. Let's say we want to set up some database maintenance policies. We can figure out what properties are available to us by creating a facet object variable for the 'IDatabaseMaintenanceFacet' facet. Then we grab the properties list from it by setting a variable to the facet's FacetProperties object. Finally we can pipe the property list through the select-object cmdlet to see the name and property type of each property for the facet:

$f = $facets['IDatabaseMaintenanceFacet']
$fp = $f.FacetProperties
$fp | select Name, PropertyType

Once we have this information we can use it to build conditions, and from those conditions, the policies we want to establish on our servers.


Published Monday, September 27, 2010 11:56 AM by AllenMWhite



olive22 said:

Thanks for this great insight.

I'm now trying to actually pull out the value of a FacetProperty.

Is this feasible ?

September 28, 2010 11:19 AM

AllenMWhite said:

Olive, the property is set on the object you're evaluating.  You use the facet with a setting you define in a condition, and then include that condition in a policy.  Then you can evaluate the policy to see if an object (instance, database, table, etc.) complies with the policy.

September 28, 2010 2:12 PM

Rick Cornell said:

Allen: Excellent discussion, I will give it a try soon! You should come down to DC SQL Saturday this December!

October 5, 2010 3:41 PM
New Comments to this post are disabled

About AllenMWhite

Allen White is a consultant and mentor for Upsearch Technology Services in Northeast Ohio. He has worked as a Database Administrator, Architect and Developer for over 30 years, supporting both the Sybase and Microsoft SQL Server platforms over that period.

This Blog


Privacy Statement