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

Question

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

shamshad.ali

Hall of Fame

Points: 3850
More actions
February 27, 2019 at 4:00 am

#409553

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,

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

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

Thom A SSC Guru Points: 99126 More actions · Answer 1

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.
Larnu.uk

shamshad.ali Hall of Fame Points: 3850 More actions · Answer 2

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: 1003863 More actions · Answer 3

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

HappyGeek SSCoach Points: 18726 More actions · Answer 4

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: 90860 More actions · Answer 5

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: 323504 More actions · Answer 6

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!