AWS ALB Price Planning w/ IIS : Add IisLogWebAppId

This post continues the series from AWS ALB Price Planning w/ IIS : Grouped Sites.

This doesn’t really help figure out much in the larger picture. But, I wanted to separate out statistics about the Web API applications from the normal web applications. Web API applications are strong candidates for rewrites as Serverless ASP.NET Core 2.0 Applications on Lambda Functions. Changing these applications to Lambda Functions won’t reduce the cost of the ALB as they will still use host names that will be serviced by the ALB. But, this will help figure out the tiny tiny costs that the Lambda Functions will charge each month.

This is just an intermediary step to add WebAppId’s to all of the requests.

Background Info

Instead of adding a WebAppId column onto the IisLog, I’m going to create a new table which will link the IisLog table entries to the ProxyWebApp table entries. The reason for this is that the IisLog table has 181,507,680 records and takes up 400 GB of space on disk. Adding a new column, even a single integer column, could be a very dangerous operation because I don’t know how much data the system might want to rearrange on disk.

Plan of Action and Execution

Instead, I’m going to

1. Add a WebAppId int Identity column onto table dbo.ProxyWebApp. The identity column won’t be part of the Primary Key, but it’s also a super tiny table.
2. Create a new table called dbo.IisLogWebAppId which takes the Primary Key of table dbo.IisLogWebAppId and combines it with WebAppId.
3. Create a script to populate dbo.IisLogWebAppId.
4. Create a stored procedure to add new entries nightly.

The scripts are below, but I think it’s worthwhile to note that the script to populate dbo.IisLogWebAppId took 4h57m to create on 181,507,680 records which was 15 GBs of disk space.

	use IisLogs
	go
	/*
	Script created by SQL Compare version 11.5.2 from Red Gate Software Ltd at 9/8/2018 11:05:41 PM
	*/
	SET NUMERIC_ROUNDABORT OFF
	GO
	SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON
	GO
	SET XACT_ABORT ON
	GO
	SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
	GO
	BEGIN TRANSACTION
	GO
	IF @@ERROR <> 0 SET NOEXEC ON
	GO
	PRINT N'Creating [dbo].[ProxyWebApps]'
	GO
	CREATE TABLE [dbo].[ProxyWebApps]
	(
	[WebAppId] [int] NOT NULL IDENTITY(1, 1),
	[SiteName] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[AppPath] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[MemoryMB] [int] NULL
	)
	GO
	IF @@ERROR <> 0 SET NOEXEC ON
	GO
	PRINT N'Creating primary key [PK_ProxyWebApps] on [dbo].[ProxyWebApps]'
	GO
	ALTER TABLE [dbo].[ProxyWebApps] ADD CONSTRAINT [PK_ProxyWebApps] PRIMARY KEY CLUSTERED ([SiteName], [AppPath])
	GO
	IF @@ERROR <> 0 SET NOEXEC ON
	GO
	PRINT N'Creating [dbo].[fn_FindWebAppId]'
	GO
	create function [dbo].[fn_FindWebAppId](
	@sitename varchar(255),
	@csUriStem varchar(255)
	)
	returns int
	as
	begin
	declare @webAppId int
	select top 1
	@webAppId = a.WebAppId
	from dbo.ProxyWebApps a
	where a.SiteName = @sitename
	and charindex(a.AppPath, @csUriStem) = 1
	order by len(a.AppPath) desc
	return @webAppId
	end
	GO
	IF @@ERROR <> 0 SET NOEXEC ON
	GO
	PRINT N'Creating [dbo].[IisLogWebAppId]'
	GO
	CREATE TABLE [dbo].[IisLogWebAppId]
	(
	[EntryTime] [datetime] NOT NULL,
	[LogFilename] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[RowNumber] [int] NOT NULL,
	[WebAppId] [int] NULL
	)
	GO
	IF @@ERROR <> 0 SET NOEXEC ON
	GO
	PRINT N'Creating primary key [PK_IisLogWebAppId] on [dbo].[IisLogWebAppId]'
	GO
	ALTER TABLE [dbo].[IisLogWebAppId] ADD CONSTRAINT [PK_IisLogWebAppId] PRIMARY KEY CLUSTERED ([EntryTime], [LogFilename], [RowNumber])
	GO
	IF @@ERROR <> 0 SET NOEXEC ON
	GO
	PRINT N'Creating [dbo].[usp_Calculate_IisLogWebAppId_For_Date]'
	GO
	create procedure [dbo].[usp_Calculate_IisLogWebAppId_For_Date]
	@Date date
	as
	declare @dateStart varchar(10) = @Date,
	@dateEnd varchar(10) = dateadd(d, 1, @Date),
	@includeAll bit = 0;
	insert into dbo.IisLogWebAppId
	select EntryTime, LogFilename, RowNumber,
	dbo.fn_FindWebAppId([SiteName], csUriStem) [webAppId]
	from dbo.IisLog
	where (
	(EntryTime >= @dateStart and EntryTime < @dateEnd)
	or @includeAll = 1
	)
	GO
	IF @@ERROR <> 0 SET NOEXEC ON
	GO
	COMMIT TRANSACTION
	GO
	IF @@ERROR <> 0 SET NOEXEC ON
	GO
	DECLARE @Success AS BIT
	SET @Success = 1
	SET NOEXEC OFF
	IF (@Success = 1) PRINT 'The database update succeeded'
	ELSE BEGIN
	IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION
	PRINT 'The database update failed'
	END
	GO

