Tuesday, May 10, 2016

How to find all SQL Server instances on your network?

How do you discover all of the different SQL Server instances on your network?  We'd like to think we know our inventory, and are managing it well, but... Different developers, different software installations, or just different days of the week.  :-)  There are often new instances popping up on the network.  I believe this is called 'SQL Server Sprawl', where large numbers of SQL Server instances are installed in the domain, many of which are not needed, not managed... and very often not licensed. But that's another story.

Anyway, how do you discover all of the different SQL Server instances on the network?  SQL Ping, the MSFT Assessment and Planning Toolkit, Powershell, OSQL, and many more.  I'm wiling to bet this post is one of the fastest and easiest ways to do the deed.

Open a CMD prompt, maneuver to whatever directory you choose, and type the following:

         SQLCMD -L >SQLservers.txt

         Press ENTER.

That's it.  Seconds later (depending on how big your network is), you've output all discovered SQL Server instances on your network to the targeted .txt file.  

Seriously. 











It's that easy.




Short and sweet... leaving you all kinds of time to cleanup that SQL Server Sprawl.

Take a look at this for more details on the sqlcmd utility:
    https://msdn.microsoft.com/en-us/library/ms162773.aspx






4 comments:

  1. That's a nice way to find out all the instances, even the ones that have had their default ports reconfigured. There is one caveat though - if those servers are given addl DNS names, then duplicates might turn up (one with the actual servername, and then again with the DNS name(s)). Otherwise, it's neat!

    -az

    ReplyDelete
  2. If you don"t mind proceed with this extraordinary work and I anticipate a greater amount of your magnificent blog entries.  Digital Seva

    ReplyDelete
    Replies
    1. Yes, of course! Sometimes I lag, but I have several pending posts... soon as I can!

      Delete