SQL Server instance discovery using SqlServer PowerShell module

Question

The dbatools module's Find-DbaInstance can accept a computer name and return a list of instances on that machine. Is there an equivalent in the SqlServer module that does this? I tried Get-SqlInstance, but it seems to need actual instance names, as opposed to being able to use it for instance discovery. I'd prefer to use dbatools, but the SqlServer module is what I have consistent access to in the current environment.

Which type of scan do you want: WMI or SQL Browser or a specific TCP port or SPN lookup? — Charlieface
– Charlieface, Commented May 30, 2022 at 20:53
@Charlieface, WMI seems like it would be the most reliable (Browser might be disabled, the port may be something other than 1433, etc.). I am attempting to find every last 'rogue' install of SQL Server as those unknown instances are the most vulnerable (you can't patch them or retire them if you don't even know they exist). — Alex Pixley
– Alex Pixley, Commented May 31, 2022 at 13:27
Not sure about WMI, maybe look through the code github.com/dataplat/dbatools/blob/development/functions/… Is there any reason you don't want to install DBA Tools, it's pretty easy — Charlieface
– Charlieface, Commented May 31, 2022 at 15:02
Using dbatools is the ultimate goal, but the rollout is only partially complete and our security software (after an upgrade) has suddenly started labeling dbatools as 'potentially malicious' and blocks it from running, so until that's remedied, it's not an option. — Alex Pixley
– Alex Pixley, Commented May 31, 2022 at 17:46

Otter · Accepted Answer · 2022-05-30 21:03:17Z

1

There are many good results that dont even depend on the sqlserver module in this article

eg.

$SQLInstances = Invoke-Command -ComputerName "localhost" {
    (Get-ItemProperty 'HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server').InstalledInstances
}

foreach ($sql in $SQLInstances) {
    [PSCustomObject]@{
        ServerName = $sql.PSComputerName
        InstanceName = $sql
    }
}

answered May 30, 2022 at 21:03

Otter

1,1729 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Alex Pixley Over a year ago

Based on this link, I have avoided the method you suggest. I am trying to find every last 'rogue' install of SQL Server as those unknown instances are the most vulnerable (you can't patch them or retire them if you don't even know they exist).

Otter Over a year ago

Have a look at the section labelled Get-Service cmdlet in the link I mentioned, that uses WMI to some extent, happy to edit my answer if that is your preferred method?

Alex Pixley Over a year ago

I worked through the different methods mentioned in the article. I was surprised at how well using Get-ChildItem addressed this problem and a couple of others I've been dealing with. For what I'm doing, It's even better than SMO.

Collectives™ on Stack Overflow

SQL Server instance discovery using SqlServer PowerShell module

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related