Finding lost or forgotten SQL Servers

  • SQLHeap

    Hall of Fame

    Points: 3502

    Comments posted to this topic are about the item Finding lost or forgotten SQL Servers

    There is an exception to every rule, except this one...

  • a648

    SSC Veteran

    Points: 281

    I'd have started with a cmd window and

    sqlcmd -L

    but that's an interesting technique, thanks 🙂

  • AverageDBA

    Grasshopper

    Points: 24

    a648 (4/19/2011)


    I'd have started with a cmd window and

    sqlcmd -L

    but that's an interesting technique, thanks 🙂

    But this article is about finding instances regardless of whether SQL Browser is running...

  • Geoff A

    SSChampion

    Points: 11407

    nice article. I like the ability to schedule the scan with the agent. Will be very helpful @ my current employer. Thanks.

  • michael.eklof

    SSC Rookie

    Points: 30

    Great example, I will try it out, since I am also interested in findning computers where SQL Server is installed but stopped.

    However, since you are using 'NET VIEW' to list computers, you will only see the computers on the same subnet as the computer you are running the script, right? (At least that is the way it works for me, I don't know if it is a configuration issue with broadcasting, I get the same result with SQLCMD -L). A way to get computers from separate subnets would be to query the AD, for example like this:

    http://blogs.technet.com/b/heyscriptingguy/archive/2006/11/09/how-can-i-use-windows-powershell-to-get-a-list-of-all-my-computers.aspx

  • magasvs

    SSCertifiable

    Points: 7659

    I have been using SQLPing for years (http://sqlsecurity.com/Tools/FreeTools/tabid/65/Default.aspx), but this method is also interesting.

  • SQLHeap

    Hall of Fame

    Points: 3502

    The last script builds a job that executes every Monday at 9am 🙂

    There is an exception to every rule, except this one...

  • Ignacio A. Salom Rangel

    SSC-Insane

    Points: 20443

    I like the script. The one thing I don't like is that you also get computers that have services like the "SQL Server VSS Writer".

  • SQLHeap

    Hall of Fame

    Points: 3502

    @Michael.eklof -- This script pulls in all server names across all domains for me, please let me know if this is not the case for you.

    There is an exception to every rule, except this one...

  • Ignacio A. Salom Rangel

    SSC-Insane

    Points: 20443

    What Michael said is true, Servers on another VLAN will not be included.

  • Craig A. Silvis

    Ten Centuries

    Points: 1038

    I tried sql ping for a while, the command line version, but it takes forever because it needs to look at each IP.

    It returns a nice report of what version and all but it started to fail for some reason so I gave it up.

    I also found that using the sqlcmd -L is problematic in that it only reports on servers on the subnet that you run it on.

    My current solution is to use powershell against AD and it runs very fast so I can run it each night and compare the results against my table of previously known sql servers.

    I did some testing of turning sql services off on a test server and the powershell script did not report the server if the service was off.

    This is probably not as important as finding a new one that someone installed but forgot to tell you about.

  • SQLHeap

    Hall of Fame

    Points: 3502

    Haven't considered VLAN's, thanks.

    There is an exception to every rule, except this one...

  • Ignacio A. Salom Rangel

    SSC-Insane

    Points: 20443

    Maybe you can add it on your next script 😉

  • TheChrisMan

    Mr or Mrs. 500

    Points: 511

    I second the motion on SQLPing. I've never had it fail in my enterprise of 130+ SQL Servers. It's slower because it uses several methods of finding a SQL Server, whether it's started or not.

  • K. Brian Kelley

    SSC Guru

    Points: 114465

    I prefer the use of SQLPing (especially command-line) or the Quest Discovery Wizard (which can also be used command-line as it licenses components of SQLPing). The reason it can take longer is it uses all known methods of discovering SQL Servers:

    - UDP request against port 1434

    - TCP request against port 1433

    - TCP request against port 2433

    - Registry scan

    - SCP scan

    So if your account doesn't have rights to the system being scanned, or they've done something to attempt to hide the computer, SQLPing may still pick it up when a typical PowerShell script won't. Another option is to use nMap, which does fingerprinting and can identify SQL Servers hiding on non-standard ports (or SQL named instances running on dynamic ports) where the user account being used doesn't have appropriate rights.

    K. Brian Kelley
    @kbriankelley

Viewing 15 posts - 1 through 15 (of 58 total)

You must be logged in to reply to this topic. Login to reply