view raw sc_Add_IisLogWebAppId_Objects.sql hosted with ❤ by GitHub

AWS ALB Price Planning w/ IIS : Grouped Sites

This post continues the series from AWS ALB Price Planning w/ IIS : Rule Evaluations Pt. 2.

Having all the data in IIS and generating out all the hourly LCU totals helps define what the monthly charges could be. But, my expectations are that I will need to split the 71 DNS host names over multiple ALBs in order to reduce the total cost of the LCUs. My biggest fear is the Rule Evaluation Dimension. The more the host names on a single ALB, the more likely a request will go past the free 10 rule evaluations.

To do this, I need to build a script/program that will generate out possible DNS host name groupings and then evaluate the LCUs based upon those groupings.

In the last post I had already written a simple script to group sites based upon the number sub-applications (ie. rules) they contain. That script didn’t take the next step, which is to evaluate the combined LCU aggregates and recalculates the fourth dimension (the Rule Evaluation LCU).

But, before that …

The Full Database Model

So, instead of having to comb through the previous posts and cobble together the database. I think the database schema is generic enough that it’s fine to share all of it. So …

• Full database object script

New Additions:

• ALBGrouping
  
  This is the grouping table that the script/program will populate.
  
• vw_ALB*
  
  These use the ALBGrouping table recalculate the LCUs.
  
• usp_Aggregate_ALBLCUComparison_For_DateRange
  
  This combines all the aggregates (similar to vw_ProxySiteLCUComparison). But, the way the aggregation works, you can’t filter the result by Date. So, I needed a way to pass a start and end date to filter the results.

ALB Grouping Results

Wow! It’s actually cheaper to go with a single ALB rather than even two ALBs. It way cheaper than creating individual ALBs for sites with more than 10 sub-applications.

I wish I had more confidence in these numbers. But, there’s a really good chance I’m not calculating the original LCU statistics correctly. But, I think they should be in the ball park.

I have it display statistics on it’s internal trials before settling on a final value. And, from the internal trials, it looks like the least expensive options is actually using a single ALB ($45.71)!

Next Up, AWS ALB Price Planning w/ IIS : Add IisLogWebAppId.

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

view raw Calculate-AwsAlbSorting.ps1 hosted with ❤ by GitHub

AWS ALB Price Planning w/ IIS : Rule Evaluations Pt. 2

This post continues the series from AWS ALB Price Planning w/ IIS : Rule Evaluations.

In the last post, I looked at the basics of pricing a single site on an ALB server. This time I’m going to dig in a little further into how to group multiple sites onto multiple ALB servers. This would be to allow a transition from a single IIS proxy server to multiple ALB instances.

Background

