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
Learn Powershell | Achieve More 
I have a box which is running with SQL 2000 and SQL2005 instance. In this case
following both conditions are writening true. In this case,I am unable to list default instance of SQL 2000 because first loop is returning true.
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
}
I would like to return only the instance name of server and fetch the list server name from a table
I have retrieved list of servers from the Invoke-SQLCMD, I would like to pass this result set to get instances installed.
Pingback: Updated Function: Get-SQLInstance | Learn Powershell | Achieve More
Hi Boe, Where is authentication being done? If I have to use different credentials, how do i go about it?
The authentication is done with the user currently running the PowerShell session.
Great post. Thank you! One slight issue I noticed. The SQL 2014 version number is actually “12” not “14”. Other than that, it works like a charm!
Thanks! I will have to get that fixed!
Unfortunately, the script that is available for download does not work. I get the following error when running the script in the PS ISE. Please advise on what the code fix for this error is and when the script will be updated to reflect that change. I d/l’d the script a few minutes ago, so this error is current and outstanding.
Thank you in advance for your time on this.
Function : The term ‘Function’ is not recognized as the name of a cmdlet, function, script file, or operable program.
Check the spelling of the name, or if a path was included, verify that the path is correct and try again.
At line:1 char:1
+ Function Get-SQLInstance {
+ ~~~~~~~~~
+ CategoryInfo : ObjectNotFound: (Function:String) [], CommandNotFoundException
+ FullyQualifiedErrorId : CommandNotFoundException
When I run this code “Get-SQLInstance” under the same directory where it is located, I get no results. Dont know what is causing this, can anyone help me ??
It’s because he has a slight typo in the download link version… the call on this page is correct though. The whole thing, assuming that you don’t want to do a dot source for the function would be the following…just open the PS ISE and paste all of the following in and run it…
Function Get-SQLInstance {
<#
.SYNOPSIS
Retrieves SQL server information from a local or remote servers.
}
$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’
FYI – in the case of clustered servers, you need to modify the code to use the cluster virtual server name rather than the physical (node) name when you create “fullname”.
I worked on a similar solution to collect Windows and SQL Server inventory by gathering scripts from your posts and other SQL Family and released it as a free tool at http://crazydba.com. The tool uses powershell to grab data from all servers and stores it in SQL tables and can be viewed using SSRS reports. Can you please check it and give me any suggestions to improve it?
Thnx
Pingback: Why PowerShell? | rambling cookie monster
Pingback: Managing Multi-Instance Cluster Failovers - SQL Server - SQL Server - Toad World
Pingback: Managing Multi-Instance Cluster Failovers | SQL Hammer
Hi Boe, thanks in advance … Could you send me tip how to get SQL information from main servers list for example from txt file via your script? I have more domains and servers and I need to do it report only from SQL servers. I am not familier with powershell yet. Thank you Petr
Pingback: Connecting to a SQL Server Using SQL Server Management Objects (SMO) and PowerShell | Learn Powershell | Achieve More
Great post! I have something similar, but not this exhaustive though. Also,this is the first time I have seen InvokeReturnAsIs(). One small thing is that, I used Get-RemoteRegistryKey from Lee Holme’s PowerShell CookBook to get the values from registry 🙂 ; the is available on PoshCode (http://poshcode.org/2163).
Thanks! I decided I wanted something a little more extensive.
I am familiar with Lee’s function but opted to not use it in this case. The InvokeReturnAsIs() is something I have used before in a scriptblock due to the way the object was being returned.