How to get list of SQL servers on the network

Question

How to get list of SQL servers on the network

Kranthi DBA 191

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!

Answer 1

Ben Miller (DBAduck) 951 MVP

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.

Kranthi DBA 191 Reputation points

2020-12-17T16:19:26.303+00:00

Hi Ben,

Thanks for the reply.

I am trying to get the information from our of our inventory server which is in the same domain as other servers. Unfortunately, we aren't allowed to install any of the softwares on that server. I am asked to use some powershell script to get the list of servers that are part of the domain and I don't know the list of the servers. Is there a way by which I can get the list of all of the servers in domain? so that I can try using below command once I have the list.

Get-Service -ComputerName @(comma separated list of computers) -name mssql*

Ben Miller (DBAduck) 951 MVP

You can get the list of computers from the ActiveDirectory module (install RSAT tools for Active Directory)

$computers = Get-AdComputer -Filter * | Select -ExpandProperty Name

foreach($computer in $computers) {
Get-Service -ComputerName $computer -Name mssql*
Get-Service -ComputerName $computer -Name msolap*

# you can add other name patterns to get things
}

Answer 2

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/

Answer 3

Olaf Helper 27,211

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

Kranthi DBA 191 Reputation points

2020-12-17T16:21:36.28+00:00

Hi Ola,

Thanks for the reply.

I have tried running the powershell script which executed with the below output.

2020-12-18T00:14:32: 0 server found.
2020-12-18T00:14:32: Started creating Html document.
2020-12-18T00:14:32: Finished

How to get list of SQL servers on the network

2 additional answers

Activity