a new command to find all of your sql instances

Posted on by Chrissy LeMaire

Nearly every time I inherit a SQL Server environment, I’m only given a partial list of SQL Servers that exist on the network. It’s my usual routine to get permission to sniff the network then run about five different programs including Idera’s SQL Discovery and Microsoft’s SQL Server Assessment and Planning Toolkit.

I always thought it’d be cool to have one comprehensive PowerShell command that could do the work of all the above and was ecstatic to see NetSPI’s Scott Sutherland had written a few commands to do just that in his awesome PowerShell module PowerUpSQL.

PowerUpSQL

Find-DbaInstance

When I saw Scott’s multi-pronged approach (including some UDP magic 🎩), I asked if he’d be interested in contributing to dbatools and he said yes! He submitted a gorgeous mock-up and I was so excited. Then came the PR, complete with great documentation and multithreading.

I asked our architect Fred Weinmann to perform a code review and he was so taken by the command, he refactored it with some C# magic including strong types. This command, which is available in 0.9.314, is a beauty!

The basics

This command searches for SQL Server Instances. It supports a variety of scans for this purpose which can be separated in two categories: Discovery and Scan.

DiscoveryType

This is where it compiles a list of computers / addresses to check. It supports any combination of:

Domain

Connects to Active Directory to look up all computers with registered SQL Instances. Not all instances need to be registered properly, so this is not 100% reliable. By default, your nearest Domain Controller is contacted for this scan, but you can specify its DistinguishedName and the -DomainController parameter for greater control. If credentials were specified using the -Credential parameter, those same credentials are used to perform this lookup, allowing the scan of other domains.

DataSourceEnumeration

This uses the default UDP Broadcast based instance enumeration used by SSMS to detect instances. Note that the result from this is not used in the actual scan, but only to compile a list of computers to scan. To enable the same results for the scan, ensure that the -Browser scan is enabled.

IPRange

This discovery uses a range of IPAddresses and simply passes them on to be tested. By default, it will enumerate all ethernet network adapters on the local computer and scan the entire subnet they are on. By using the -IpAddress parameter, custom network ranges can be specified. Check out the help for more information on range formats.

ScanType

Once a list of computers has been provided, this command will execute a variety of actions to determine any instances present for each of them.

DNSResolve

  • Tries resolving the computername in DNS

Ping

  • Tries pinging the computer. Failure will NOT terminate scans.

SQLService

  • Tries listing all SQL Services using CIM/WMI
  • This scan uses credentials specified in the -Credential parameter if any.
  • This scan detects instances.
  • Success in this scan guarantees high confidence (See parameter -MinimumConfidence for details).

Browser

  • Tries discovering all instances via the browser service
  • This scan detects instances.

TCPPort

  • Tries connecting to the TCP Ports.
  • By default, port 1433 is connected to.
  • The parameter -TCPPort can be used to provide a list of port numbers to scan.
  • This scan detects possible instances. Since other services might bind to a given port, this is not the most reliable test.
  • This scan is also used to validate found SPNs if both scans are used in combination

SqlConnect

  • Tries to establish a SQL connection to the server
  • Uses windows credentials by default
  • Specify custom credentials using the -SqlCredential parameter
  • This scan is not used by default
  • Success in this scan guarantees high confidence (See parameter -MinimumConfidence for details).

SPN

  • Tries looking up the Service Principal Names for each instance
  • Will use the nearest Domain Controller by default
  • Target a specific domain controller using the -DomainController parameter
  • If using the -DomainController parameter, use the -Credential parameter to specify the credentials used to connect

Example usage

Here are just a few usage examples.

Using Active Directory

Get-ADComputer -Filter { name -like 'sql*' } | Find-DbaInstance | Out-GridView

the 0 ports are null ints

Scans all computers named like sql% in the domain for SQL Instances, using a deep probe:

  • Tries resolving the name in DNS
  • Tries pinging the computer
  • Tries listing all SQL Services using CIM/WMI
  • Tries discovering all instances via the browser service
  • Tries connecting to the default TCP Port (1433)
  • Tries connecting to the TCP port of each discovered instance
  • Tries to establish a SQL connection to the server using default windows credentials
  • Tries looking up the Service Principal Names for each instance

And for more detailed information, you can use Select * to expose all properties, including those hidden by default.

Get-ADComputer -Filter { name -like 'sql*' } | Find-DbaInstance | Select *

SPN and auto-discovery

Find-DbaInstance -DiscoveryType Domain, DataSourceEnumeration

Performs a network search for SQL Instances by:

  • Looking up the Service Principal Names of computers in active directory
  • Using the UDP broadcast based auto-discovery of SSMS
  • After that it will extensively scan all hosts thus discovered for instances

Servers from file

Get-Content .\servers.txt | Find-DbaInstance -ScanType Browser, SqlConnect -Credential (Get-Credential ad\winadmin) -SqlCredential ad\sqladmin

  • Reads all servers from the servers.txt file (one server per line)
  • Scans each of them for instances using the browser service using the ad\winadmin account
  • Attempts to connect to each instance found using the ad\sqladmin account

Do everything

Warning! This one takes a long time due to the IP scan. How long? About 2 hours on my single subnet lab.

Find-DbaInstance -DiscoveryType All -ScanType All

