Results 1 to 15 of 15
  1. #1
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54

    Unanswered: Enumerate SQL Servers

    I'm looking for a tool or library that has a rock-solid-reliable way to enumerate EVERY SQL Server running in a given TCP/IP address range. Most of the servers will run SQL 7 to SQL 2008. A significant portion of the servers will run on Windows machines that belong to one Active Directory domain, although they'll be in multiple trees. A significant majority of them will be running either a default instance (port 1433) or a named instance that can be located via port 1434, but I can't depend on that because I know at least a few will not be configured that way.

    I only have to find the SQL Servers that can be accessed via the network, although enumerating non-network accessible servers (such as appliance servers outside the domain and configured to only allow Shared Memory access) would be a nice bonus.

    I don't know of any tool that will find all cases reliably, and only a few tools that will even get 98% of these servers. Can anyone suggest where I might find this (possibly mythical) beastie???

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    woooooooooooooooooooosssssshhhhhhhh
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Provided that you have sufficient permissions in the domain, you could do something like this:

    for /f %i in (file_with_ip_addresses) do @echo IP=%i&@sc \\%i query mssqlserver | find "SERVICE_NAME"

    It will print "SERVICE_NAME: mssqlserver" after each IP that has the service running.
    ---
    "It does not work" is not a valid problem statement.

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Actually, this:

    for /f %i in (file_with_ip_addresses) do @echo IP=%i&@sc \\%i query | find "SQL Server"

    will accommodate named instances and show their names.
    ---
    "It does not work" is not a valid problem statement.

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    OK then, where exactly do you do that?


    And are you going to the game Thursday night?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Not sure that will find named instances. Unfortunately, the FIND command does not accept wildcards, so SQL Server and MSSQLServer may have to be two different queries.

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Too many of these servers are outside of the domain. If I could use Active Directory, then the query would be relatively simple and I could just set up a linked server and do everything that I need from within SQL Server.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  8. #8
    Join Date
    Nov 2005
    Posts
    122
    SQL H2 uses remote registry to find SQL Server instances for a server. Copy/paste the remote registry code and wrap it in your application. Keep in mind that it requires the user running the application to have access to open the registry.

  9. #9
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by MCrowley View Post
    Not sure that will find named instances. Unfortunately, the FIND command does not accept wildcards, so SQL Server and MSSQLServer may have to be two different queries.
    It should. The service description looks like "SQL Server (<instance name>)". Instance name would be MSSQLSERVER for the default instance. In other words, if you look for "SQL Server" you should see all instances.
    ---
    "It does not work" is not a valid problem statement.

  10. #10
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Some of the machines that aren't part of the domain are development. Some are physical, some are virtual (mix of VMWare and Microsoft Virtual Server).

    The hard cases are the "appliance" machines that run SQL, but are not part of the domain. Someone here has administrative access to the appliances, but there is no common login and no domain trust established that would permit simple registry queries.

    I'm not at all sure that there is an elegant solution to this problem, I'm just "fishing" before I resort to brute force.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  11. #11
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    I have to say, N_I's solution is very nice. I may have to play around with it here for a bit. I would have resorted to some sort of WMI script to search each machine.

  12. #12
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by Pat Phelan View Post
    Too many of these servers are outside of the domain.
    They don't have to be in the domain. All you need is sufficient permissions to access the service controller remotely.
    ---
    "It does not work" is not a valid problem statement.

  13. #13
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by n_i View Post
    All you need is sufficient permissions to access the service controller remotely.
    While that is theoretically possible at some specific point in the future if I have enough time to make sure that I get everything set up correctly and account for every machine, it isn't a practical solution for routine use. It is looking more and more like I'll have to create a "roll-your-own" solution for this specific environment instead of a more general solution, but that is pretty much what I expected that I'd have to do.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  14. #14
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    I see only two ways of identifying SQL Server instances. The most reliable method is to detect the actual service registered on each machine, for which you obviously need some kind of remote access on that machine. Another method would be to scan each TCP/IP port on each server and try to understand whether something speak SQL Serverese on that port; this method is obviously subject to firewall restrictions, SQL Server versions and service packs, and the fact that some instances may not be running or listening to local connections only.

    You could try nmap to see how many instances it can detect, if any.
    Last edited by n_i; 12-02-09 at 18:33. Reason: Fixed typo: "nmap", not "nmon"
    ---
    "It does not work" is not a valid problem statement.

  15. #15
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Quote Originally Posted by MCrowley View Post
    Not sure that will find named instances. Unfortunately, the FIND command does not accept wildcards, so SQL Server and MSSQLServer may have to be two different queries.
    Windows 2000 and higher provides the FINDSTR command that can handle regular expressions.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •