PowerShell Snippet #7: Retrieve SQL Server Licence Key

Copied from somewhere else on the internet, this PowerShell script will return the product key used for a SQL instance Install. Super useful when changing licenses on temporary VM’s I spin up and play around with to SQL Developer whose instances have passed the Enterprise evaluation use-by date. Putting this here for my own benefit. I claim no kudos!


function GetSqlServerProductKey {
## function to retrieve the license key of a SQL 2014 Server.
param ($targets = ".")
$hklm = 2147483650
$regPath = "SOFTWARE\Microsoft\Microsoft SQL Server\120\Tools\Setup"
$regValue1 = "DigitalProductId"
$regValue2 = "PatchLevel"
$regValue3 = "Edition"
Foreach ($target in $targets) {
$productKey = $null
$win32os = $null
$wmi = [WMIClass]"\\$target\root\default:stdRegProv"
$data = $wmi.GetBinaryValue($hklm,$regPath,$regValue1)
[string]$SQLver = $wmi.GetstringValue($hklm,$regPath,$regValue2).svalue
[string]$SQLedition = $wmi.GetstringValue($hklm,$regPath,$regValue3).svalue
$binArray = ($data.uValue)[0..16]
$charsArray = "B","C","D","F","G","H","J","K","M","P","Q","R","T","V","W","X","Y","2","3","4","6","7","8","9"
## decrypt base24 encoded binary data
For ($i = 24; $i -ge 0; $i--) {
$k = 0
For ($j = 14; $j -ge 0; $j--) {
$k = $k * 256 -bxor $binArray[$j]
$binArray[$j] = [math]::truncate($k / 24)
$k = $k % 24
$productKey = $charsArray[$k] + $productKey
If (($i % 5 -eq 0) -and ($i -ne 0)) {
$productKey = "-" + $productKey
$win32os = Get-WmiObject Win32_OperatingSystem -computer $target
$obj = New-Object Object
$obj | Add-Member Noteproperty Computer -value $target
$obj | Add-Member Noteproperty OSCaption -value $win32os.Caption
$obj | Add-Member Noteproperty OSArch -value $win32os.OSArchitecture
$obj | Add-Member Noteproperty SQLver -value $SQLver
$obj | Add-Member Noteproperty SQLedition -value $SQLedition
$obj | Add-Member Noteproperty ProductKey -value $productkey


SQL Server 2014 Sample Databases Now Available

SQL Server 2014 has been available for some time now, yet until now we have not had any sample databases available. I say until now because on August 27th Microsoft released the AdventureWorks 2014 sample databases, available on Codeplex.

Read up on Jimmy May’s blog on how they (eventually) got to us.

Compressing Open Row Groups

If you’re using, or at least read up on Clustered Columnstore Indexes then you’ll know that the data is stored in segments. You’ll also know that there are 4 states the segments can be in at any one time:

  1. OPEN

When segments are in the OPEN/CLOSED states they are delta-stores. Delta stores are immediately up-datable and are currently uncompressed. So at this stage the delta stores will not have the space saving advantages of columnstore. The db engine creates a new delta store whenever it needs one to handle inserts, closes them when full (have 1048576 rows). OPEN and CLOSED delta stores can be directly updated and deleted, unlike COMPRESSED segments (see below). You can have more than one OPEN delta store at any one time. Continue reading “Compressing Open Row Groups”

How To Split Non-Empty Partitions When A Clustered Columnstore Index Exists on The Table

Another pithily titled blog post, but this one revisits the feature of SQL Server 2014 that piqued my interest the most: Clustered Columnstore Indexes. I’ve been working with Clustered Columnstore Indexes for the past few months and recently a colleague had to split a non empty partition, only for the error message below to appear.

Msg 35346, Level 15, State 1, Line 390
SPLIT clause of ALTER PARTITION statement failed because the partition is not empty. Only empty partitions can be split in when a columnstore index exists on the table. Consider disabling the columnstore index before issuing the ALTER PARTITION statement, then rebuilding the columnstore index after ALTER PARTITION is complete.

Ok, so it looks like we cannot split non empty partitions with a Clustered Columnstore Index on the table, or at least an enabled clustered columnstore index. So the topic for todays post is to find out how can we split a non empty partition when a clustered columnstore index exists on the table. We can create a test database/tables/Clustered Columnstore Indexes and disable the indexes before we split to see if that is the solution, as suggested in the error that it might be. Let’s start at the beginning using the database I created for my previous post on SQL Server 2014 Clustered Columnstore Indexes and Partitioning. The script below will create the database and the objects necessary. I’m going to state the obvious, but read through the scripts first before you run them; things like file locations on boxes are always different: Continue reading “How To Split Non-Empty Partitions When A Clustered Columnstore Index Exists on The Table”

Microsoft’s Lousy Response to a Connect Issue

I’ve been going through the process of upgrading from SQL Server 2012 to SQL Server 2014 and I’ve hit a bug when running snapshots. Any snapshot that was in place from SQL Server 2012 cannot be dropped in SQL Server 2014. There’s a Connect article already open. Continue reading “Microsoft’s Lousy Response to a Connect Issue”

SQL Server 2014 Clustered Columnstore Indexes and Partitioning

(If you attended my talk at SQL Supper in January 2016 and want a copy of my scripts, then this post here has the full details)

One of the features of SQL 2014 which got less column inches (pun intended) than Hekaton/ in memory OLTP but was of more interest to me was the clustered columnstore indexes, or CCI for brevity. Continue reading “SQL Server 2014 Clustered Columnstore Indexes and Partitioning”

Cumulative Updates – SQL Server 2014 CU1

As part of their Incremental Service Modelling, no sooner have they released SQL 2014 RTM, Microsoft have now released SQL Server 2014 Cumulative Update #1.

Cumulative Update 1 includes all hotfixes which were released in SQL Server 2012 SP1 CU 6, 7, 8, and 9.

The build number of the cumulative update package is 12.0.2342.0. You may have to restart the computer after you apply this cumulative update package.

This CU contains 111-121 fixes, depending on your sources.

A Few Things I Didn’t Know Last Week

  1. That it is possible to have a Victorian house built in the 1930’s: I’m in the process of buying a new house, and one I saw listed was Victorian terraced. When I viewed the property I wasn’t too surethat it was Victoarian so I asked the agent when this property was built. “1930’s.” was the response. “Oh, I read on the listing it was Victorian” I replied, hoping for further clarity. “Yea, yea.” was the agents clarification…
  2. That the beginning of a book can be the end, and the end be not in the book, and the last few pages of a book be the middle: I finally finished Infinite Jest, a book I’d been promising to read for about two years. For me, the effort involved outweighed the enjoyment I got from reading the book. To contradict that, I want to read it again, but after reading some of the texts cited in the reviews of the book. I don’t want to post spoilers (trust me, what I’ve written in bold above does not reveal anything), but I’ve come away from the book wanting to read reviews, articles etc on the book, and it’s challenged me, so it;s certainly had an impact on me, in a good way.
  3. That it is possible to have other indexes on tables that have Clustered Columnstore Indexes on it: but you need to consider all the implications.

    This isn’t a knock on Brent’s advice, it’s a clever solution and I’m sure he’d expect people to go check their work before adding indexed views on CCI tables. When testing this solution I found the indexed views to be more than twice the size of the compressed table, so it’s not as if the solution is without it’s disadvantages which Brent goes over at the end of the post.
  4. That it’s possible to bite off more than you can chew: I failed the 70-462 exam last Friday. What with moving house, reading literary (and literally) heavyweight books, spending time with the baby, and learning about Clustered Columnstore Indexes, revision time was at a minimum. It’s not the first time I have failed an exam, it won’t be the last, and I know where I messed up: lack of revision.  So time to book my second shot and get revising. I got my arse kicked by the exam and I didn’t like it, so I’m going to work that much harder to pass this exam.

Clustered Columnstore Index Catalog Views Split By Partition

If you are using partitions with the new Clustered Columnstore Indexes then it’s helpful to know the number of dictionaries/segments each partition has. This is because partitions have their dictionaries and segments isolated so that switching of partition is still kept as a meta data task only. Below are two queries for checking the catalog views for both dictionaries and segments and grouping them by partition

SELECT i.name, p.object_id, p.index_id, i.type_desc, p.partition_number,
COUNT(*) AS number_of_segments
FROM sys.column_store_segments AS s
INNER JOIN sys.partitions AS p
ON s.hobt_id = p.hobt_id
INNER JOIN sys.indexes AS i
ON p.object_id = i.object_id
WHERE i.type = 5
GROUP BY i.name, p.object_id, p.index_id, i.type_desc, partition_number ;

SELECT i.name, p.object_id, p.index_id, i.type_desc, p.partition_number,
COUNT(*) AS number_of_dictionaries
FROM sys.column_store_dictionaries AS s
INNER JOIN sys.partitions AS p
ON s.hobt_id = p.hobt_id
INNER JOIN sys.indexes AS i
ON p.object_id = i.object_id
WHERE i.type = 5
GROUP BY i.name, p.object_id, p.index_id, i.type_desc, p.partition_number