powershell - 需要为每个 SQL Server 打开 PSSession 才能运行查询

Question

我必须从 7 个 SQL Server 上的表中提取一列的总和。我以前从未使用过 SQL PS。

似乎我可以让它工作的唯一方法是为每个服务器打开一个 PS 会话并运行查询。那正确吗？

我正在尝试弄清楚如何对每个人进行身份验证，然后只打开一次 PS 会话以从第一台服务器获取 cmdlet。

我试过只打开第一个，然后使用 Credentials 开关运行查询，但这给了我一个登录错误。

[array] $SQLServers= @("SQLSERVER1","SQLSERVER2","SQLSERVER3","SQLSERVER4","SQLSERVER5","SQLSERVER6","SQLSERVER7")
#$credentials = Get-Credential
$report = @()
$username = "domain\username"
$password = Get-Content 'PathToSecureTextFile' | ConvertTo-SecureString
$cred = new-object -typename System.Management.Automation.PSCredential `
         -argumentlist $username, $password

$reportQuery = @"

SELECT SUM(COLUMNNAME) FROM TABLENAME

"@

foreach ($SQLServer in $SQLServers){

    Switch ($SQLServer){
        "SQLSERVER1" {$pod = "POD1";
                      $database = "DATABASE01"}
        "SQLSERVER2" {$pod = "POD2";
                      $database = "DATABASE02"}
        "SQLSERVER3" {$pod = "POD3";
                      $database = "DATABASE03"}
        "SQLSERVER4" {$pod = "POD4";
                      $database = "DATABASE04"}
        "PDC2SQL071" {$pod = "POD5";
                      $database = "DATABASE05"}
        "PDC2SQL072" {$pod = "POD6";
                      $database = "DATABASE06"}
        "PDC2SQL073" {$pod = "POD7";
                      $database = "DATABASE07"}

    }


    Write-Host "you are using Server" $SQLServer "with database " $database

    $newSQLPS = New-PSSession -ComputerName $SQLServer -Credential $cred
    Import-Module -Name SQLPS -PSSession $newSQLPS

    [int]$dataMigrated = [math]::Round(((Invoke-Sqlcmd -ServerInstance $SQLServer `
         -Database $Database -Query $reportQuery).Column1/1024/1024/1024),2)

    $props = [ordered]@{
        Pod = $pod
        DataMigrated = $datamigrated
    }

    Write-host "Server $SQLServer Migated $datamigrated GB" -ForegroundColor Green
    Write-Host "==========================================" -ForegroundColor Yellow

    $reportRow = New-Object psobject -Property $props
    $report += $reportRow

}

#New-Object PSObject -Property $report
$report

这确实有效，我得到了我想要的结果，但我只是在想，如果我能正确地进行身份验证，那么脚本会运行得更快。

标签： powershell