• The IIS proxy server hosts 73 websites with 233 web applications.
• Any site with 8 or more web applications within it will be given it’s own ALB server. The LCU cost of having over 10 rule evaluations on a single ALB are so dominant that it’s best to cut the number of rules you have to less than 10.
• Of the 73 websites, only 6 sites have 8 or more web applications within them. Leaving 67 other websites containing 103 web applications.

Findings from last time

I looked at grouping by number or rules and by average request counts.

If you have figured out how to get all the import jobs, tables, and stored procedures setup from the last couple posts then you are amazing! I definitely left out a number of scripts for database objects and some of the scripts have morphed throughout the series. But, if you were able to get everything setup, here’s a nice little view t0 help look at the expenses of rule evaluation LCUs.

Just like in the last post, there is a section at the bottom to get a more accurate grouping.

Simple Grouping

To do the simple groupings, I’m first going to generate some site statistics, usp_Regenerate_ProxySiteRequestStats. They really aren’t useful, but they can give you something to work with.

You can combine those stats with the WebAppCounts and use them as input into a PowerShell function. This PowerShell function attempts to:

• Separate Single Host ALBs from Shared Host ALBs
• $singleSiteRuleLimit sets the minimum number of sub-applications a site can have before it is required to be on it’s on ALB
• Group Host names into ALBs when possible
• It creates a number of shared ALBs (“bags”) which it can place sites into.
• It uses a bit of an elevator algorithm to try and evenly distribute the sites into ALBs.
• Enforce Rule Limits
• Unfortunately, elevator algorithms aren’t great at finding a good match every time. So, if adding a site to a bag would bring the total number of evaluation rules over $sharedSiteRuleLimit, then it tries to fit the site into the next bag (and so on).
• Give Options for how the sites will be prioritized for sorting
• Depending on how the sites are sorted before going into the elevator algorithm you can get different results. So, $sortByOptions lets you choose a few ways to sort them and to see the results of each options side by side.

The results look something like this:

So, sort by WebAppCount (ie. # of sub-applications) got it down to 19 ALBs. That’s 6 single ALBs and 13 shared ALBs.

Conclusion:

The cost of 19 ALBs without LCU charges is $307.80 per month ($0.0225 ALB per hour * 24 hours * 30 days * 19 ALBs). Our current IIS proxy server, which can run on a t2.2xlarge EC2 image, would cost $201.92 per month on a prepaid standard 1-year term.

The Sorting PowerShell Script

	<# SEE SETTINGS BELOW #>
	# pull in the data from the database
	[void][Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo");
	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
	}
	$countsQuery = @"
	select s.*, c.WebAppCount
	from dbo.ProxySiteRequestStats s
	inner join dbo.ProxyWebAppCounts c on s.SiteName = c.SiteName
	"@
	$siteCounts = Invoke-SqlCmd -Server "BGLR382,1433" -Database IisLogs -Query $countsQuery
	<############# SETTINGS #################>
	# number of rules/sub-applications to select a site to have it's own ALB
	$singleSiteRuleLimit = 8
	# maximum number of rules on a shared site.
	$sharedSiteRuleLimit = 10
	# 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 = 10 # minimum 3
	<############# END OF SETTINGS ##########>
	# calculate everything
	$singles = $siteCounts |? { $_.WebAppCount -gt $singleSiteRuleLimit }
	$shared = $siteCounts |? { $_.WebAppCount -le $singleSiteRuleLimit }
	#$singles | ft -AutoSize
	#$shared | ft -AutoSize
	$albSortedOptions = @{}
	foreach($sortBy in $sortByOptions) {
	$albs = @{ Cnt = 0 }
	foreach($site in $singles) {
	$albs."$($albs.Cnt)" = @($site)
	$albs.Cnt++
	}
	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
	$bi += $increment
	}
	return $results
	}
	$currentBagCount = $minimumBagCount
	$continueSorting = $true
	while($continueSorting) {
	$sortedBags = Sort-IntoBags -BaseCount ($Albs.Cnt - 1) `
	-SharedSites $shared `
	-BagCount $currentBagCount `
	-SortBy $sortBy `
	-RuleLimit $sharedSiteRuleLimit
	# check that no single ALB has too many rules on it
	$continueSorting = $false
	for($j = $Albs.Cnt; $j -lt $sortedBags.Cnt; $j++) {
	$numberOfRules = ($sortedBags."$j".WebAppCount | Measure-Object -Sum).Sum
	if($numberOfRules -gt $sharedSiteRuleLimit) {
	$currentBagCount++
	$continueSorting = $true
	}
	}
	}
	# add the sorted bag results into the albs
	for($j = $albs.Cnt; $j -lt $sortedBags.Cnt; $j++) {
	$albs."$j" = $sortedBags."$j"
	$albs.Cnt++
	}
	$albStats = @()
	for($j = 1; $j -lt $albs.Cnt; $j++) {
	$alb = $albs."$j"
	$props = @{
	Id = $j
	SiteNames = $alb.SiteName
	SiteCount = ($alb.WebAppCount | Measure-Object -Sum).Count
	RuleCount = ($alb.WebAppCount | Measure-Object -Sum).Sum
	AvgRequestCount = ($alb.Avg | Measure-Object -Sum).Sum
	}
	$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, SiteNames
	}

