|
<############# 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 |
|
} |
