Calculate-AwsAlbSorting.ps1

<############# SETTINGS #################>

# number of rules/sub-applications to select a site to have it's own ALB
$singleSiteRuleLimit = 1000

# maximum number of rules on a shared site.
$sharedSiteRuleLimit = 1000

# The "shared" ALBs will evenly distribute the sites using a SINGLE primary
# sort options. Each value in $sortByOptions will generate out a new distribution.
# Each option list results in different ALB configurations
#$sortByOptions = @("Avg","WebAppCount")
$sortByOptions = @("WebAppCount")

# This is the number of "shared" ALBs to create. It's really hard to
# have the system dynamically figure this out.
$minimumBagCount = 1

# your database server name (ie. "localhost,1433")
$datatbaseServer = "<your-server>,<port>"

# your database name
$databaseName = "IisLogs"

<############# END OF SETTINGS ##########>

# pull the input from the database

function Invoke-SqlCmd {
param (
	[string] $Server,
	[string] $Database,
	[string] $Query,
	[int] $Timeout = 30
)

	$connection = New-Object System.Data.SqlClient.SqlConnection
	$connection.ConnectionString = "Server=$Server;Database=$Database;Integrated Security=True;Connection Timeout=$Timeout"
	$connection.Open()

	try {

		$command = $connection.CreateCommand()
		$command.CommandText = $Query
		$command.CommandTimeout = $Timeout

		$result = $command.ExecuteReader()
		$table = New-Object System.Data.DataTable
		$table.Load($result)
	} finally {
		$connection.Close()
	}

	return $table
}
	

$lcuSumsQuery = @"
select		SiteName
			,min(WebAppCount)			[WebAppCount]
			,sum([requests-per-hour])	[requests-per-hour-sum]
			,sum([rule-eval-lcus])		[rule-eval-lcus-sum]
			,sum([new-conn-lcus])		[new-conn-lcus-sum]
			,sum([active-conn-lcus])	[active-conn-lcus-sum]
			,sum([bandwidth-lcus])		[bandwidth-lcus-sum]
			,sum([max-lcus])		[max-lcus-sum]
from			dbo.vw_ProxySiteLCUComparison
where		[Date] >= '$startDateSql' and [Date] < '$endDateSql'
group by	SiteName
"@

$lcuSumsComparison = DatabaseUcsb\Invoke-SqlCmd -Server $datatbaseServer -Database $databaseName -Query $lcuSumsQuery

# calculate everything

$singles = $lcuSumsComparison |? { $_.WebAppCount -gt $singleSiteRuleLimit }
$shared = $lcuSumsComparison |? { $_.WebAppCount -le $singleSiteRuleLimit }

#$singles | ft -AutoSize WebAppCount, request-per-hour-sum, SiteName
#$shared | ft -AutoSize

