How do you connect to Central Management Servers if your server has an instance name with it

Question

Post reply

How do you connect to Central Management Servers if your server has an instance name with it

itpcpro

Ten Centuries

Points: 1043
More actions
March 5, 2015 at 12:25 pm

#315888

Trying to connect to my CMS using powershell but the server name for CMS has an instance name and all of the script I find online only has the server name.
I cant figure out the combination I need to use to make it work
$serverGroupPath = "SQLSERVER:\SQLRegistration\Central Management Server Group\<Server\Instance>\NonProd\Dev"
I've tryed " ",' ',(),[] and I figured not putting anything around it, it would think the instance name would be folder.
Thanks
Scott

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Gary Varga SSC Guru Points: 82166 More actions · Answer 1

Please can you supply some context e.g. the failing line plus, perhaps, a line or two above it.

Gaz

-- Stop your grinnin' and drop your linen...they're everywhere!!!

itpcpro Ten Centuries Points: 1043 More actions · Answer 2

Here is what I am getting if I use a single quote around the server and instance name:

Get-ChildItem : Cannot find path 'SQLSERVER:\SQLRegistration\Central Management Server Group\'Server\InstanceName'\NonProd

\Dev' because it does not exist.

At C:\SQL\CMS_Query.ps1:16 char:24

+ $instanceNameList = dir <<<< $serverGroupPath -recurse | select-object Name -Unique

+ CategoryInfo : ObjectNotFound: (SQLSERVER:\SQLR...com\NonProd\Dev:String) [Get-ChildItem], ItemNotFoundException

+ FullyQualifiedErrorId : PathNotFound,Microsoft.PowerShell.Commands.GetChildItemCommand

Gary Varga SSC Guru Points: 82166 More actions · Answer 3

I think that I need to see some of the script to have a chance of understanding the issue.

Gaz

-- Stop your grinnin' and drop your linen...they're everywhere!!!

itpcpro Ten Centuries Points: 1043 More actions · Answer 4

I am using a script I found on toadworld:

http://www.toadworld.com/platforms/sql-server/b/weblog/archive/2012/10/10/using-powershell-to-run-queries-against-central-management-server.aspx

just trying to get it to work with my CMS server which has an Instance name.

Here is the script as I have modified it:

#========================================================================

# Created with: SAPIEN Technologies, Inc., PowerShell Studio 2012 v3.0.5

# Created on: 7/25/2012 7:33 PM

# Created by: cchurchwell

# Organization: PWC

# Filename: PopulateRemoteDatabase.ps1

#========================================================================

Import-Module SQLPS -DisableNameChecking

#Path to central management server

$serverGroupPath = "SQLSERVER:\SQLRegistration\Central Management Server Group\'ServerName\InstanceName'\NonProd\Dev"

#Get List of registered Servers from above path

$instanceNameList = dir $serverGroupPath -recurse | select-object Name -Unique

#$SqlQuery = "Select Name From SysDatabases"

#instanceName returns System.Management.Automation.PSCustomObject and have to call .Name to get the actual ServerName.

foreach($instanceName in $instanceNameList)

{

$serverName = $instanceName.Name

#Database name can be derived from the Server Name

$DatabaseName = 'Site-' + $serverName.Substring(2,3)

$DBParam1 = "DatabaseName=" + $DatabaseName

$DBParams = $DBParam1

Invoke-Sqlcmd -InputFile $scriptFile -Variable $DBParams -ServerInstance $serverName -ConnectionTimeout 300

Write-Output "Script Completed for $serverName"

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 5

I created a CMS with an instance name and duplicated your problem. There is no way to use SERVER\INSTANCE in the SQLRegistration path.

I was able to see the CMS server (as SERVER\INSTANCE) with this command:

dir "SQLSERVER:\SQLRegistration\Central Management Server Group" | ? { $_.Name -like '*\*' }

Adding " | gm " to that command showed the RegisteredServer properties. The PSPath property revealed it uses HTML-type encoding for the backslash, so it was SERVER%5CINSTANCE.

I tested SERVER%5CINSTANCE in the SQLRegistration path and it worked perfectly.

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 6

Another answer is to set up the Central Management Server in SSMS Registered Servers using an alias. Or go to the existing one and choose "Central Management Server Actions" -> Properties.

Leave "Server name" as the full SERVER\INSTANCE, but you can use any one-word alias you like for "Registered server name".

I verified that this works for your login on the computer where you registered the alias, but I would not be surprised to find out you would have to repeat it for every user and every computer where you want to run the script. I believe you're just changing connection info stored locally, not creating some kind of domain-wide alias.

itpcpro Ten Centuries Points: 1043 More actions · Answer 7

That script worked to get the naming of the server that would work in the script. Thank you so much..

Gary Varga SSC Guru Points: 82166 More actions · Answer 8

If Scott's answer resolved your issue please can you mark it as such for future readers of the thread. Thanks

Gaz

-- Stop your grinnin' and drop your linen...they're everywhere!!!