Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer sometimes empty when used remotly

[andreasjordan]

I use the SMO version included in dbatools, GetType().Assembly shows version v4.0.30319.

I use this code from a management server with dbatools to a access a server with the SQL Server instance:

$wmi = [Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer]::new('SQLCN01')
$wmi.Initialize()
$wmi.ServerInstances.Count
$wmi.ClientProtocols.Count

What I did:

• Deployed a brand new server SQLCN01 with Windows Server 2016
• Installed an instance of SQL Server 2017
• Used the code above:
  • Initialize() returns True.
  • ServerInstances.Count returns 1.
  • ClientProtocols.Count returns 3.
  • So everything as expected...
• Installed an instance of SQL Server 2019
• Used the code above:
  • Initialize() returns True.
  • ServerInstances.Count returns 2.
  • ClientProtocols.Count returns 3.
  • So everything as expected...
• Uninstalled the instance of SQL Server 2019
• Used the code above:
  • Initialize() returns True.
  • ServerInstances.Count returns nothing.
  • ClientProtocols.Count returns nothing.
  • So here I have a problem....

It looks like the connection is targeted to some kind of left over version 2019 endpoint which is not able to get me the information.
As soon as I install a version 2019 instance again, the information is back there.

Any ideas?

[potatoqualitee]

Agreed, I've experienced these issues for years, over several versions of SMO. It's so unpredictable, our internal command has to try locally then remotely, as you know. I'd love to see a solution.

[andreasjordan]

I think I found a solution.

After uninstalling SQL Server 2019, the corresponding WMI namespace "ComputerManagement15" is still present on the computer (Get-WmiObject -Query "Select * From __Namespace Where Name Like 'ComputerManagement%'" -Namespace "root\Microsoft\SQLServer" -ComputerName SQLCN01).

It looks like the remote WMI connection always "connects" to the highest available namespace. After removing the namespace with Get-WmiObject -Query "Select * From __Namespace Where Name='ComputerManagement15'" -Namespace "root\Microsoft\SQLServer" -ComputerName SQLCN01 | Remove-WmiObject the remote WMI connection works again.

So the WMI connection should not only connect to the highest namespace, but also see if this namespace "is filled". And if not, continue with the next smaller namespace.

[shueybubbles]

@Matteo-T is the leftover WMI namespace "by design" after uninstalling SQL?

[andreasjordan]

One additional information: If I connect via WinRM and run the code locally on the target (with $wmi = [Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer]::new()), the WMI object is filled with the correct information. So it looks like different access methods are used.

[potatoqualitee]

So the WMI connection should not only connect to the highest namespace, but also see if this namespace "is filled". And if not, continue with the next smaller namespace.

Not sure exactly how this works, but I recall version mismatches being problematic. It's already a bit slow so I imagine that's the reason each available namespace isn't interrogated. Maybe multithreading can help?

[Matteo-T]

@shueybubbles - setup in this area had not changes in probably a decade, so I'm not surprised if there are bugs.

@potatoqualitee - this is NOT the same issue that (1) is still affecting SQLPS (2) it stopped affecting SQLServer a long time ago. This seems to be just the WMI provider in SQL Server setup that is not unregistering itself properly. I'd have to follow up with the SQL Setup folks and see what we can do about it, at least in the next SQL version.

@andreasjordan - your theory seems reasonable. Considering that, at best this will be addressed in SQL vNext, we may still need/want to put logic into SMO to workaround this possible problem.

[Matteo-T]

As an aside, in the early version of SQLServer per PS6/7, the WMI class in SMO was totally unavailable so I moved away from that stuff altogether. I traded the WMI stuff with remote registry access... which, at least, was available. I also recall some talks around "what about WMI on non-Windows?" but I think the thread kind of died a while back...

[Matteo-T]

@andreasjordan - FWIW, I am not able to repro the original problem you reported both then I use the v150 version of SMO that comes with the SQLServer module and when I created a C# application that uses the latest SMO package (161.47008.0) available on nuget.org. Perhaps, you should have a word with the owners of DBATools :-) or narrow it down to a non-dbatools specific problem (I agree that, in principle, SQL 2019 setup should remove the class/namespace when it is uninstalled, but that's a different story).

