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