Search code examples
sql-serverpowershellwmi

PowerShell SMO ManagedComputer.ServerInstances Collection is Empty

I am trying to enumerate SQL Server instances using the ManagedComputer.ServerInstances collection:

[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlWmiManagement")  
$ManagedComputer = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer
$ManagedComputer

The expected output would be something similar to the following:

ConnectionSettings : Microsoft.SqlServer.Management.Smo.Wmi.WmiConnectionInfo
Services           : {MSSQLSERVER, SQLBrowser...}
ClientProtocols    : {np, sm, tcp}
ServerInstances    : {MSSQLSERVER}
...

For one particular host I manage the ServerInstances collection is empty:

ConnectionSettings : Microsoft.SqlServer.Management.Smo.Wmi.WmiConnectionInfo
Services           : {MSSQLSERVER, SQLBrowser...}
ClientProtocols    : {np, sm, tcp}
ServerInstances    : {}
...

A 64-bit PowerShell v5 session is used. Default SQL Server 2016 instances are installed across the fleet. What could be causing the ServerInstances collection to be empty?

There are some cases where SQL Server Management Studio 2017 is installed on the host. Could this potentially have an effect on querying the ServerInstance collection?

Solution

  • After a call with Microsoft support, they confirmed that if SQL Server Management Studio 2017 is installed alongside SQL Server 2016 the WMI assemblies can get corrupted time to time. Even after running mofcomp to recompile the WMI repository the issue persists.

    This issue was resolved after uninstalling SSMS 2017 and installing the latest version.