This function looks up SQL Instance information via the registry on local and remote systems. Information looked up is Version, Edition type, whether the SQL Instance is part of a cluster and the other nodes in the cluster and the full name that can be used in another script to

4.7 Star
22,599 times
Add to favorites
E-mail Twitter Digg Facebook
  • Services -match
    3 Posts | Last post June 14, 2016
    • Awesome job on this Boe! But of course,  I managed to find a edge case "Bug", one of my servers has a service that also matches the instance name, and it checks the file version of of that binary instead of the sqlservr.exe. I'm stumped as to a fix right now, but I think tonight I'll dig around and see if I can come up with something.
    • there's probably a better way, but adjusting this line fixes it in my instance:
      $serviceKey = $servicesReg.GetSubKeyNames() | Where {
      ($_ -match "$instance") -and ($_ -match "MSSQL")
      } | Select -First 1
      Does the SQL Server Service always start with MSSQL$<instancename> ?
    • Thanks! I've updated the code to include this.
  • AlwaysOn name
    2 Posts | Last post June 14, 2016
    • It appears you are testing for SQL Clusters only (pre SQL 2012) or FCI (SQL 2012 onwards). What about Avail Grps? Would be nice if the AG name was also detected and shown.
    • It should be an available property. Are you not seeing it when you run the command?
  • information about CU and/or SP
    2 Posts | Last post January 25, 2016
    • a nice addition would be the information about CU and/or SP.
      The versionnumber translatet to a human readable versioninfo.
    • I agree!
  • SQL Server Express
    5 Posts | Last post July 08, 2015
    • Hi Boe,
      Noticed this doesn't pick up SQL Server Express instances, at least for a few I've spot checked.  Other than that, very helpful command!
    • Weird. I could have sworn that it was working on Express instances in testing. I'll check into this and follow up with a good answer (or updated function). Just out of curiousity, what versions of SQL Express have you seen issues with?
    • I take that back!  Was a Wow6432Node thing.  Tweaked it to look in both locations, seems to be working.
      $baseKeys = "SOFTWARE\\Microsoft\\Microsoft SQL Server",
          "SOFTWARE\\Wow6432Node\\Microsoft\\Microsoft SQL Server"
    • Awesome! Thanks for finding that. To be honest, I completely neglected that location to look at. I will get the function updated with that new location so this will be more accurate for others. Thanks again!
    • For me works only with
      $reg = [Microsoft.Win32.RegistryKey]::OpenRemoteBaseKey('LocalMachine', $Computer, 'Registry64') 
      $baseKeys = "SOFTWARE\\Microsoft\\Microsoft SQL Server",
                      "SOFTWARE\\Wow6432Node\\Microsoft\\Microsoft SQL Server"
  • Doesnt accurately create full SQL instance name for clustered instances
    1 Posts | Last post April 29, 2015
    • Had to rewrite the FullName = block to get it accurately create the SQL instance full name:
      FullName = {
                                      If (!$isCluster)
                                          If ($Instance -eq 'MSSQLSERVER') {
                                          } Else {
                                      Else {
                                          If ($Instance -eq 'MSSQLSERVER') {
                                              } Else {
  • won't run for me.
    1 Posts | Last post February 09, 2015
    • Hey,
      many thanks for going to the trouble of writing this.
      After working out what exactly dot netting a powershell script was.
      (Put a dot and then a space in then the file path to the script, e.g. PS> . C:\Scripts\Get-SqlInstance.ps1) i tried running the script using .\Get-sqlInstance.ps1 but it just goes the next line and doesn't appear to do much, I know of 12 instances of SQL on my network so it should find something, or is there something that needs editing in the script first?! Have a feeling it is assumed that I need to know something else?!
  • List Alias?
    1 Posts | Last post October 03, 2014
    • This is awesome, 
      I was going with winRM to import failovercluster modules etc, but soon learned I'd hit a wall with all the old SQL we have.
      I'm knee deep in SQL 200, 2008, 2008R2, and 2012.
      I'm wondering if there's a hive or other components we can query to get alias names for each instance.
      I can see on clusters HKEY_LOCAL_MACHINE\0.Cluster\Resources\ has various keys with these details in them, but wondering if anyone else expanded for this?
      Or can point me in the right direction.
      Additionally, also curious if it's able to know if the instance is up or down.
  • SQL 2000
    6 Posts | Last post September 18, 2014
    • Does this support sql 2000?  It doesn't seem to identify the edition.  It's just blank.  There may be a logic error in how you are handling instances found in the InstalledInstances value.
    • OK, I think I got it working for SQL 2000.  I modified part of the code in the ForEach($instance in $instances) as follows (the first line and last line are untouched so you can find the position in the original file):
      $isCluster = $False
      if ($regKey.GetValue($instance))
        $instanceValue = $regKey.GetValue($instance)
        $instanceReg = $reg.OpenSubKey("$regpath\\$instanceValue")
        $instanceValue = $instance
        $first = $regPath.LastIndexOf("\\")
        $instanceReg = $reg.OpenSubKey("$($regPath.Substring(0, $first))\\$instanceValue")
      If ($instanceReg.GetSubKeyNames() -contains "Cluster") {
    • What it's doing is if it can't read the value from the $instance value name, it assumes the $instance variable IS the $instanceValue and appends the $instanceValue to the parent key of $regPath instead of to $regPath.
      SQL2000 stored things slightly differently in the registry and it looks like you had most of it there.
    • I've tested this against a number of SQL 2000 servers and didn't have any issues. I'll test your approach and maybe merge it in with the existing code. Thanks!
    • I've discovered that my solution wasn't entirely correct.  It seems that only the default instance for SQL 2000 has the different registry path.  The named instances seem to be in the same spot as the other versions.
    • Also, the part where you are getting the executable file name from the service key:  If they have full text services, you might get the wrong key (eg, msftesql$MSSQLSERVER instead of MSSQL$MSSQLSERVER), which would give you the wrong version
  • SQL 2014
    2 Posts | Last post August 19, 2014
    • Boe - You need to add an additonal line for your RegEx when you create your PSObject
      "^12" {'SQL Server 2014';Break}
      This adds SQL 2014 to audit appropriately.
    • Thanks for the heads up Graeme! I've made the update to include SQL 2014.
  • No result displayed
    2 Posts | Last post August 06, 2014
    • Hi,
      I am running on Windows 2008 R2, and I downloaded the code to a location. Then, I just run as "./GetSQLInstance.ps1 -Computername <Computername>". But, it gives me no output. Although, I have instances running. 
      Can you help please ??
    • Sorry, for this stupid question, I was running in wrong way. 
      Can I get each instance status also whether it is UP or DOWN ??
11 - 20 of 20 Items