Home Forums Programming Powershell How do you connect to Central Management Servers if your server has an instance name with it RE: How do you connect to Central Management Servers if your server has an instance name with it

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