view raw Sort-SitesIntoALBs.ps1 hosted with ❤ by GitHub

How to get more accurate groupings:

• Instead of generating hourly request statistics based upon Date, Time, and SiteName; the hourly request statistics need to be based upon Date, Time, SiteName, and AppPath. To do this you would need to:
• Assign a WebAppId to the dbo.ProxyWebApps table
• Write a SQL query that would use the dbo.ProxyWebApps data to evaluate all requests in dbo.IisLogs an assign the WebAppId to every request
• Regenerate all hourly statistics over ALL dimensions using Date, Time, SiteName and AppPath.
• Determine a new algorithm for ALB groupings that would attempt to make the number of rules in each group 10. But, the algorithm should leave extra space for around 1000~1500 (1.0~1.5 LCU) worth of requests per ALB. The applications with the lowest number of requests should be added to the ALBs at this point.
• You need to ensure that all applications with the same SiteName have to be grouped together.
• The base price per hour for an ALB is around 2.8 LCU. So, if you can keep this dimension below 2.8 LCU, it’s cheaper to get charged for the rule evaluations than to create a new ALB.

Next Up, AWS ALB Price Planning W/ IIS : Grouped Sites.

AWS ALB Price Planning w/ IIS : Rule Evaluations

This post continues the series from AWS ALB Price Planning w/ IIS : Bandwidth. Here are couple things to note about the query to grab Rule Evaluations:

• This can either be the most straightforward or most difficult dimension to calculate. For me, it was the most difficult.
• The IIS logs I’m working have 73 distinct sites (sometimes referred to as DNS host names or IP addresses). But there are 233 web applications spread across those 73 distinct sites. An ALB is bound to an IP address, so all of the sub-applications under a single site will all become rules within that ALB. At least, this is the way I’m planning on setting things up. I want every application/rule under a site to be pointed at a separate target server list.
• An important piece of background information is that the first 10 rule evaluations on a request are free. So, if you have less than 10 rules to evaluate on an ALB, you will never get charged for this dimension.
• Another important piece of information: Rules are evaluated in order until a match is found. So, you can put heavily used sub-applications at the top of the rules list to ensure they don’t go over the 10 free rule evaluation per request limit.
• However, you also need to be aware of evaluating rules in the order of “best match”. For example, you should place “/webservices/cars/windows” before “/webservices/cars”, because the opposite ordering would send all requests to /webservices/cars.
• The point being, you can tweak the ordering of the rules to ensure the least used sub-application is the only one which goes over the 10 free rule evaluations limit.

With all that background information, the number of rule evaluations are obviously going to be difficult to calculate. And, that’s why I fudged the numbers a lot. If you want some ideas on how to make more accurate predictions please see the notes at the bottom.

Here were some assumptions I made up front:

