Finding SQL Servers with PowerShell

You may also like...

No Responses

  1. Ted Krueger says:

    As always, nice post, Aaron!
    I set up 4 runs on 4 different servers and 1 run on a Windows 7 machine.
    My executions would vary from returning 70 to 75 instances. That is missing several that I ruled out due to different network configurations and I expected it not to find them. The execution from my local machine would often return the least amount which was odd. That was Window 7 which should have no difference to Windows 2008 Server (R2 or not).
    I only had one instance where two different runs came back with identical results (that is out of 17 executions. Not good odds). Named instances did seem to be an issue. I could only get a return from 4 named instances and those were SQL Express instances so they (kind of) count. When I narrowed down the named instance issue, or so I thought I did, then one would pop up all the sudden.
    Not much to report beyond what has been said. The inconsistency is/was hard to track down. There was no apparent reasoning at all why an instance would drop from the list and then shows back up from the machine it was executed from. So the statement that it is unreliable on catching all instances and normal behavior is very accurate.

  2. AJ Mendo says:

    Aaron,

    Nice post but I ran across an issue you may want to check.

    I tested this script and it only returns the server name I an on. If I start SQL Browser, it then returns additional servers (SQL Express) and returns the Instance, IsClustered, and Version info.

    I then stopped SQL Browser and only my server name info returned. I am running Win XP SP3, so not sure if there is a difference running it from other OS versions.

    By default SQL Browser is set to disabled, if I remember correctly. But I will check the script this week at work. We do have a separate domain for developers to do what they want. Then we control Pre-Prod and Prod so it will be interesting to see what I can find.

    Thanks,
    AJ
    SQLAJ

    • Aaron Nelson says:

      AJ,
      Having multiple instances on my machine it looks like I’m having the same result, ironically, my default instance is only show up when the browser is off howerever this behavior is exactly the same for me in SSMS as noted at the beginning of the post. I did notice that they only showed up under “Network Servers” and not at all under “Local Servers” which I thought was a bit odd.

      SQL Browser is always enabled when you have a named instances. Here’s some more info on it.

      http://msdn.microsoft.com/en-us/library/ms181087.aspx
      Background
      ——————————————————————————–
      Prior to SQL Server 2000, only one instance of SQL Server could be installed on a computer. SQL Server listened for incoming requests on port 1433, assigned to SQL Server by the official Internet Assigned Numbers Authority (IANA). Only one instance of SQL Server can use a port, so when SQL Server 2000 introduced support for multiple instances of SQL Server, SQL Server Resolution Protocol (SSRP) was developed to listen on UDP port 1434. This listener service responded to client requests with the names of the installed instances, and the ports or named pipes used by the instance. To resolve limitations of the SSRP system, SQL Server 2005 introduced the SQL Server Browser service as a replacement for SSRP.

      How SQL Server Browser Works
      ——————————————————————————–
      When an instance of SQL Server starts, if the TCP/IP or VIA protocols are enabled for SQL Server, the server is assigned a TCP/IP port. If the named pipes protocol is enabled, SQL Server listens on a specific named pipe. This port, or “pipe,” is used by that specific instance to exchange data with client applications. During installation, TCP port 1433 and pipe \sql\query are assigned to the default instance, but those can be changed later by the server administrator using SQL Server Configuration Manager. Because only one instance of SQL Server can use a port or pipe, different port numbers and pipe names are assigned for named instances, including SQL Server Express. By default, when enabled, both named instances and SQL Server Express are configured to use dynamic ports, that is, an available port is assigned when SQL Server starts. If you want, a specific port can be assigned to an instance of SQL Server. When connecting, clients can specify a specific port; but if the port is dynamically assigned, the port number can change anytime SQL Server is restarted, so the correct port number is unknown to the client.

      Upon startup, SQL Server Browser starts and claims UDP port 1434. SQL Server Browser reads the registry, identifies all instances of SQL Server on the computer, and notes the ports and named pipes that they use. When a server has two or more network cards, SQL Server Browser returns the first enabled port it encounters for SQL Server. SQL Server Browser support ipv6 and ipv4.

      When SQL Server clients request SQL Server resources, the client network library sends a UDP message to the server using port 1434. SQL Server Browser responds with the TCP/IP port or named pipe of the requested instance. The network library on the client application then completes the connection by sending a request to the server using the port or named pipe of the desired instance.

  3. Peter says:

    And just verified, unless we have some way to look at all of the subnets to which we have access, this seems to work about as well as SSMS. Your code looks good. It would be nice to have some other method to look at other subnets or otherwise figure out how SQLPing does its job and mimic that.

    • Aaron Nelson says:

      Peter,
      I’m working on a way that you can specify a list of computers to poll and it’s progressing but there are definitely some kinks to it. I will see what I can do to streamline it.

  4. Peter says:

    Out of curiosity, what should we do if we’re running on non-standard ports? I found all of the local instances of SQL Server on our network, but didn’t find any of the actual servers. I guess I should mention that this “find all servers” functionality has always seemed lacking to me, regardless of how it’s called. 🙂

    Giving a quick test of SQLPing to see if it behaves the same way – it’s been ages since I’ve run that tool. Glad not to be the “admin” now and just a dev.

    • Aaron Nelson says:

      Peter,
      I’ll have to look into the other ports thing. I do have a PowerShell script around here somewhere to tell you what port your instance is running on; not sure if there would be a way to leverage that or not but I will see.

  5. Nice find. I did notice one disclaimer in the MSDN docs – Due to the nature of the mechanism used by SqlDataSourceEnumerator to locate data sources on a network, the method will not always return a complete list of the available servers, and the list might not be the same on every call.

    So, depending on the context and the state of machines on the network, your mileage may vary.

    Regardless, it is a great tool for the toolbelt.

    • Aaron Nelson says:

      Steve,
      Thanks for pointing out that it’s a documented behavior of SqlDataSourceEnumerator. I’m glad that you like what I put together, I’m hoping to show a few more ways to do this in the coming weeks (somewhere in my vast amounts of spare time).

  1. 2010.09.24

    […] This post was mentioned on Twitter by MSFT Scripting Guys, ravikanth, Jason Coombes, Jim Poshible, Eric Travers and others. Eric Travers said: RT @ravikanth: RT @SQLvariant: New post: Finding SQL Servers with PowerShell http://bit.ly/8Xg1Qf #SQLPASS […]

Leave a Reply

%d bloggers like this: