Find All SQL Instances Using PowerShell Plus A Little 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.

image

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.

image

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).

image

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

image

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

image

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

About Boe Prox

Microsoft PowerShell MVP working as a Senior Systems Administrator
This entry was posted in powershell, scripts and tagged , , , . Bookmark the permalink.

17 Responses to Find All SQL Instances Using PowerShell Plus A Little More

  1. Pingback: Updated Function: Get-SQLInstance | Learn Powershell | Achieve More

  2. Saikiran Paramkusham says:

    Hi Boe, Where is authentication being done? If I have to use different credentials, how do i go about it?

  3. Francis says:

    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!

  4. Bruce says:

    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

  5. jay says:

    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.

          .DESCRIPTION
              Retrieves SQL server information from a local or remote servers. Pulls all 
              instances from a SQL server and detects if in a cluster or not.
      
          .PARAMETER Computername
              Local or remote systems to query for SQL information.
      
          .NOTES
              Name: Get-SQLInstance
              Author: Boe Prox
              DateCreated: 07 SEPT 2013
      
          .EXAMPLE
              Get-SQLInstance -Computername DC1
      
              SQLInstance   : MSSQLSERVER
              Version       : 10.0.1600.22
              isCluster     : False
              Computername  : DC1
              FullName      : DC1
              isClusterNode : False
              Edition       : Enterprise Edition
              ClusterName   : 
              ClusterNodes  : {}
              Caption       : SQL Server 2008
      
              SQLInstance   : MINASTIRITH
              Version       : 10.0.1600.22
              isCluster     : False
              Computername  : DC1
              FullName      : DC1\MINASTIRITH
              isClusterNode : False
              Edition       : Enterprise Edition
              ClusterName   : 
              ClusterNodes  : {}
              Caption       : SQL Server 2008
      
              Description
              -----------
              Retrieves the SQL information from DC1
      #>
      [cmdletbinding()] 
      Param (
          [parameter(ValueFromPipeline=$True,ValueFromPipelineByPropertyName=$True)]
          [Alias('__Server','DNSHostName','IPAddress')]
          [string[]]$ComputerName = $env:COMPUTERNAME
      ) 
      Process {
          ForEach ($Computer in $Computername) {
              $Computer = $computer -replace '(.*?)\..+','$1'
              Write-Verbose ("Checking {0}" -f $Computer)
              Try { 
                  $reg = [Microsoft.Win32.RegistryKey]::OpenRemoteBaseKey('LocalMachine', $Computer) 
                  $baseKeys = "SOFTWARE\\Microsoft\\Microsoft SQL Server",
                  "SOFTWARE\\Wow6432Node\\Microsoft\\Microsoft SQL Server"
                  If ($reg.OpenSubKey($basekeys[0])) {
                      $regPath = $basekeys[0]
                  } ElseIf ($reg.OpenSubKey($basekeys[1])) {
                      $regPath = $basekeys[1]
                  } Else {
                      Continue
                  }
                  $regKey= $reg.OpenSubKey("$regPath")
                  If ($regKey.GetSubKeyNames() -contains "Instance Names") {
                      $regKey= $reg.OpenSubKey("$regpath\\Instance Names\\SQL" ) 
                      $instances = @($regkey.GetValueNames())
                  } ElseIf ($regKey.GetValueNames() -contains 'InstalledInstances') {
                      $isCluster = $False
                      $instances = $regKey.GetValue('InstalledInstances')
                  } Else {
                      Continue
                  }
                  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("$regpath\\$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
                          }
                          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'
                          }
                          New-Object PSObject -Property @{
                              Computername = $Computer
                              SQLInstance = $instance
                              Edition = $edition
                              Version = $version
                              Caption = {Switch -Regex ($version) {
                                  "^14" {'SQL Server 2014';Break}
                                  "^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)
              }  
          }   
      }
      

      }

      $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’

  6. steve s says:

    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”.

  7. Crazy DBA says:

    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

  8. Pingback: Why PowerShell? | rambling cookie monster

  9. Pingback: Managing Multi-Instance Cluster Failovers - SQL Server - SQL Server - Toad World

  10. Pingback: Managing Multi-Instance Cluster Failovers | SQL Hammer

  11. Petr says:

    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

  12. Pingback: Connecting to a SQL Server Using SQL Server Management Objects (SMO) and PowerShell | Learn Powershell | Achieve More

  13. sqlchow says:

    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).

    • Boe Prox says:

      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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s