New Facets and Properties in SQL Server 2012

Problem

With a new version comes new features. In this tip we look at new facets for Policy-Based Management.

Solution

I decided to find out what changes are coming in SQL Server 2012 for PBM facets by doing a compare of what is available in SQL 2008 R2 to the SQL Server 2012 CTP1 release.

I took the 2012 CTP1 preview and installed it onto a VM on my laptop. Once installed I needed to generate a list of all facets available in that version. However, when I checked the msdb database for details on facets I was surprised to find that they are not listed. But they have to be somewhere, because I can see them listed in SSMS.

In order to get a list of facets that I could use for a comparison I was going to need to use some Powershell. The following code will do just that by a connection to an instance named BACON:

[System.Reflection.Assembly]::LoadWithPartialName(‘Microsoft.SQLServer.Dmf’)

[System.Reflection.Assembly]::LoadWithPartialName(‘Microsoft.SQLServer.Management.Sdk.Sfc’)

$conn = New-Object Microsoft.SQLServer.Management.Sdk.Sfc.SqlStoreConnection(“server=.\
BACON;Trusted_Connection=true”);

$PolicyStore = New-Object Microsoft.SQLServer.Management.DMF.PolicyStore($conn);

$facets = [Microsoft.SqlServer.Management.Dmf.PolicyStore]::Facets

out-file PBM_facet_counts.txt

foreach ($i in $facets)

    {

    $f = $facets[$i.Name]

    $fp = $f.FacetProperties

    foreach ($j in $fp)

        {

        $fpstring = $i.name + “,” + $j.Name

        $fpstring | out-file PBM_facet_counts.txt -append

I ran this code twice, once for the 2012 CTP1 instance and once for a SQL2008 R2 instance. The end result of which was two files containing the facet name and properties for each version. With over 1100 lines in each file, I needed an easy way to compare the two files. Powershell to the rescue again!

The following code snippet will get the job done. It simply loads two files into distinct arrays and creates four output files that have descriptive names. When you compare two lists such as these you may want to know all possible duplicates and omissions, although the files named FirstInSecond.txt and SecondInFirst.txt will be identical so we really have only three distinct lists.

$arrFirst = get-content PBM_facet_counts.txt

$arrSecond = get-content Denali_PBM_facet_count.txt

New-Item “C:\POSH\FirstInSecond.txt” -Type file

New-Item “C:\POSH\FirstNotInSecond.txt” -Type file

New-Item “C:\POSH\SecondInFirst.txt” -Type file

New-Item “C:\POSH\SecondNotInFirst.txt” -Type file

Foreach ($First in $arrFirst)

    {

        If ($arrSecond -contains $First)

    {Add-content “C:\POSH\FirstInsecond.txt” $First}

    Else

    {Add-content “C:\POSH\FirstNotInSecond.txt” $First}

    }

Foreach ($Second in $arrSecond)

    {

        If ($arrFirst -contains $Second)

    {Add-content “C:\POSH\SecondInFirst.txt” $Second}

    Else

    {Add-content “C:\POSH\SecondNotInFirst.txt” $Second}

    }

Now, why are FirstInSecond.txt and SecondInFirst.txt going to be identical? Let’s think about those lists in terms of a Venn diagram:

The end result will be three distinct lists; items unique to the first list, items unique to the second list, and items common to both. Our goal here was to find all of the new facet properties available in the 2012 CTP1 release, so we want to examine the output contained in the file named SecondNotInFirst.txt.

Here is a list of the facet properties that I found:

A few things stood out to me immediately. First up was the property ‘IsHadrEnabled’ for the Server facet. HADR is a high-availability and disaster recovery service newly available in 2012 (you can read more about HADR here). I like the idea of being able to easily identify a server that has this service enabled and think this facet property will prove useful for many in time.

The next thing was the addition of the Sequence facet. A Sequence is exactly what it sounds like: a sequence of numbers. These numbers are created in memory, but only the last number is ever stored in memory so there is no storage on disk required. You can read more about Sequences here. I like the idea of Sequences and I think they are a great addition to SQL Server. And I love the fact that I can build policies based upon the Sequence facet as well. This way I can better monitor my environment to account for these objects.

The last thing that stood out to me was the ServerRole facet. Beginning with 2012 users are going to be able to create their own defined server level roles. You can read more about that here. I have lost track of the number of times I have thought to myself “wow, I really wish I could create my own server role”. With 2012, my wishes will come true!

Next Steps

• For more information about Policy Based Management read these tips
• Stay tuned for future tips about SQL Server 2012