Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

New Facets and Properties in SQL Server 2012


By:   |   Last Updated: 2011-02-17   |   Comments (1)   |   Related Tips: 1 | 2 | 3 | 4 | More > Policy Based Management

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:

using powershell to compare pbm facets in sql 2008 r2 and the denali cpt1 release

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:

New Facets and Properties in SQL Server 2012

Facet name Property
Database AvailabilityGroupName
ContainmentType
NestedTriggersEnabled
TransformNoiseWords
TwoDigitYearCutoff
FullTextIndex SearchPropertyListName
Login PasswordHashAlgorithm
SearchPropertyList DateCreated
DateLastModified
ID
Owner
Name
Sequence CacheSize
CreateDate
DateLastModified
ID
IsCycleEnabled
IsExhausted
IsSchemaOwned
Owner
SequenceCacheType
Schema
Name
Server IsHadrEnabled
IServerConfigurationFacet ContainmentEnabled
IServerInformation IsHadrEnabled
ServerRole ID
DateCreated
DateModified
IsFixedRole
Owner
Name
User AuthenticationType

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


Last Updated: 2011-02-17


next webcast button


next tip button



About the author
MSSQLTips author Thomas LaRock Thomas LaRock is a Head Geek at SolarWinds and a Microsoft Certified Master, Microsoft Data Platform MVP, VMware vExpert, and a former Microsoft Certified Trainer with over 20 years’ experience.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Monday, August 06, 2012 - 7:39:46 AM - Hanumathkumar Back To Top

Good Post its very helpful for us.


Learn more about SQL Server tools