A Port, a Port! My Kingdom for a Port!!

One of the steps in setting up database mirroring is to assign a port to the endpoint. It's common practice to simply give it port 5022, but what if that port is already being used? The endpoint creation doesn't verify the availability of a port and will be created successfully. You won't know that the port is already being used until you get a communication error when trying to start the mirroring session. Then you have to go through troubleshooting steps to determine if it is the port, and if so, which port?

There's an easier way

Rather than waiting for mirroring to fail with a communication failure, why not simply verify the availability of a port before assigning it to the endpoint? Well this too kind of sounds like a lot of trouble to go through to prevent a failure that only occurs a small percentage of time.

I figured that there had to be an easy to do it. I was in the process of writing an powershell script for automating database mirroring setup when I decided to look for a solution to this problem. This issue was especially pressing for me because I was testing the script using instances on the same server. I did not want to rely on hard coded port numbers, and I wanted my solution to be part of my powershell script. So I found a powershell solution.

The Script

In addition to including the script in my database mirroring setup script, I created a stand-alone powershell script for this function alone.

Execution Syntax:

     .\FindAFreePort.ps1 "RemoteServer"
     .\FindAFreePort.ps1 "RemoteServer" "5022,5023"

Parameters:

     $ServerToCheck - [string] Name of server to check.
     $PortsToExclude - [string] Comma delimited list of ports to exclude.

FindAFreePort.ps1:

## Find an unused port
Param (
     [string] $ServerToCheck,
     [string] $PortsToExclude
)

if (!$ServerToCheck) {
     $ServerToCheck = read-host "Enter server to check:"
}

if (!$PortsToExclude) {
     $PortsToExclude = read-host "Enter ports to exclude (optional):"
}

if (!$ServerToCheck) {
     write-host "     Server to check is a required parameter. Execution halted." -f red
     Exit
}

$PortsUsed = $PortsToExclude.Split(",")
$PortArray = ((5022..5025), (7022..7025), (5026..6000), (7026..8000))
$socket = new-object System.Net.Sockets.TcpClient
$PortAvailable = 0
foreach ($Ports in $PortArray) {
     foreach ($Port in $Ports) {
          if ($PortsUsed -notcontains $Port) {
               $erroractionpreference = "SilentlyContinue"
               $socket.Connect($ServerToCheck, $Port)
               if (!$socket.Connected) {
                    $PortAvailable = $Port
                    $erroractionpreference = "Continue"
                    $error.clear()
                    $socket.Close()
                    break
               } else {
                    $socket.Disconnect()
               }
          }
     }
     if ($PortAvailable -ne 0) { break }
}
write-host "     Port $PortAvailable appears to be available" -f green
return $PortAvailable

Why exclude ports?

Ah, you noticed that I included an optional parameter to include a delimited list of ports to exclude. One issue I discovered along the way is that whn you create an endpoint, it doesn't actually start using the port until the mirroring session has been started. So if my principal and mirror are on SQL instances on the same machine, I will get the same port back both times since the port still is not in use yet.

The mirroring setup script does this behind the scenes, but for the purpose of this script, if I want to ensure I get a different port each time, I remind it what ports it has already given me. For example, if it returns port 5022 the first time, and I use it to create the endpoint on the principal, I now want a different port so I can create an endpoint on the mirror. So I pass "5022" as the ports to exclude, and it does not even test this port for availability.

What's with the weird array at the beginning?

Since I wrote this script specifically for use with database mirroring, I wanted the function to search for preferred port numbers first before just doing a straight port scan. This ensures that the common ports for mirroring will be checked first before the uncommon ports. So basically, it is an array of arrays. I loop through the array to get the arrays inside the arrays and then loop through these arrays one at time. And to make things more confusing, I used the ".." range indicator rather than listing the individual range members in the classic comma delimited style.