[potatoqualitee]

LOL burrrn 🔥

[andreasjordan]

Thanks for trying to repro. In the next free time slot, I will get the nuget package and test.

[Matteo-T]

The key to the repro is a reboot after uninstalling SQL 2019 - at least for me.

So, it would appear that SQL 2019 uninstall is "doing something" with the WMI namespace/classes, but not something that is fully committed until after a reboot happens (maybe a missing forceupdate somewhere?)

[andreasjordan]

Ok, I now rebooted after removing SQL Server 2019 - but the namespace is still there.

Here my code for documentation:

Import-Module -Name dbatools 

$adminCredential = Get-Credential -Message WindowsAdmin -UserName LABDOMAIN\Admin

$installParams = @{
    ComputerName     = 'SQL03'
    Feature          = 'Engine'
    Path             = '\\FS\Software\SQLServer\ISO'
    UpdateSourcePath = '\\FS\Software\SQLServer\CU'
    Restart          = $true
    Credential       = $adminCredential
    Confirm          = $false
}

$removeParams = @{
    ComputerName     = 'SQL03'
    Configuration    = @{ ACTION = 'Uninstall' } 
    Path             = '\\FS\Software\SQLServer\ISO'
    Restart          = $true
    Credential       = $adminCredential
    Confirm          = $false
}

$result = Install-DbaInstance @installParams -Version 2017 -InstanceName SQL2017
$result

$result = Install-DbaInstance @installParams -Version 2019 -InstanceName SQL2019
$result

(Get-WmiObject -Query "Select * From __Namespace Where Name Like 'ComputerManagement%'" -Namespace "root\Microsoft\SQLServer" -ComputerName $installParams.ComputerName).Name
# Both 14 and 15 are present

$wmi = [Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer]::new($installParams.ComputerName)
$wmi.Initialize()
$wmi.ServerInstances.Count
$wmi.ClientProtocols.Count
# WMI object is filled

$result = Install-DbaInstance @removeParams -Version 2019 -InstanceName SQL2019
$result

(Get-WmiObject -Query "Select * From __Namespace Where Name Like 'ComputerManagement%'" -Namespace "root\Microsoft\SQLServer" -ComputerName $installParams.ComputerName).Name
# Both 14 and 15 are still present

$wmi = [Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer]::new($installParams.ComputerName)
$wmi.Initialize()
$wmi.ServerInstances.Count
$wmi.ClientProtocols.Count
# WMI object is not filled anymore

Restart-Computer -ComputerName $installParams.ComputerName

(Get-WmiObject -Query "Select * From __Namespace Where Name Like 'ComputerManagement%'" -Namespace "root\Microsoft\SQLServer" -ComputerName $installParams.ComputerName).Name
# Both 14 and 15 are still present

$wmi = [Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer]::new($installParams.ComputerName)
$wmi.Initialize()
$wmi.ServerInstances.Count
$wmi.ClientProtocols.Count
# WMI object is not filled anymore

Get-WmiObject -Query "Select * From __Namespace Where Name='ComputerManagement15'" -Namespace "root\Microsoft\SQLServer" -ComputerName $installParams.ComputerName | Remove-WmiObject

(Get-WmiObject -Query "Select * From __Namespace Where Name Like 'ComputerManagement%'" -Namespace "root\Microsoft\SQLServer" -ComputerName $installParams.ComputerName).Name
# Only 14 is left

$wmi = [Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer]::new($installParams.ComputerName)
$wmi.Initialize()
$wmi.ServerInstances.Count
$wmi.ClientProtocols.Count
# WMI object is not filled again

Next step is to use last version of SMO from nuget. Will keep you updated.

[Matteo-T]

@andreasjordan - no need for that. It's just a bug in SQL. I have several repros and I've started a conversation with some folks here. As I said, we need to get creative and put some workaround in place in SMO or at least make sure that we provide a dcent way to "complete the uninstall" (the one you have may be "good enough" - I just don't know enough about about WMI and mofcomp to tell if there's a better way to go about it)

[Matteo-T]

