Sql-server – How does the PowerShell SQLPS SQLSERVER provider detect central management servers in SQLRegistration\Central Management Server Group

powershellsql server

I'm experiencing odd behavior with the PowerShell SQLSERVER provider and the SQLRegistration\Central Management Server Group directory. Below are the behaviors. I would like to know how the SQLSERVER provider knows what CMS's are registered so that I can understand why it doesn't seem to notice the registration when done in SSMS 2014 (see below for detailed behavior). Also, why my connection fails when I know the instance is online.

Setup
Machine

  • localhost

Instances

  • localhost\SQL2012
  • localhost\SQL2014
  • localhost\SQL2014_1

Behavior 1
Using SSMS 2014, I registered localhost\SQL2012 as a CMS. Executing this code returns no items. 

PS SQLSERVER:\SQLRegistration\Central Management Server Group> dir

Open and closing the PowerShell console and SSMS does not change the results. If I open SSMS 2012 and register localhost\SQL2012 as a CMS and re-run the above command, I see localhost\SQL2012 registered as expected.

Behavior 2
After getting the server to list successfully…

Directory: Microsoft.SqlServer.Management.PSProvider\SqlServer::SQLSERVER:\SQLRegistration\Central Management
Server Group

Mode Name

  • localhost\SQL2012

And running the below command to try and navigate to my registered server groups fails with the below error, even though the instance is online and available. 

PS SQLSERVER:\SQLRegistration\Central Management Server Group> Set-Location "localhost\SQL2012\"

Set-Location : Cannot find path 'SQLSERVER:\SQLRegistration\Central Management Server Group\localhost\SQL2012\'
because it does not exist.
At line:1 char:1
+ Set-Location "localhost\SQL2012\"
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : ObjectNotFound: (SQLSERVER:\SQLR…alhost\SQL2012:String) [Set-Location], ItemNotFoundE
xception
+ FullyQualifiedErrorId : PathNotFound,Microsoft.PowerShell.Commands.SetLocationCommand

Update 1
SMO seems to work fine. The below code successfully returns my server groups. 

#Load SMO assemblies
$CentralManagementServer = "localhost\sql2012"
$MS='Microsoft.SQLServer'
@('.SMO', '.Management.RegisteredServers', '.ConnectionInfo') |
     foreach-object {if ([System.Reflection.Assembly]::LoadWithPartialName("$MS$_") -eq $null) {"missing SMO component $MS$_"}}

$connectionString = "Data Source=$CentralManagementServer;Initial Catalog=master;Integrated Security=SSPI;"
$sqlConnection = new-object System.Data.SqlClient.SqlConnection($connectionString)
$conn = new-object Microsoft.SqlServer.Management.Common.ServerConnection($sqlConnection)
$CentralManagementServerStore = new-object Microsoft.SqlServer.Management.RegisteredServers.RegisteredServersStore($conn)

$CentralManagementServerStore.ServerGroups[ "DatabaseEngineServerGroup" ].ServerGroups

Best Answer

Behavior 1 I can expect because there are some features that may or may not work as expected when using higher version of SSMS on lower version of instance. There could be things in the background that changed just slightly from 2014 to 2012 that you will probably never find any documentation on. I would actually create a connect item on this if you can recreate this scenario on another machine every time.

Now, Behavior 2 failed the first time I tried, but then succeeded every time after that, even after opening/closing windows. However if you are trying this using just PowerShell.exe it is to be expected that some things will not function the exact same way when you are dealing with the SQLPS provider. If you tried doing the same command within SQLPS.exe I would expect it will work every time, it does for me at least. Now I am not using SQL Server 2014, only 2012 is loaded on my machine right now.

You can see the fact of how different PowerShell.exe and SQLPS.exe are by comparing the assemblies loaded in each one:

enter image description here

Related Question