Getting SQL Server Version using dbatools

Time to read: ~ 4 minutes

Words: 730

I will discuss different ways to get the SQL Server version using PowerShell.

I’ll explore a function you can use, and how a dictionary/hash table could also work.

Finally, I’ll discuss how neither of these are needed since dbatools/SMO has a better way.

The Initial Issue

I’m sure you’ve needed the version of a SQL Server instance for a report a few times.
And, surprisingly, not many people are up for parsing the output of @@VERSION.

I can’t see why not, it’s perfectly fine. It is not the easiest thing in the world, but it is also not the hardest. But, you do you.

Invoke-DbaQuery -SqlInstance localhost\SQL2019 -Query "SELECT version = @@VERSION;" | FL

My team lead has a function that he uses to get these versions, but it encountered an issue. We recently updated to a later version of SQL Server, and the function stopped working.

$instance = Connect-DbaInstance -SqlInstance localhost\SQL2019

$instance.Version.Major

Get-WhatVersionAmI -Version $instance.Version.Major

It seems to work fine if I pass in an older version of SQL Server.

Get-WhatVersionAmI -Version 14

So, what’s the issue?
I’m going to take a look under the hood of that function, and see if I can spot what’s wrong.

Checking under the function’s hood

If you don’t have the code, or are screen-sharing, you can query the function provider to get the definition of most PowerShell functions.

Get-ChildItem function:\Get-WhatVersionAmI | Select-Object *

Taking a look at the function, here’s the gist of the function block:

param([string] $Version)

    switch ($Version) {
        14 { "SQL Server 2017" }
        13 { "SQL Server 2016" }
        12 { "SQL Server 2014" }
        default { "Unknown" }
    }

This seems simple enough. Hell, it’s just a switch statement that takes the input and spits out the matching output.

Seeing something this simple I’d almost suggest using a dictionary

Using a dictionary instead

This is the equivalent code of the function, but using a hash-table.

$dict = @{
    14 = "SQL Server 2017" 
    13 = "SQL Server 2016" 
    12 = "SQL Server 2014" 
}

$version = $dict[15]
if (!$version) {
    $version = "Unknown"
} 

"Version: $version"

The benefit of this approach is:

• it’s easier to modify. I don’t need to update the function and either dot-source it, e.g. . <path_to_function_file.ps1>, or re-run it in memory to use it. I only need to add an entry to the hash table.

$dict = @{
    15 = "SQL Server 2019" 
    14 = "SQL Server 2017" 
    13 = "SQL Server 2016" 
    12 = "SQL Server 2014" 
}

$version = $dict[15]
if (!$version) {
    $version = "Unknown"
} 

"Version: $version"

The problem with this approach is:

• I have to keep it updated, and I’ll find it difficult if I have to add it to multiple scripts.

Shift left?

The above approaches have common issues. They have to be ported to every script I have, run into memory, be in sync, etc., etc.

Can I do something about that? Can this be shifted left somehow? Can I remove some of the work that I’ve to do? In the words of Homer Simpson “can’t someone else do it?”.

Why yes, yes they can.

Using dbatools

When dbatools connects to a SQL Instance, the usually returned object is a Microsoft.SqlServer.Management.Smo.Server, and that object has a few methods. The one I’m interested in is GetSqlServerVersionName.

Connect-DbaInstance -SqlInstance localhost\SQL2019 | Get-Member -MemberType Method -Name GetSql*

Now, I can connect to an instance, and query what version it is without any external functions or scripts:

Connect-DbaInstance

$instance = Connect-DbaInstance -SqlInstance localhost\SQL2019
$Instance.GetSqlServerVersionName()

Hey, the function is even there on Microsoft.SqlServer.Management.Smo.Database from Get-DbaDatabase, in case I get the strange notion that the version changes from database to database.

Get-DbaDatabase

$databases = Get-DbaDatabase -SqlInstance localhost\SQL2019
$databases.GetSqlServerVersionName()

I don’t think they change, by the way.

End

I’m a dbatools fan. I’m also slightly lazy, which is why I like automation so much.

Now that I know that there’s a built-in way to get a proper human readable version of an instance. You better believe I’m using that!