sql - Powershell – 使用 SQL 数据 Ping 服务器进程

Question

我有一个从 SQL 数据库表中获取服务器列表的进程。从那里，该列表通过一个循环并执行 ping 测试。服务器和 ping 测试结果被发送回同一个 SQL 数据库表，但使用结果更新列“IS_PINGABLE”。

以下 Powershell 脚本执行此操作，但速度非常慢。我从这里获取了部分代码：https ://gallery.technet.microsoft.com/scriptcenter/Powershell-Script-to-ping-15e0610a并添加了一些其他步骤。

如果有人有任何建议让这个更快、更好、更强大，请在下面告诉我。提前谢谢了。

$MasterServerConnString = dbserver,1433


#**********************************************************************
#region Get Server List
#Get Server list from SQL DB Table and save to temp Powershell table
#**********************************************************************

$ping_cmd = "Set NOCOUNT ON; SELECT distinct machinename, IS_PINGABLE FROM [DB].[dbo].[TABLE] order by 1"

$ping_cn = new-object System.Data.SqlClient.SqlConnection("Data Source=$MasterServerConnString;Integrated Security=SSPI;Initial Catalog=DB");
$ping_cn.Open()
            $ping_a = $ping_cn.CreateCommand()
            $ping_a.CommandText = $ping_cmd
            $ping_a = $ping_a.ExecuteReader()
            $ping = new-object “System.Data.DataTable” "computername"
            $ping.Load($ping_a)
$ping_cn.Close()

$Server = $ping.Item(0)

#**********************************************************************
#endregion

#**********************************************************************
#region Ping Sever Test
#Ping each server to see if there is connectivity.  If pingable, 1.  If not pingable 0.
#**********************************************************************

$PCData = foreach ($PC in $Server) {

    Write-Verbose "Checking computer'$PC'"
    try {
        Test-Connection -ComputerName $PC -Count 2 -ErrorAction Stop | Out-Null

        $Props = @{
            ComputerName   = $PC
            Status         = 1

        }
        New-Object -TypeName PSObject -Property $Props
    } catch { # either ping failed or access denied 
        try {
            Test-Connection -ComputerName $PC -Count 2 -ErrorAction Stop | Out-Null
            $Props = @{
                ComputerName   = $PC
                Status         = 0

            }
            New-Object -TypeName PSObject -Property $Props            
        } catch {
            $Props = @{
                ComputerName   = $PC
                Status         = 0

            }
            New-Object -TypeName PSObject -Property $Props              
        }
    }
}

#**********************************************************************
#endregion


#**********************************************************************
#region Upload Results
#Upload the ping results for each server back to the database table
#**********************************************************************

foreach ($sv in $PCData)
{

$svr_name = $sv.ComputerName
$svr_stat = $sv.Status


$Updatequery = " 
Update [DB].[dbo].[TABLE]
SET
IS_PINGABLE = $svr_stat
WHERE
MachineName = '$svr_name'
"

$A_cn = new-object System.Data.SqlClient.SqlConnection("Data Source=$MasterServerConnString;Integrated Security=SSPI;Initial Catalog=DB");
$A_cn.Open() 
        $command = $A_cn.CreateCommand()
        $command.CommandText = $Updatequery
        $result = $command.ExecuteReader()
$A_cn.Close()

}
#**********************************************************************
#endregion

标签： sqlsql-serverpowershell