List edition and version of all SQL Server machines over the Network ...

  • shamshad.ali

    Hall of Fame

    Points: 3850

    In our organization, we have more than 100 SQL Server machines on different regions over the Network/LAN/WAN. We have restricted the network to do not go out of subnet/your allowed access until permitted explicitly.
    As an administrative task, I need to check which version and edition of SQL Servers are running over the entire network within the domain.
    We have few different domains but I am OK with each domain to query explicitly with no issue. Is there any way to iterate all of the network and generate report to see machine IP, ServerName, Edition as well as version (Service Pack etc.) using powershell or any free tool.

    I used following to check such report, but is restricted within the subnet from where I am running the query ...

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

    https://www.mssqltips.com/sqlservertip/2013/find-sql-server-instances-across-your-network-using-windows-powershell/

    Regards,

  • Thom A

    SSC Guru

    Points: 98219

    The other problem that that is that you do need the SQL Browser service to be running. I assume, therefore, you don't at least have any documentation of the servers running SQL Server and their instance names, at least? Thus, effectively, you're saying that you have no idea what hosts are running SQL Server, and (if they are) what the name of the instance(s) it's running is? If none of them are running the browser service, then this does get harder; especially when they aren't using the default name and/or port.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.

  • shamshad.ali

    Hall of Fame

    Points: 3850

    Assuming the browser service is running, because it is started by default when SQL Server is installed normally on any machine. If any one else know how to bring the required details (free) with script without login to any remote machine, please share ...
    I searched few and they are not successful giving me the output... 

    1- Following do not work on network- only works well with local machine ...
    https://devblogs.microsoft.com/scripting/use-powershell-to-discover-diagnose-and-document-sql-server/

    2- Following is outdated and I do not see any proper details about the given information ... links are not working and the script is not working as well ...
    https://archive.codeplex.com/?p=sqlpowerdoc

    Please see if one can help ...

  • Jeff Moden

    SSC Guru

    Points: 993924

    I am by no means a Windows Ninja but it seems to me that the Domain Controller would be aware of all machines on the network.  Perhaps interrogating the Domain Controller would be more effective?

    As a bit of a sidebar, we consider having the browser service enabled to be a security risk and turn it off on all of our machines, so anything that uses that relies on that service for a "discovery process" will simply produce no results.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems

  • HappyGeek

    SSCoach

    Points: 18657

    Microsoft have, or at least had, a discovery tool which was designed to evaluate a network for licence true up purposes, it is (was?) a free tool, haven't used it for some years. If you have cross domain trusts set up and sufficient rights, that should do it.
    Be prepared, if it still works, to be perplexed at the number of other SQL services ande servers thatt arer disvovered on user PC's, printers, and various other places you don't expect it

    ...

  • Sue_H

    SSC Guru

    Points: 89905

    HappyGeek - Thursday, February 28, 2019 9:11 AM

    Microsoft have, or at least had, a discovery tool which was designed to evaluate a network for licence true up purposes, it is (was?) a free tool, haven't used it for some years. If you have cross domain trusts set up and sufficient rights, that should do it.
    Be prepared, if it still works, to be perplexed at the number of other SQL services ande servers thatt arer disvovered on user PC's, printers, and various other places you don't expect it

    Good thought. I've used MAP for the same and it's still around, being updated, etc:
    Microsoft Assessment and Planning Toolkit

    Sue

  • Lowell

    SSC Guru

    Points: 323354

    Active directory will have a list of all servers, which you could then use a WMI query to query services,and get the names of each instance on the server...
    then you could connect ot each instance and get the versions info.....

    but that's the long way around the tree.
    Have you created a Central Management server and placed all the instances in there? that is one of my core tools to track all my servers, as i can query all servers in a given folder with the same query.

    here's something to get you started with the Active Directory Scan:
    [ import-module ActiveDirectory
    $DomainDNS = [System.DirectoryServices.ActiveDirectory.Domain]::GetCurrentDomain().Name #Get AD Domain (lightweight & fast method)
      [array] $AllSQLServers = Get-ADComputer -filter { (OperatingSystem -like "*Windows*")
                     -and (OperatingSystem -like "*Server*")
                     -and (Enabled -eq $True) } -properties Name, ServicePrincipalNames, DistinguishedName, OperatingSystem, passwordLastSet
      get-service -name * -computername $AllSQLServers | format-table -property MachineName, Status, Name, DisplayName -auto
      $AllSQLServers | Out-GridView
    ########################
    # Discover SQL Servers? #
    ########################

       Write-Verbose "AutoDiscover mode set to AD. The script will query AD for registered SPNs and their associated server names. `r"
      
        get-service -name MSSQL* -computername $AllSQLServers | format-table -property MachineName, Status, Name, DisplayName -auto
      
      
       $AllSQLServersCount = $AllSQLServers.Count
       Write-Output "There were $AllSQLServersCount SQL Servers discovered in $DomainDNS … `r"
       get-service -name MSSQL* -computername $AllSQLServers | format-table -property MachineName, Status, Name, DisplayName -auto
       get-service -filter { name -like "*MSSQL*" -or name -like "*DTS*" name -like "*OLAP*" name -like "*REPORT*"} -computername $AllSQLServers | format-table -property MachineName, Status, Name, DisplayName -auto

       Write-Verbose "Compiling Server List `r"
       ForEach ($Computer in $AllSQLServers)
         { ## OPEN ForEach
          Get-Service MSSQL* -ComputerName $Computer.Name | format-table -property MachineName, Status, Name, DisplayName -auto
          --ps
         } ## CLOSE ForEach
     

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Viewing 7 posts - 1 through 7 (of 7 total)

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