Learn Powershell | Achieve More

A while back I saw this the following post on the PowerShell Magazine that showed how to get all of the instances of a SQL server. This was pretty cool, but only listed the instances and nothing else (well it can return a boolean value if you choose to search for a specific instance). I wanted more out of this ability to locate instances and decided to dive deeper into the registry rabbit hole and came back up with a wealth of information that will turn this into a super-charged function capable of being used in your domain to find out some great information about your SQL server environment!

As specified in the PowerShell Magazine article, you can find the available instance names (SQL 2005 and above) at the following key: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL.

The next trick is to look at the data value that lies within the instance name, in this case MSSQL10.SHIRE and use that to locate its key under the SQL key. I am mostly concerned with what is in the Setup key and what the edition is. I know that there is a Version key,but this is not as accurate as what you can get with the actual file for the sqlservr.exe file. More on that in a minute.

Now we can take a look at the SQL version that is running for this particular instance. Using the instance name, you can locate the service under SYSTEM\CurrentControlSet\Services and find the SQL service running the instance and locate the path to the file (imagepath).

Those are pretty much the basics of what I will doing in my version of Get-SQLInstance. I have also added a couple of other things such as the ability to determine if this particular SQL server is part of  a cluster (I don’t have a cluster to show the examples, but you will see in the code how it works), find its cluster name and the other nodes in the cluster. What I decided not to do is to allow an option to filter out the nodes and only show the actual cluster itself. It is pretty simple to do this in a script that I will show later on.

$Computer = $computer -replace '(.*?)\..+','$1'
Write-Verbose ("Checking {0}" -f $Computer)
Try { 
    $reg = [Microsoft.Win32.RegistryKey]::OpenRemoteBaseKey('LocalMachine', $Computer) 
    $regKey= $reg.OpenSubKey("SOFTWARE\\Microsoft\\Microsoft SQL Server")

I start out by only using the hostnames of the systems by making sure there are no fully qualified domain names here (my own cosmetic choice) and then attempting the remote registry connection to the system and connecting to the registry path hosting the SQL server information.

If ($regKey.GetSubKeyNames() -contains "Instance Names") {
    $regKey= $reg.OpenSubKey("SOFTWARE\\Microsoft\\Microsoft SQL Server\\Instance Names\\SQL" ) 
    $instances = @($regkey.GetValueNames())
} ElseIf ($regKey.GetValueNames() -contains 'InstalledInstances') {
    $isCluster = $False
    $instances = $regKey.GetValue('InstalledInstances')
} Else {
    Continue
}

Here I am looking for the Instance Names key to determine if this is actually a SQL Server (note that I am also looking for InstalledInstances which is a SQL 2000 key), otherwise this is assumed to not be a SQL Server.

If ($instances.count -gt 0) { 
    ForEach ($instance in $instances) {
        $nodes = New-Object System.Collections.Arraylist
        $clusterName = $Null
        $isCluster = $False
        $instanceValue = $regKey.GetValue($instance)
        $instanceReg = $reg.OpenSubKey("SOFTWARE\\Microsoft\\Microsoft SQL Server\\$instanceValue")
        If ($instanceReg.GetSubKeyNames() -contains "Cluster") {
            $isCluster = $True
            $instanceRegCluster = $instanceReg.OpenSubKey('Cluster')
            $clusterName = $instanceRegCluster.GetValue('ClusterName')
            $clusterReg = $reg.OpenSubKey("Cluster\\Nodes")                            
            $clusterReg.GetSubKeyNames() | ForEach {
                $null = $nodes.Add($clusterReg.OpenSubKey($_).GetValue('NodeName'))
            }
        }
        $instanceRegSetup = $instanceReg.OpenSubKey("Setup")
        Try {
            $edition = $instanceRegSetup.GetValue('Edition')
        } Catch {
            $edition = $Null
        }

A lot of things happening here. If I find at least one instance, the function will continue to gather more information about the SQL server. One of the next checks is to make the determination about whether this is a SQL cluster or not. If it is a cluster, then it will check the Cluster key under the root of Software and get the cluster name and all of the cluster nodes. The edition of the SQL server is also located as well in this code snippet.

Try {
    $ErrorActionPreference = 'Stop'
    #Get from filename to determine version
    $servicesReg = $reg.OpenSubKey("SYSTEM\\CurrentControlSet\\Services")
    $serviceKey = $servicesReg.GetSubKeyNames() | Where {
        $_ -match "$instance"
    } | Select -First 1
    $service = $servicesReg.OpenSubKey($serviceKey).GetValue('ImagePath')
    $file = $service -replace '^.*(\w:\\.*\\sqlservr.exe).*','$1'
    $version = (Get-Item ("\\$Computer\$($file -replace ":","$")")).VersionInfo.ProductVersion
} Catch {
    #Use potentially less accurate version from registry
    $Version = $instanceRegSetup.GetValue('Version')
} Finally {
    $ErrorActionPreference = 'Continue'
}

Up next is the file version gathering (more accurate than looking in the registry) by locating the proper service for the instance and then performing a little cleanup on the image path courtesy of some regex. Once we have the path, it is just a matter of getting the file version information from the file and moving forward.

                        New-Object PSObject -Property @{
                            Computername = $Computer
                            SQLInstance = $instance
                            Edition = $edition
                            Version = $version
                            Caption = {Switch -Regex ($version) {
                                "^11" {'SQL Server 2012';Break}
                                "^10\.5" {'SQL Server 2008 R2';Break}
                                "^10" {'SQL Server 2008';Break}
                                "^9"  {'SQL Server 2005';Break}
                                "^8"  {'SQL Server 2000';Break}
                                Default {'Unknown'}
                            }}.InvokeReturnAsIs()
                            isCluster = $isCluster
                            isClusterNode = ($nodes -contains $Computer)
                            ClusterName = $clusterName
                            ClusterNodes = ($nodes -ne $Computer)
                            FullName = {
                                If ($Instance -eq 'MSSQLSERVER') {
                                    $Computer
                                } Else {
                                    "$($Computer)\$($instance)"
                                }
                            }.InvokeReturnAsIs()
                        }
                    }
                }
            } Catch { 
                Write-Warning ("{0}: {1}" -f $Computer,$_.Exception.Message)
            }  
        }   
    }
}

