Finding Servers On Your Network

by

I have seen more than one blog post written about how you can use sqlcmd /L to find the SQL Servers on your network. I have seen it mentioned so many times, in fact, that I started to believe it myself. I mean, why would I doubt such statements, especially when I have never used /L to find servers…until today.

While troubleshooting an issue today wih a vendor I asked how they were populating a list of servers. After some back and forth I finally decided to compare their list to the list returned by using /L, just for shits and giggles. The two lists were identical and about an hour later the vendor confirmed for me that they were using /L. Of course I was not surprised. And it is not unreasonable for them to use that method. Well, except for two things.

First, it doesn’t work. And second, it doesn’t work.

Why do I say it doesn’t work? Well, go ahead and tr it for yourself. Go on, I will wait. Meanwhile, here is what the BOL says about /L:

Lists the locally configured server computers, and the names of the server computers that are broadcasting on the network.

If you have SQL Servers on more than one segment of your network, then /L will not work; it will only return the SQL Servers broadcasting on your current segment, which is what I will assume they mean by using the word ‘locally’. In my case I get a list of about 22 instances which is just shy of the 210 instances I was expecting. Oh, and one of those servers listed is not even running SQL Server. Well, okay, technically it is running SQL Server, but the more common name is Sybase ASE.

And no, using OSQL /L (nor it’s cousin, ISQL) did not solve the issue, but feel free to try those as well.

I know that other methods do exist and work well because I see them almost daily. Inside of SSMS, for example (I will assume SMO and/or POSH at work there). Or inside some vendor tools. Or some free tools from Microsoft. Or Operations Manager. There seems to be a lot of different ways to get the job done right.

If you have been relying on /L to get a list of instances on your network, you should rethink your strategy.

15 thoughts on “Finding Servers On Your Network”

  2. If it wasn’t completely free I wouldn’t plug it, but Quest has the Discovery Wizard for SQL Server that works similarly to SCOM and the MAP tool and is very effective at finding all the SQL Instances on your network.

    http://www.quest.com/discovery-wizard-for-sql-server/

    Its a lot less involved than a SCOM setup or MAP configuration, which makes it very useful in consulting work, or when taking over a new environment and you want to know all of the instances on the network.

    Reply

    • nice Mike…hijacking my blog post with a link to your own…i see Brent has taught you well. your skills are now complete.

      Reply

  4. Go ahead and kill the link, I can’t edit my post. I don’t care 🙂 The really goal is to give a flying thumbs up for MAP and show folks how to use it. Take the screen shots and post it as you own 🙂 I don’t get anything special when folks visit the blog, no ads yet. 😉

    Reply

    • no way, young jedi. your comment will stay. if i can redirect both of my readers to your stuff, that’s fine with me.

      Reply

  6. SMO or Powershell is just as inaccurate as sqlcmd /L. You should see the same results as sqlcmd /L. Try it yourself, here’s two methods. The first uses SMO and second uses System.Data

    [reflection.assembly]::LoadWithPartialName(“Microsoft.SqlServer.Smo”)

    [Microsoft.SqlServer.Management.Smo.SmoApplication]::EnumAvailableSqlServers()

    ([System.Data.Sql.SqlDataSourceEnumerator]::Instance).GetDataSources()

    Reply
  8. Pingback: QuickBlog: Build a list of SQL Server without SMO… | Max Trinidad – The PowerShell Front

Leave a Comment

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