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

  • 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

  • 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!!!

  • 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

  • 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!!!

  • 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"

  • 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.

  • 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.

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

  • 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!!!

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

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