@shueybubbles - one way to go about it could be to expose an overload of the ctor that takes the namespace (or the version) and just uses that instead of the heuristic we have today. That would address @potatoqualitee's complain about performance (if the right namespace is at the very end of the list, I can see taking several seconds or minutes for all the requests to timeout).

[andreasjordan]

But we don't know the namespace. And the "filled" namespace has information about all the instances regardless of their version.

[andreasjordan]

After all, I installed a version 2016 instance:

$result = Install-DbaInstance @installParams -Version 2016 -InstanceName SQL2016
$result

(Get-DbaCmObject -Namespace "root\Microsoft\SQLServer\ComputerManagement14" -Query "SELECT * FROM SqlService" -ComputerName $installParams.ComputerName).ServiceName
# Shows all services, from version 2016 and 2017
(Get-DbaCmObject -Namespace "root\Microsoft\SQLServer\ComputerManagement13" -Query "SELECT * FROM SqlService" -ComputerName $installParams.ComputerName).ServiceName
# Shows only services from version 2016

So it looks like this to me: Namespaces for a version without an installed instance are completely empty. Namespaces for a version with an installed instance contain information about all installed instances on the computer. So we need to get the higherst filled namespace.

[andreasjordan]

That is how we get the services:

https://github.com/dataplat/dbatools/blob/324db843ce4bde71959b446150577c6eea4a2c24/functions/Get-DbaService.ps1#L189-L209

            Write-Message -Level Verbose -Message "Getting SQL Server namespaces on $computer" -Target $computer
            try {
                $namespaces = Get-DbaCmObject -ComputerName $resolvedComputerName -Credential $Credential -Namespace root\Microsoft\SQLServer -Query "Select Name FROM __NAMESPACE WHERE Name Like 'ComputerManagement%'" -EnableException | Sort-Object Name -Descending
                Write-Message -Level Verbose -Message "The following namespaces have been found: $($namespaces.Name -join ', ')."
            } catch {
                Write-Message -Level Verbose -Message "No namespaces found in relevant namespace on $computer."
            }


            foreach ($namespace in $namespaces) {
                try {
                    Write-Message -Level Verbose -Message "Getting Cim class SqlService in Namespace $($namespace.Name) on $computer." -Target $computer
                    foreach ($service in (Get-DbaCmObject -ComputerName $resolvedComputerName -Credential $Credential -Namespace "root\Microsoft\SQLServer\$($namespace.Name)" -Query "SELECT * FROM SqlService WHERE $searchClause" -EnableException)) {
                        Write-Message -Level Verbose -Message "Found service $($service.ServiceName) in namespace $($namespace.Name)."
                        $services += $service
                    }
                    # Use highest namespace available, so break if services have been found
                    break
                } catch {
                    Write-Message -Level Verbose -Message "Failed to acquire services from namespace $($namespace.Name)." -Target $Computer -ErrorRecord $_
                }
            }

[andreasjordan]

It looks like this is where all the possible namespaces are iterated to get a connection:

sqlmanagementobjects/src/Microsoft/SqlServer/Management/Smo.Wmi/ManagedComputer.cs

Lines 329 to 346 in 9809360

	foreach(var managementPath in managementPaths)
	{
	bool unrecoverableException;
	exception = TryConnectUsingPath(managementPath, out unrecoverableException);
	if (exception == null)
	{
	// Refresh the WmiConnection info now that we found a valid WMI Provider
	m_WmiConnectionInfo = new WmiConnectionInfo(m_ManagementScope, this);
	return;
	}
	// If the exception was unrecoverable, just stop iterating
	if (unrecoverableException)
	{
	break;
	}
	}

And the namespace15 (in my example) is there, so the connection is successful. It is just empty - which is not tested. Can you add a "test for emptyness" after line 337 and if the namespace is empty, not return but continue? That would then test with namespace14 and find the higherst filled namespace.

[Matteo-T]

FWIW, I have a fix ready (in SQL vNext) to make this right.
Of course, we'll still want to change outlined in this issue.

[potatoqualitee]

Wow, awesome! 💯 💯 💯 💯

[Matteo-T]

PR with the fix sent out (in our internal fork).

[potatoqualitee]

Nice!