• If the site has over 8 sub-applications, that site should have it’s own ALB. It should not share that ALB with another site. (Because the first 10 rule evaluations are free.)
• All sites with less than 8 sub-applications should be grouped onto shared ALBs.
• For simplicity the groupings will be based on the number of rule evaluations. The number of requests for each sub-applications will not be used to influence the groupings.

Findings

Here were my biggest take aways from this:

• When an ALB is configured with more than the 10 free rule evaluations allowed, the rule evaluation LCUs can become the most dominant trait. But, that only occurs if number of requests are very high and the ordering of the rules is very unfavorable.
• The most influential metric on the LCU cost of a site is the number of requests it receives. You really need a high traffic site to push the LCU cost.
• As described in the “How to get more accurate numbers” section below. The hourly base price of an ALB is $0.0225 per hour. The hourly LCU price is $0.008. So, as long as you don’t spend over 2.8 LCU per hour; it’s cheaper to bundle multiple sites onto a single ALB rather than make a new one.

To demonstrate this, here was the second most heavily “requested” site. That site has 22 sub-applications. I used some gorilla math and came up with a statement of “on average there will be 6 rule evaluations per request” ((22 sub-applications / 2) – (10 free requests / 2)). Looking at August 1st 2018 by itself, the Rule Evaluations LCU was always lower the amount of Bandwidth used.

How to Gather the Data

Since I wanted every application under a site to need a rule; I first needed to get the number of web applications on the IIS server. I do not have that script attached. You should be able to write something using the WebAdministration or IISAdministration powershell modules. I threw those values into a very simple table:

	USE [IisLogs]
	GO
	/****** Object: Table [dbo].[ProxyWebApps] Script Date: 9/2/2018 6:48:43 PM ******/
	SET ANSI_NULLS ON
	GO
	SET QUOTED_IDENTIFIER ON
	GO
	CREATE TABLE [dbo].[ProxyWebApps](
	[SiteName] [varchar](255) NOT NULL,
	[AppPath] [varchar](255) NOT NULL,
	CONSTRAINT [PK_ProxyWebApps] PRIMARY KEY CLUSTERED
	(
	[SiteName] ASC,
	[AppPath] ASC
	)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
	) ON [PRIMARY]
	GO
	USE [IisLogs]
	GO
	/****** Object: Table [dbo].[ProxyWebAppCounts] Script Date: 9/2/2018 6:49:53 PM ******/
	SET ANSI_NULLS ON
	GO
	SET QUOTED_IDENTIFIER ON
	GO
	CREATE TABLE [dbo].[ProxyWebAppCounts](
	[SiteName] [varchar](255) NOT NULL,
	[WebAppCount] [int] NOT NULL,
	CONSTRAINT [PK_ProxyWebAppCounts] PRIMARY KEY CLUSTERED
	(
	[SiteName] ASC
	)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
	) ON [PRIMARY]
	GO

view raw dbo.ProxyWebApps.sql hosted with ❤ by GitHub

Once you get your data into dbo.ProxyWebApps, you can populate dbo.ProxyWebAppCounts easily with:

	insert into [dbo].[ProxyWebAppCounts]
	select SiteName, count(*) [WebAppCount]
	from [dbo].[ProxyWebApps]

view raw Populate-ProxyWebAppCounts.sql hosted with ❤ by GitHub

Now, we need to calculate the number of requests per application for each hour.

	USE [IisLogs]
	GO
	/****** Object: Table [dbo].[ProxyWebAppCounts] Script Date: 9/2/2018 6:54:29 PM ******/
	CREATE TABLE [dbo].[ProxyWebAppCounts](
	[SiteName] [varchar](255) NOT NULL,
	[WebAppCount] [int] NOT NULL,
	CONSTRAINT [PK_ProxyWebAppCounts] PRIMARY KEY CLUSTERED
	(
	[SiteName] ASC
	)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
	) ON [PRIMARY]
	GO
	## generate the data
	declare @dateStart varchar(10) = '2018-08-18',
	@dateEnd varchar(10) = '2018-08-19',
	@includeAll bit = 1
	insert into dbo.ProxySiteRequestCounts
	select convert(date, EntryTime) [date]
	,format(datepart(hour, EntryTime), '0#') + ':00:00' [time]
	,SiteName
	,count(*) [RequestCounts]
	from dbo.IisLog
	where (EntryTime >= @dateStart and EntryTime < @dateEnd)
	or @includeAll = 1
	group by convert(date, EntryTime),
	datepart(hour, EntryTime),
	SiteName
	order by [date], [time], SiteName;