Performs a network search for SQL Instances, using all discovery protocols and scan types:

  • Active directory search for Service Principal Names
  • SQL Instance Enumeration (same as SSMS does)
  • All IPAddresses in the current computer’s subnets of all connected network interfaces
  • Tries resolving the name in DNS
  • Tries pinging the computer
  • Tries listing all SQL Services using CIM/WMI
  • Tries discovering all instances via the browser service
  • Tries connecting to the default TCP Port (1433)
  • Tries connecting to the TCP port of each discovered instance
  • Tries to establish a SQL connection to the server using default windows credentials
  • Tries looking up the Service Principal Names for each instance

Want to know more? Check out Get-Help Find-DbaInstance -Detailed. This includes additional information and discusses parameters like -SqlCredential, -MinimumConfidence and more.

Thank you so very much for sharing your hard work, Scott! I’ve wanted a command like this for years. And thank you, Fred, for the time and love you invested into the dbatools refactor.

- Chrissy

15 thoughts on “a new command to find all of your sql instances”

  1. Pingback: Finding SQL Server Instances With dbatools – Curated SQL

  2. Pingback: Attacking Application Specific SQL Server Instances

  3. Jim Reply

    When I try to run any of the above scripts I get…

    Unable to find type [DbaInstance].
    At C:\WINDOWS\system32\WindowsPowerShell\v1.0\Modules\Find-DBAInstance\Find-DBAInstance.psm1:185 char:9
    + [DbaInstance[]]$ComputerName,
    + ~~~~~~~~~~~~~~~
    + CategoryInfo : InvalidOperation: (DbaInstance:TypeName) [], RuntimeException
    + FullyQualifiedErrorId : TypeNotFound

    • Chrissy LeMaire Post authorReply

      hey jim, what is find-dbainstance\find-dbainstance.psm1? that is not our module. please update to the latest version of dbatools and try again.

  4. Chris J. Reply

    Hi Chrissy,

    I am running the “Give me everything from everywhere!” command,

    Find-DbaInstance -DiscoveryType All -ScanType All

    I do believe this is working as the script has been running for more than 5 hours now. The issue is that we have a /13 in our environment and its going to take FOREVER to finish scanning that entire range. Is it possible to limit this command to a segmented server subnet, for example a /24. As all my servers should live on this segmented subnet. This would serve to expedite the scanning process. Secondly, does this particular command work when using the pipe or perhaps piping to a CSV file.

    | Out-GridView

    I truly appreciate your time and teams work into this project. Thank you and have a great day!

    • Jaime Reply

      | Out-File -FilePath c:\oracle\sqlservers.txt works fine for me

  6. Anne Reply

    Hi Chrissy,

    I’m at a new client and I ran the command Find-DbaInstance -DiscoveryType All -ScanType All and it gave us a lot of sql server instances back, only not all. What could be the cause of that?
    For one server it is an always-on cluster, but I don’t see the cluster, and also not de two nodes back in the list.
    But now some backups failed, and I am checking if these servers are on the list, but no.
    I can connect to the servers through ssms, so they are up and running.

    Kind regards, Anne

  7. Anne Reply

    Hi anyone,

    I would like to store the list of servers into a sql table. What is the simplest way to this. I’m looking for something like this, but then write it to a sql table in stead of a csv file:
    Find-DbaInstance -DiscoveryType All -ScanType All | Export-Csv -Path “C:\TEMP\servers.csv”

    Thnx in advance!

  8. Zman Reply

    This is a very helpful module but the one thing that is missing is pulling the version and if its clustered.
    Combining find-dbainstance and get-slqinstance would be magical. I’m not a PowerShell guru so if you would be so inclined to point me in the direction of taking the output of find-dbainstance and getting that into get-sqlinstance to grab the sql versions it would be super helpful.

  9. Zman Reply

    Performing some testing with get-sqlinstance and it would appear that the isClustered option does not appear to detect AoAG clustering. I have nearly 20 AoAG clusters and every node comes back as IsClustered = False.

    It also appears that SQL 2016 and 2019 are not detected properly, the Caption for the SQL version returns Unknown. I updated dbatools using the self update command prior to my testing. Thoughts?

    • Chrissy LeMaire Post authorReply

      IsClustered is only about Failover Clustered Instances, not Availability Groups. For Availability Groups, use Get-DbaAgHadr 👍

  10. Scott Reply

    Not getting too far here starting from the beginning… thanks for your efforts Chrissy.

    PS C:\Windows\system32> Get-ADComputer -Filter { name -like ‘sql*’ } | Find-DbaInstance | Out-GridView
    Find-DbaInstance : The term ‘Find-DbaInstance’ 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 th
    try again.
    At line:1 char:48
    + … t-ADComputer -Filter { name -like ‘sql*’ } | Find-DbaInstance | Out-G …
    + ~~~~~~~~~~~~~~~~
    + CategoryInfo : ObjectNotFound: (Find-DbaInstance:String) [], CommandNotFoundException
    + FullyQualifiedErrorId : CommandNotFoundException

    PS C:\Windows\system32> Find-DbaInstance -DiscoveryType All -ScanType All
    Find-DbaInstance : The term ‘Find-DbaInstance’ 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 th
    try again.
    At line:1 char:1
    + Find-DbaInstance -DiscoveryType All -ScanType All
    + ~~~~~~~~~~~~~~~~
    + CategoryInfo : ObjectNotFound: (Find-DbaInstance:String) [], CommandNotFoundException
    + FullyQualifiedErrorId : CommandNotFoundException

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.