How to get list of SQL servers on the network

Kranthi DBA 221 Reputation points
2020-12-16T14:59:35.507+00:00

I want to know the list of SQL services installed on the network.

Note: On some of the servers browser service is disabled state due to which I am unable to get information using the powershell scripts.

Please advise if there is any work around which I can use to overcome this issue.

Thanks in advance!

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,814 questions
0 comments
Accepted answer
  1. Ben Miller (DBAduck) 951 Reputation points
    2020-12-16T16:46:54.623+00:00

    There are a couple of ways to do this.

    One of the most effective is to use the MAP toolkit from Microsoft. 1640.microsoft-assessment-and-planning-map-toolkit-getting-started.aspx

    This allows you to get all the services that are on all machines in the Active Directory list of computers. This requires Domain Admin privileges, but it will even find instances of SQL that are offline.

    One other quick and dirty ways is to use sqlcmd. If you use sqlcmd -L and it will do it's best to find them.

    And another is to use PowerShell and have a list of servers. The command is Get-Service -ComputerName @(comma separated list of computers) -name mssql* . This requires a little more knowledge of the computers, but it is effective as long as you have Domain Admin privileges or at least admin on the machine so that you can get at the services.

    I will tell you that the MAP toolkit (Microsoft Assessment and Planning toolkit) is the most comprehensive because it can scan the computers list in AD and get to every machine that is not turned off and get you a list of the services on that server without regard to it being online.

    1 person found this answer helpful.

2 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Cris Zhan-MSFT 6,606 Reputation points
    2020-12-17T08:44:18.007+00:00

    Hi KranthiDBA-1529,

    Consider that not all servers will always enable the Browser service. I think the Microsoft Assessment and Planning(MAP) toolkit is the most suitable.

    Check the article to use Microsoft Assessment and Planning (MAP) Toolkit. Generate report will be able to view detailed information about SQL Server Assessment and the Database details.
    https://www.sqlshack.com/how-to-use-microsoft-assessment-and-planning-map-toolkit-for-sql-server/

    0 comments
  2. Olaf Helper 41,006 Reputation points
    2020-12-17T08:51:55.937+00:00

    Example for an advanced PowerShell script to fetch the informations: Create Inventory of SQL Server in Network as Html