$albSortedOptions = @{}
foreach($sortBy in $sortByOptions) {

	$albs = @{ Cnt = 0 }
	foreach($site in $singles) {
		$albs."$($albs.Cnt)" = @($site)
		$albs.Cnt++
	}

	function Get-RuleEvalLcu {
	param(
		$RuleCount,
		$RequestsPerHourCount
	)
		# This follows this statement of “on average there will be 6 rule evaluations per request”
		# ((22 sub-applications / 2) – (10 free requests / 2))
		$ruleEvaluations = ($RuleCount / 2) - 5
		$ruleEvaluationsPerHour = $ruleEvaluations * $RequestsPerHourCount
		$lcu = $ruleEvaluationsPerHour / 3600 			# 3600 seconds in an hour = 60 seconds * 60 minutes
		return $lcu
	}
	
	function Sort-IntoBags {
	param(
		$BaseCount,
		$SharedSites,
		$BagCount,
		$SortBy,
		$RuleLimit
	)
		$results = @{ Cnt = $BaseCount + 1 }
		$bi = 1;
		$lastbi = $bi;
		$isEdge = $true;
		$increment = 1;
		$retry = 0;
		$SharedSites = $SharedSites | sort -Property $SortBy -Descending
		for($i = 0; $i -lt $SharedSites.Count; $i++) {
			$site = $SharedSites[$i]
			
			$bagIndex = $baseCount + $bi
		
			if($results."$bagIndex" -eq $null) { $results."$bagIndex" = @(); $results.Cnt++; }
			
			$bag = $results."$bagIndex"
		
			$ruleCount = ($bag.WebAppCount | Measure-Object -Sum).Sum
			if($ruleCount + $site.WebAppCount -gt $RuleLimit) {
				$i--
				$retry++

				if($retry -eq $BagCount) {
					$i++
					$bag += @($site)
					$retry = 0
				}
			} else {
				$bag += @($site)
				$retry = 0
			}

			if(@(1, $BagCount) -contains $bi -and $bi -ne $lastbi) {
				$i++
				if($i -ge $SharedSites.Count) { break; }
				$site = $SharedSites[$i]
				$bag += @($site)
				$increment = -1 * $increment
			}
		
			$results."$bagIndex" = $bag
			$lastbi = $bi

			if($BagCount -eq 1) {
				# edge case, only 1 bag, don't increment the bag index
			} else {
				$bi += $increment
			}
			
		}

		return $results
	}

	

	$currentBagCount = $minimumBagCount
	$continueSorting = $true
	$baseCount = $Albs.Cnt - 1
	$firstSharedIdx = $Albs.Cnt
	while($continueSorting) {

		$loopBagCount = $currentBagCount
		$sortedBags = Sort-IntoBags -BaseCount $baseCount `
									-SharedSites $shared `
									-BagCount $currentBagCount `
									-SortBy $sortBy `
									-RuleLimit $sharedSiteRuleLimit

		# create groupings in database
		$truncateGroupings = "truncate table dbo.ALBGrouping"
		DatabaseUcsb\Invoke-SqlCmd -Server $datatbaseServer -Database $databaseName -Query $truncateGroupings
		for($j = $firstSharedIdx; $j -lt $sortedBags.Cnt; $j++) {
			$sites = $sortedBags."$j"
			foreach($site in $sites) {
				$insertGrouping = "insert into dbo.ALBGrouping (GroupId, SiteName) values ($j, '$($site.SiteName)')"
				DatabaseUcsb\Invoke-SqlCmd -Server $datatbaseServer -Database $databaseName -Query $insertGrouping
			}
		}

		# retrieve the LCU values
		$albMonthlyLCUCost = "exec usp_Aggregate_ALBLCUComparison_For_DateRange @dateStart = '$startDateSql', @dateEnd = '$endDateSql'"
		$albLcuComparison = DatabaseUcsb\Invoke-SqlCmd -Server $datatbaseServer -Database $databaseName -Query $albMonthlyLCUCost

		# https://aws.amazon.com/elasticloadbalancing/pricing/
		$daysInMonth = ($endDate - $startDate).Days
		$albMonthlyBaselineCost = 0.0225 * $daysInMonth * 24 # ALB hour * days in month * 24 hours

		# check that no single ALB's LCU costs more than the baseline ALB monthly price
		# (if the LCU cost goes above the ALB monthly price, then add a new ALB and resort the sites)
		$continueSorting = $false
		foreach($grp in $albLcuComparison) {
			if($grp."max-lcus-cost-mth" -gt $albMonthlyBaselineCost) {
				$currentBagCount++
				$continueSorting = $true
			}
		}

		<# Used for debugging the incremental attempts #>
		$sortedStats = @()
		for($k = 0; $k -lt $sortedBags.Cnt; $k++) {
			$b = $sortedBags."$k"
			$lcus = $albLcuComparison |? GroupId -eq $k
			$props = @{
				Id = $k
				SiteNames = $b.SiteName
				SiteCount = ($b.WebAppCount | Measure-Object -Sum).Count
				RuleCount = ($b.WebAppCount | Measure-Object -Sum).Sum
				AvgRequestCount = ($b."requests-per-hour-sum" | Measure-Object -Sum).Sum
				LCUs = $lcus."max-lcus-mth"
				LCUCost = $lcus."max-lcus-cost-mth"
				BaselineCost = $albMonthlyBaselineCost
				TotalCost = $albMonthlyBaselineCost + $lcus."max-lcus-cost-mth"
			}
			$sortedStats += @([PSCustomObject] $props)
		}
		Write-Host "Testing BagCount: $loopBagCount (Total Cost: `$$(($sortedStats.TotalCost | Measure-Object -Sum).Sum))"
		$sortedStats | ft -AutoSize Id, SiteCount, RuleCount, AvgRequestCount, LCUs, LCUCost, BaselineCost, TotalCost, SiteNames
		
	}
	
	# add the sorted bag results into the albs
	for($j = $firstSharedIdx; $j -lt $sortedBags.Cnt; $j++) {
		$albs."$j" = $sortedBags."$j"
		$albs.Cnt++
	}
	
	
	$albStats = @()
	for($j = 0; $j -lt $albs.Cnt; $j++) {
		$alb = $albs."$j"
		$lcus = $albLcuComparison |? GroupId -eq $j
		$props = @{
			Id = $j
			SiteNames = $alb.SiteName
			SiteCount = ($alb.WebAppCount | Measure-Object -Sum).Count
			RuleCount = ($alb.WebAppCount | Measure-Object -Sum).Sum
			AvgRequestCount = ($alb."requests-per-hour-sum" | Measure-Object -Sum).Sum
			LCUs = $lcus."max-lcus-mth"
			LCUCost = $lcus."max-lcus-cost-mth"
			BaselineCost = $albMonthlyBaselineCost
			TotalCost = $albMonthlyBaselineCost + $lcus."max-lcus-cost-mth"
		}
		$albStats += @([PSCustomObject] $props)
	}

	$albSortedOptions."$sortBy" = $albStats
}

foreach($sortBy in $sortByOptions) {
	$configuration = $albSortedOptions."$sortBy"

	Write-Host "Sorted by $sortBy :"
	$configuration | ft -AutoSize Id, SiteCount, RuleCount, AvgRequestCount, LCUs, LCUCost, BaselineCost, TotalCost, SiteNames
}