SQL Server How to check port connectivity for list of windows servers

5anewei6  于 2023-03-07  发布在  Windows
关注(0)|答案(1)|浏览(138)

As a database admin in a microsoft shop, my team supported several database servers, with hundreds of databases, used by lots of servers. It's no secret that firewall rules can be applied in non-prod without making it to production or prod standby (dr). You can have it working perfectly in prod, then failover and have connections fail because ports aren't open for the secondary servers.

2vuwiymt

2vuwiymt1#

I wrote this script to remote into windows servers hosting our integrations and test port connectivity.

  1. create a .ps1 script with this content
#--for windows hosts-- output to array then dump array to file
#https://stackoverflow.com/questions/41130310/change-directory-in-powershell
Set-Location -Path  C:\Users\foo_user_name\Desktop

#https://social.technet.microsoft.com/Forums/windows/en-US/78d5a5fa-bb82-4c2d-a2c1-96d518b9bd74/need-to-read-text-file-as-an-array-and-get-elements-from-each-line-of-array-using-powershell?forum=winserverpowershell
$DB = Get-Content SourcePortDestinationTestCases.csv | select-object -skip 1 #skip the first header row

#https://ss64.com/ps/syntax-arrays.html
$Resultlist = new-object system.collections.arraylist

#https://social.technet.microsoft.com/Forums/en-US/4452976f-47fd-430d-b65f-8fd3f33fff0e/get-date-format-powershell?forum=winserverpowershell
$date = get-date
$date =$date.Tostring("yyyyMMdd") 
$newfilename = "SourcePortDestinationTestCasesResults" + $date+ ".csv"

#https://blogs.technet.microsoft.com/heyscriptingguy/2015/06/07/powertip-use-powershell-to-create-new-file/
New-Item   $newfilename -ItemType file -Force

$OldServer =''
foreach ($Data in $DB) {
  $Source, $Port, $Destination = $Data -split ',' -replace '^\s*|\s*$'
  
  write-host "SourceServer is: "$Source
  write-host "Port is: "$Port
  write-host "Destination is: "$Destination
  If (-NOT ($Source -eq  $OldServer)) {
     Exit-PSSession -
     Enter-PSSession -ComputerName $Source 
  }
  #https://social.technet.microsoft.com/Forums/ie/en-US/7a3304c7-b564-4acc-ab28-2648a20f4bce/telnet-using-powershell?forum=winserverpowershell
   If ( Test-Connection $Destination -Count 1 -Quiet) {
    
        try {       
            $null = New-Object System.Net.Sockets.TCPClient -ArgumentList $Destination,$Port
            $props = @{
                SourceServer = $Source
                Port = $Source
                DestinationServer = $Destination
                PortOpen = 'Yes'
            }# | Format-Table | Out-File -FilePath $newfilename -Append 
            $PortOpen = 'Yes'  
            
        }

        catch {
            $props = @{
                SourceServer = $Source
                Port = $Port
                DestinationServer = $Destination
                PortOpen = 'No'
            } #| Format-Table | Out-File -FilePath  $newfilename -Append 
            $PortOpen = 'No'
        }
    }

    Else {
        
        $props = @{
            SourceServer = $Source
            Port = $Port
            DestinationServer = $Destination
            PortOpen = 'Server did not respond to ping'
        } #| Format-Table | Out-File -FilePath  $newfilename -Append 
        $PortOpen = 'Server did not respond to ping'
    }

    #https://community.spiceworks.com/topic/1358008-building-an-array-list-and-exporting-to-a-csv
    $Output = New-Object -Type PSCustomObject 
    $output | Add-Member -MemberType NoteProperty -Name "Source" -value $Source
    $output | Add-Member -MemberType NoteProperty -Name "Port" -value $Port
    $output | Add-Member -MemberType NoteProperty -Name "Destination" -value $Destination
    $output | Add-Member -MemberType NoteProperty -Name "PortOpen" -value $PortOpen

    $Resultlist.add($Output) | Out-Null
    New-Object PsObject -Property $props
    $OldServer = $Source
  
}

$Resultlist | Export-Csv $newfilename 
$Resultlist[0][0][0][0]
exit
  1. create a csv called SourcePortDestinationTestCases.csv fill with your source, port, and destinations you have to be able to remote into the windows server for this to work. here's the csv format
Source,Port,Destination
ServerA,6000,ServerB

相关问题