sql – 具有Powershell的多线程帮助

所以我有一个脚本将通过sql Server中存储的列表中的所有服务器进行ping操作.该脚本工作正常,但它按顺序完成(跛脚).

有人可以帮助我如何改变这个使用多线程而不是foreach循环？

$Server = "ServerName"
$Database = "DatabaseName"

$con = "server=$Server;database=$Database;Integrated Security=sspi"
$cmd = "SELECT ServerName FROM dbo.vwServerListActive"

  $da = new-object System.Data.sqlClient.sqlDataAdapter ($cmd,$con)

  $dt = new-object System.Data.DataTable

  $da.fill($dt) | out-null


  foreach ($srv in $dt)
    {

    $ping = new-object System.Net.NetworkInformation.Ping
    $Reply = $ping.send($srv.ServerName)

    $ServerName = $srv.ServerName 

    $ServerName
    $Reply.status

    if ($Reply.status –eq “Success”)
    {
        $sql = "UPDATE dbo.ServerList SET GoodPing = 1 WHERE GoodPing <> 1 AND ServerName = '$ServerName'"

    }
    else
    {
        $sql = "UPDATE dbo.ServerList SET GoodPing = 0 WHERE GoodPing <> 0 AND ServerName = '$ServerName'"
    }

    $Reply = ""

    invoke-sqlcmd -serverinstance $Server -database $Database -query $sql


    }

解决方法

如果有PowerShell 2.0,您可以使用后台作业.您需要将服务器列表分解为“组”.给定具有serverName和groupName的源表：

CREATE TABLE [dbo].[vwServerListActive](
    [serverName] [varchar](50) NULL,[groupName] [char](1) NULL
)

对您的脚本稍作修改(另存为forum.ps1)：

param($groupName)

$Server = "$env:computername\sql2k8"
$Database = "dbautility" 

$con = "server=$Server;database=$Database;Integrated Security=sspi" 
$cmd = "SELECT ServerName FROM dbo.vwServerListActive WHERE groupName ='$groupName'" 

  $da = new-object System.Data.sqlClient.sqlDataAdapter ($cmd,$con) 

  $dt = new-object System.Data.DataTable 

  $da.fill($dt) | out-null 


  foreach ($srv in $dt) 
    { 

    $ping = new-object System.Net.NetworkInformation.Ping 
    $Reply = $ping.send($srv.ServerName) 

    new-object PSObject -Property @{ServerName=$($srv.ServerName); Reply=$($Reply.status)} 

    }

然后,您可以为不同的组调用脚本：

#groupName A
start-job -FilePath .\forum.ps1 -Name "Test" -ArgumentList "A"
#groupName B
start-job -FilePath .\forum.ps1 -Name "Test" -ArgumentList "B"

Get-Job -name "test" | wait-job | out-null
Get-Job -name "test" | receive-job

#get-job -name "test" |remove-job

如果您使用的是PowerShell V1或sqlps,则可以使用System.Diagnostics.ProcessStartInfo启动单独的powershell.exe进程并传递组名.

param($groupName)

    $StartInfo = new-object System.Diagnostics.ProcessStartInfo
    $StartInfo.FileName = "$pshome\powershell.exe"
    $StartInfo.Arguments = " -NoProfile -Command C:\scripts\forum.ps1 $groupName"
    $StartInfo.WorkingDirectory = "C:\scripts"
    $StartInfo.LoadUserProfile = $true
    $StartInfo.UseShellExecute = $true
    [System.Diagnostics.Process]::Start($StartInfo) > $null

sql – 具有Powershell的多线程帮助

解决方法

猜你在找的MsSQL相关文章