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.