The rest of the code finishes up by outputting the object that lists all of the information gathered previously. The only other things is that it adds a boolean value if the cluster is in a node and also gets the type of SQL server being used (SQL 2008, etc…). An additional thing is the FullName property which gives a ‘fullname’ of the SQL instance that can be used in a script for a SQL connection using something like SMO.

So with that, it is time to take a little look at this function in action. First lets just look at my own local system. Remember that this is a function so the script needs to be dot sourced first!

Get-SQLInstance

Here you can tell that what version I am running as well as the type of SQL server that is being used. Now we can take this a step further by checking out what exists on my remote server.

Get-SQLInstance -Computername DC1

Here you can tell that I actually have 2 instances installed on DC1. Both are the same version and type of SQL server.

If you remember , I mentioned something about filtering out the cluster nodes and only showing the cluster server itself. Here is what I have been using when I attempt to locate those systems.

Get-SQLInstance -ComputerName $Computername -Verbose | ForEach {
    If ($_.isClusterNode) {
        If (($list -notcontains $_.Clustername)) {
            Get-SQLInstance -ComputerName $_.ClusterName
            $list += ,$_.ClusterName
        }
    } Else {
        $_
    }
}

If I had a clustered server, then it would only show the cluster name and would not show the nodes in the cluster.

Personally, I use this to scan the active directory domain for reporting purposes using the following code that provides a simple CSV with the SQL information:

$Computername = Get-ADComputer -Filter {operatingSystem -Like '*server*'} | Select -Expand DNSHostName
Get-SQLInstance -ComputerName $Computername -Verbose | ForEach {
    If ($_.isClusterNode) {
        If (($list -notcontains $_.Clustername)) {
            Get-SQLInstance -ComputerName $_.ClusterName
            $list += ,$_.ClusterName
        }
    } Else {
        $_
    }
} | Export-Csv -NoTypeInformation 'SQLServers.csv'

The download for this function is below. Please let me know what you think of it! I am sure improvements can be made to this and will definitely do so if needed.

Download Get-SQLInstance

Get-SQLInstance.ps1