ManagedComputer

In this article I talk about an error I've been encountering with the root\Microsoft\SqlServer\ComputermanagementXX namespace since 2014.
It can save you some frustration, and maybe you can help making it go away. This post was previously published on my old blog on 2014-10-28.

ManagedComputer

Here's a short discovery on managing SQLServer Services with PowerShell. There are multiple ways for almost all tasks, but some are more suited than others. If we explore the use of WMI, SMO, the PS Provider and especially SQL WMI Provider, not every combination is successfull. Is there something wrong on my configuration? Or did I find a bug?

I did some tests on different servers: SQL Server 2008R2 and SQL Server 2012 on Windows Server 2008R2, and SQL Server 2014 on Windows Server 2012R2. The goal is to manage SQL Server Services without having to use the SQL Server Configuration Manager.

All following code is executed on my client computer with SQL Server 2014 installed, but I ran the same statements on two other servers with SQL 2014 and to different targets with SQL Server 2012 on it. The outcome was the same for each instance of the same version.

Get-WmiObject

The first method to do some remote management is by using

Get-WmiObject -Namespace 'root\Microsoft\SqlServer\Computermanagement10' -computername SQL2008R2
Get-WmiObject -Namespace 'root\Microsoft\SqlServer\Computermanagement11' -computername SQL2012
Get-WmiObject -Namespace 'root\Microsoft\SqlServer\Computermanagement12' -computername SQL2014  

This always works from whatever client I execute it. As long as we use the right namespace for each version, everything works fine from every client to every server. I conclude that this uses the WMI provider on the serverside.

SQL Provider

Next I'm using the SQL Provider:

Set-Location SQLServer:  
Get-ChildItem \Sql\My2008R2\default\databases  

gives us a list of databases from My2014, My2012 and My2008R2, but only on My2012 there's a warning preceding the list, saying "Could not obtain SQL Server Service information. An attempt to connect to WMI on 'My2012' fails with the following error:

SQL Server WMI provider is not available on My2012. --> Invalid namespace"

twice. The SQL Server Service information is delivered through the ComputerManagementxx namespace as used above, so this could be a sign that the PSDrive is using the wrong version when connecting to My2012, but the right one when connecting to My2008R2 or My2014.

Browsing the PSDrive on My2012:

Browsing the PSDrive on My2008R2:

SMO

A third method constructs a .Net object using SMO over WMI:

$My2012 = New-Object Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer -ArgumentList My2012  
$My2012.clientprotocols | Select DisplayName,IsEnabled,Order  

When executed from a client with SQL Server 2014 installed, this code gives us a list from My2008R2 and My2014, but not from My2012. I get an error this time, again saying

"SQL Server WMI provider is not available on My2012."

We know that can't be true because:

1. Our first method, using Get-WmiObject returned the correct results from this server without complaining.
2. The same code from another SQL2012 to MySQL2012 also works fine.

From My2008R2:

From My2012:

So my guess is that the New-Object is constructing an object with information it gets from the WMI Computermanagementxx class on the server, but when connecting to My2012 it's not using the right namespace for SQL Server 2012. I would like to know if you get the same experience. If so, I think this is a bug, maybe in the SqlWmiManagement.dll ( I am not nor ever have been a developer so I don't known a lot about those things. If I'm making wrong assumptions, please correct me.)

No backward compatibility

It's normal that the connection from an older client to a newer server fails, since the namespace of the new version wasn't known when the older dll's were written. This method fails from a SQL Server 2012 client connecting to a SQL Server 2014, but I think that's no point. I would assume it has to work from any SQL Server 2014 to a SQL Server 2012 however. Especially since connecting from a SQL Server 2014 to a SQL Server 2008R2 gives no error.

you to the rescue

If you do not have this issue, I must keep looking for some configuration mistake I made on my servers. And I would really love to resolve this because managing SQL Server with one of those techniques can be a very interesting way to replace the SQL Server Configuration Manager. I think it's better in several ways:

• Once you know the CmdLets or wrote it down in a script, it's much faster to use.
• Using the SQL SCM you need to install the Client Tools SDK or the Feature Pack of every SQL Server version you want to manage. Using WMI you use the necessary components on the Server.
• In a script it's easy to execute certain tasks or collect an inventory from multiple SQL Servers in one go.

So if you can shed some light on this problem, I would really appreciate your input.

Next Post