view raw Generate-ProxyWebAppCounts.sql hosted with ❤ by GitHub

And, finally, generate the LCUs for rule evaluations and compare it with the LCU values from the previous dimensions:

• usp_Calculate_ProxySiteNewConnections_For_Date.sql
• usp_Calculate_ProxySiteActiveConnections_For_Date.sql
• usp_Calculate_ProxySiteBandwidth_For_Date.sql
• usp_Calculate_ProxySiteRuleEvaluations_For_Date.sql

	select s.[Date], s.[Time], s.[SiteName]
	,c.WebAppCount
	,((((c.WebAppCount/2) - 5) * 1.0 * s.RequestCounts) / 3600) [rule-evals-per-hour]
	,convert(decimal(4,2), ((((c.WebAppCount/2) - 5) * 1.0 * s.RequestCounts) / 3600) / 1000) [rule-eval-lcus]
	--,(((c.WebAppCount - 10) * s.RequestCounts) / 1000) * 0.008 [dollars-per-hour]
	,n.lcus [new-conn-lcus]
	,a.lcus [active-conn-lcus]
	,b.lcus [bandwidth-lcus]
	,(select max(v) from (values (n.lcus), (a.lcus), (b.lcus), (convert(decimal(4,2), ((((c.WebAppCount/2) - 5) * 1.0 * s.RequestCounts) / 3600) / 1000)) as value(v)) as [max-lcus]
	,case when (select max(v) from (values (n.lcus), (a.lcus), (b.lcus), (convert(decimal(4,2), ((((c.WebAppCount/2) - 5) * 1.0 * s.RequestCounts) / 3600) / 1000)) as value(v)) = convert(decimal(4,2), ((((c.WebAppCount/2) - 5) * 1.0 * s.RequestCounts) / 3600) / 1000) then 1 else 0 end as [rule-eval-used]
	,case when (select max(v) from (values (n.lcus), (a.lcus), (b.lcus), (convert(decimal(4,2), ((((c.WebAppCount/2) - 5) * 1.0 * s.RequestCounts) / 3600) / 1000)) as value(v)) = n.lcus then 1 else 0 end as [new-conn-used]
	,case when (select max(v) from (values (n.lcus), (a.lcus), (b.lcus), (convert(decimal(4,2), ((((c.WebAppCount/2) - 5) * 1.0 * s.RequestCounts) / 3600) / 1000)) as value(v)) = a.lcus then 1 else 0 end as [active-conn-used]
	,case when (select max(v) from (values (n.lcus), (a.lcus), (b.lcus), (convert(decimal(4,2), ((((c.WebAppCount/2) - 5) * 1.0 * s.RequestCounts) / 3600) / 1000)) as value(v)) = b.lcus then 1 else 0 end as [bandwidth-used]
	from dbo.ProxySiteRequestCounts s
	inner join dbo.ProxyWebAppCounts c on s.SiteName = c.SiteName
	inner join dbo.ProxySiteNewConnections n on s.[Date] = n.[Date] and s.[Time] = n.[Time] and s.[SiteName] = n.[SiteName]
	inner join dbo.ProxySiteActiveConnections a on s.[Date] = a.[Date] and s.[Time] = a.[Time] and s.[SiteName] = a.[SiteName]
	inner join dbo.ProxySiteBandwidth b on s.[Date] = b.[Date] and s.[Time] = b.[Time] and s.[SiteName] = b.[SiteName]
	where 1 = 1
	--and c.WebAppCount > 10
	--and s.SiteName = 'site1.your.site.com'
	--and s.[Date] >= '2018-08-01' and s.[Date] < '2018-09-01'

view raw Generate-RuleEvaluationLCUs.sql hosted with ❤ by GitHub

How to get more accurate numbers:

• Instead of generating hourly request statistics based upon Date, Time, and SiteName; the hourly request statistics need to be based upon Date, Time, SiteName, and AppPath. To do this you would need to:
• Assign a WebAppId to the dbo.ProxyWebApps table
• Write a SQL query that would use the dbo.ProxyWebApps data to evaluate all requests in dbo.IisLogs an assign the WebAppId to every request
• Regenerate all hourly statistics over ALL dimensions using Date, Time, SiteName and AppPath.
• Determine a new algorithm for ALB groupings that would attempt to make the number of rules in each group 10. But, the algorithm should leave extra space for around 1000~1500 (1.0~1.5 LCU) worth of requests per ALB. The applications with the lowest number of requests should be added to the ALBs at this point.
• You need to ensure that all applications with the same SiteName have to be grouped together.
• The base price per hour for an ALB is around 2.8 LCU. So, if you can keep this dimension below 2.8 LCU, it’s cheaper to get charged for the rule evaluations than to create a new ALB.

Next Up, AWS ALB Price Planning w/ IIS : Rule Evaluations Pt. 2.

AWS ALB Price Planning w/ IIS : Bandwidth

This post continues the series about from AWS ALB Price Planning w/ IIS : Active Connections. Here are couple things to note about the query to grab Bandwidth:

• This is one thing that IIS logs can accurately evaluate. You can get the number of bytes sent and received through an IIS/ARR proxy server by turning on the cs-bytes and sc-bytes W3C logging values. (see screen shot below)
• AWS does that pricing based on average usage per hour. So, the sql will aggregate the data into hour increments in order to return results.

Bandwidth SQL Script

	use IisLogs
	go
	/******************************************************************
	AWS ALB DIMENSION: Mbps per Hour
	******************************************************************/
	declare @dateStart varchar(10) = '2018-07-18',
	@dateEnd varchar(10) = '2018-07-19',
	@includeAll bit = 0,
	@intervalInSeconds int = 1
	create table #data
	(
	[date] date,
	[time] time,
	[avg-in-bits-per-second-per-hour] decimal
	)
	insert into #data
	select [date], [time], sum([sum-in-bits-per-second]) / 3600 [avg-bits-per-second-per-hour]
	from (
	select convert(date, EntryTime) [date]
	,format(datepart(hour, EntryTime), '0#') + ':00:00' [time]
	,sum(convert(decimal, csBytes + scBytes) * 8) [sum-in-bits-per-second]
	from dbo.IisLog
	where (EntryTime >= @dateStart and EntryTime < @dateEnd)
	or @includeAll = 1
	group by convert(date, EntryTime),
	datepart(hour, EntryTime),
	datepart(minute, EntryTime),
	datepart(second, EntryTime) / @intervalInSeconds
	) f
	group by [date], [time]
	-- 2327838.72 = 2.22 Mbps
	select [date], [time], [avg-in-bits-per-second-per-hour],
	convert(decimal(4,2), ([avg-in-bits-per-second-per-hour] / 2327838.72)) [lcus]
	from #data
	order by [date], [time]
	select avg(convert(decimal(4,2), ([avg-in-bits-per-second-per-hour] / 2327838.72))) [avg-lcus]
	from #data
	--drop table #data;

view raw AWS_ALB_Bandwidth.sql hosted with ❤ by GitHub

Graphing the output from the script shows:

• Mbps per Hour (for a month)
• The jump in the average number of new connections in the beginning of the month corresponded to a return of students to campus. During the beginning of the month, school was not in session and then students returned.
• The dip at the end of the month has to do with a mistake I made loading some data. There is one of data that I forgot to import the IIS logs, but I don’t really want to go back and correct the data. It will disappear from the database in about a month.
• Mbps per Hour LCUs
• This is a critical number. We put 215+ websites through the proxy server. The two AWS ALB dimensions that will have the biggest impact on the price (the number of LCUs) will be the Bandwidth usage and the Rule Evaluations.
• I’m very surprised that the average LCUs per hour for a month is around 2.3 LCUs, which is very low.

Next Up, AWS ALB Price Planning w/ IIS : Rule Evaluations.