Build a Local Application Support Lab

Deploy a .NET app on IIS and track incidents with SQL Server.

Introduction

30 Second Summary

When an application goes down at 2 AM, someone needs to know exactly what to check and how to fix it. That someone is an Application Support Engineer.

In this project, you will build a local operations lab that mirrors a real production support environment. You will deploy a web application, set up an incident tracking database, analyze logs, and create an operational runbook.

What You'll Build

A complete local Application Support Lab with a live web application on IIS, a SQL Server incident tracking database, analysis queries, and a troubleshooting runbook.

By the end of this project, you'll have:

  • A live ASP.NET Core web app deployed and running on IIS at http://localhost.
  • A SQL Server Express database with incident tracking tables and realistic sample data.
  • SQL queries for incident analysis and an operational runbook documenting your troubleshooting procedures.
  • Secret Mission: Add automated health monitoring with a PowerShell script that detects outages and logs incidents to your database.

Do I need Windows for this project?

Yes. This project uses IIS, SQL Server Express, and Windows Event Viewer, which are all Windows-only tools. You need a Windows 10 or Windows 11 machine to complete it.

No prior experience is required. This guide walks you through installing and setting up every tool from scratch.

Install the .NET 8 SDK and SQL Server Express

Every operations lab needs the right tools before anything can run. Your lab depends on two core pieces of software to function.

The .NET 8 SDK lets you build and publish web applications. SQL Server 2022 Express gives you a local database for incident tracking.

In this step, get ready to:
  • Install the .NET 8 SDK.
  • Verify .NET is working from the command line.
  • Install SQL Server 2022 Express.
Download and install the .NET 8 SDK

The .NET 8 SDK is the toolkit that lets you create, build, and publish ASP.NET Core web applications. You will deploy one of these applications to IIS in a later step.

  • Open your browser and navigate to the .NET 8 download page: https://dotnet.microsoft.com/download/dotnet/8.0
  • Under the SDK 8.0.x section, click the Windows x64 installer link to download it.
  • Run the downloaded installer file.
  • Windows will show a User Account Control (UAC) prompt asking for permission. Click Yes to allow the installation.
  • Click Install and wait for the installation to complete.
  • Click Close when the installer finishes.

What is the .NET SDK?

SDK stands for Software Development Kit. The .NET 8 SDK includes everything you need to build .NET applications: the compiler, the runtime, and command-line tools.

In production environments, servers only need the runtime (a smaller install). On your development machine, you need the full SDK to build and publish apps.

Verify the .NET 8 SDK installation

The installer adds the dotnet command to your system PATH. You need to open a fresh Command Prompt to pick up the change.

  • Press the Windows key to open your search bar.
  • Type Command Prompt and press Enter to open it.
  • Verify .NET is installed by running this command:
dotnet --version

Check your output and pick the tab that matches what you see:

✔️ I see a version starting with 8.0

You're all set. The .NET 8 SDK is installed and ready to use.

ⓧ I see a different version number

You may have an older .NET SDK installed that is taking precedence. Go back to the download page and confirm you downloaded from the .NET 8.0 section specifically.

  • Re-download and run the .NET 8 SDK installer from the link above.
  • Close your current Command Prompt and open a new one.
  • Run dotnet --version again. You should now see a version starting with 8.0.

ⓧ 'dotnet' is not recognized

This means your system PATH was not updated. This usually happens when you did not close and reopen Command Prompt after installing.

  • Close your Command Prompt window completely.
  • Open a brand new Command Prompt (press the Windows key, type Command Prompt, press Enter).
  • Run dotnet --version again.
  • If it still does not work, re-run the .NET 8 SDK installer from the download page and restart your computer afterwards.
Install SQL Server 2022 Express

SQL Server 2022 Express is a free edition of Microsoft's database engine. You will use it to store and query incident tracking data throughout this project.

  • Navigate to the SQL Server 2022 Express download page: https://www.microsoft.com/en-us/download/details.aspx?id=104781
  • Click Download to get the SQL2022-SSEI-Expr.exe installer file.
  • Run SQL2022-SSEI-Expr.exe.
  • The installer will ask you to choose an installation type. Select Basic.
  • Accept the license terms by clicking Accept.
  • Leave the install location as the default and click Install.
  • Wait for the installation to complete. This may take a few minutes.

When the installation finishes, you should see a summary screen showing the instance name localhost\SQLEXPRESS and connection details.

Why SQL Server Express?

SQL Server Express is the same database engine used in enterprise environments. It runs locally with no cloud account required.

The Express edition is limited to 1 physical processor, 1 GB of memory, and 10 GB of storage per database. For a local lab, these limits are more than enough.

Installation stuck or failing?

Make sure you selected Basic as the installation type. The Custom option downloads a much larger installer and requires more configuration.

If the download within the installer stalls, check your internet connection and try again. The Basic install downloads approximately 280 MB of files.

help me troubleshoot my SQL Server 2022 Express installation

Your core tools are installed and ready. Next up, you will install SSMS for managing your database and enable IIS for hosting your web application.

Install SSMS and Enable IIS

You have the .NET SDK and SQL Server Express installed. Now you need two more tools to complete your operations lab.

SQL Server Management Studio (SSMS) gives you a graphical interface to write queries and manage your database. IIS (Internet Information Services) is the web server built into Windows that will host your application.

In this step, get ready to:
  • Install SQL Server Management Studio 22.
  • Enable IIS through Windows Features.
  • Verify IIS is running by visiting the default welcome page.
Download and install SSMS 22

SSMS is how you will interact with your incident tracking database. It lets you write SQL queries, inspect tables, and monitor database health.

  • Open your browser and navigate to the SSMS download page:
  • https://aka.ms/ssms
  • Click the download link for SQL Server Management Studio 22. This downloads a small file called vs_SSMS.exe to your Downloads folder.
  • Open your Downloads folder and double-click vs_SSMS.exe to start the installation.
  • If a User Account Control prompt appears, click Yes.
  • The Visual Studio Installer window opens showing SSMS as a product to install. Click Install.
  • Wait for the progress bar to complete. This may take several minutes depending on your internet speed.

Why does SSMS use the Visual Studio Installer?

Starting with SSMS 22, Microsoft switched to the Visual Studio Installer engine instead of a standalone MSI. The download page gives you a small bootstrapper file (vs_SSMS.exe) that launches this installer.

The experience looks like a Visual Studio setup, but it installs SSMS in its own location. You do not need Visual Studio installed.

Once the installation completes, you should see a success message in the installer window.

Installer stuck or failing to download?

Make sure you have a stable internet connection. The bootstrapper downloads several hundred megabytes during installation.

If the installer fails partway through, close it and re-run vs_SSMS.exe from your Downloads folder. It will pick up where it left off.

Help me troubleshoot the SSMS installer.

Enable IIS through Windows Features

IIS is Windows' built-in web server. It is not enabled by default on desktop editions of Windows, so you need to turn it on manually through the Windows Features panel.

  • Press the Windows key to open the search bar.
  • Type Turn Windows features on or off and press Enter.
  • In the Windows Features dialog, find and check the box next to Internet Information Services.
  • Expand Internet Information Services by clicking the plus icon next to it.
  • Expand Web Management Tools.
  • Check the box next to IIS Management Console.
  • Click OK and wait for Windows to apply the changes. A progress bar will appear while the features are being installed.

What did you just enable?

Checking Internet Information Services enables the core web server that listens for HTTP requests on your machine. The IIS Management Console gives you a graphical tool to configure sites, application pools, and bindings.

In a real production environment, L1 engineers use IIS Manager daily to restart application pools, check site status, and review bindings.

Windows asks you to restart?

Some Windows configurations require a restart after enabling IIS. If Windows prompts you to restart, go ahead and restart your computer. After rebooting, continue with the next substep.

If no restart prompt appears, you can continue immediately.

Help me troubleshoot enabling IIS on Windows.

Verify IIS is running
  • Open your web browser.
  • Type http://localhost in the address bar and press Enter.

You should see the default IIS welcome page. This is a blue page with the IIS logo confirming that your web server is up and running.

Not seeing the IIS welcome page?

Make sure you completed the Windows Features installation. If you see an error about not being able to reach the page, the IIS service may not have started yet.

Try restarting your computer if you skipped a restart prompt earlier. After restarting, navigate to http://localhost again.

Help me get IIS running on localhost.

Your tools are all set. You have SSMS ready for database management and IIS serving web pages. Next up, you will deploy your own .NET web application to IIS and see it live in the browser.

Deploy a .NET Web App to IIS

Your environment is ready with IIS running and SQL Server installed. Now it's time to deploy an actual web application that IIS will serve to users.

In this step, you'll create a minimal ASP.NET Core web application, publish it to your local IIS server, and configure IIS to serve it on a dedicated port. This is the same deployment workflow used in production environments.

In this step, get ready to:
  • Create an ASP.NET Core web application.
  • Install the .NET 8 Hosting Bundle for IIS integration.
  • Configure an IIS website to serve your application on port 8080.
Create and publish the web application

You'll use the .NET CLI to scaffold a minimal web app, customize its response message, and publish the compiled output to a folder that IIS can serve.

  • Open an elevated Command Prompt. Press the Windows key to open your search bar.
  • Type cmd, then right-click Command Prompt and select Run as administrator.
  • Move to your Desktop by running this command:
cd %USERPROFILE%\Desktop
  • Create the web application project by running this command:
dotnet new web -o SupportLabApp

What does this command do?

The dotnet new web command creates a minimal ASP.NET Core web application. The -o SupportLabApp flag tells it to create a new folder called SupportLabApp and place the project files inside it.

  • Move into your new project folder by running:
cd SupportLabApp
  • Confirm the project files exist by running:
dir

You should see Program.cs and SupportLabApp.csproj listed in the output.

The generated Program.cs returns a default "Hello World!" message. You'll customize it to identify your SupportLab application.

  • Open Program.cs in Notepad by running:
notepad Program.cs
  • In Notepad, find this line:
app.MapGet("/", () => "Hello World!");
  • Replace "Hello World!" with "Hello World! - SupportLab Application v1.0", so the line now looks like this:
app.MapGet("/", () => "Hello World! - SupportLab Application v1.0");
  • Save the file in Notepad by pressing Ctrl+S, then close Notepad.
  • Publish the application to the IIS content folder by running this command:
dotnet publish -c Release -o C:\inetpub\wwwroot\SupportLabApp

What does this command do?

The dotnet publish command compiles your application into optimized files ready for deployment. The -c Release flag uses the Release configuration for production performance.

The -o flag specifies the output directory. You're publishing directly to C:\inetpub\wwwroot\SupportLabApp, which is where IIS expects to find website content.

You should see a "Publish succeeded" message in your Command Prompt output.

Seeing an access denied error?

The C:\inetpub\wwwroot folder requires administrator permissions. Make sure you opened Command Prompt with Run as administrator. Close your current Command Prompt, right-click it, select Run as administrator, then navigate back with cd %USERPROFILE%\Desktop\SupportLabApp and retry the publish command.

Help me fix this access denied error

✔️ Awesome, I've got everything!

Great. Double check you've saved Program.cs and that the publish command succeeded.

ⓧ I'd like to double check the full code

Here is what your Program.cs should look like:

var builder = WebApplication.CreateBuilder(args);
var app = builder.Build();

app.MapGet("/", () => "Hello World! - SupportLab Application v1.0");

app.Run();
Install the .NET 8 Hosting Bundle

IIS cannot run ASP.NET Core applications on its own. It needs the .NET Hosting Bundle, which installs the ASP.NET Core Module that connects IIS to your .NET app.

  • Open your browser and navigate to the .NET 8 download page:
  • https://dotnet.microsoft.com/download/dotnet/8.0
  • Scroll down to the ASP.NET Core Runtime 8.0 section.
  • Under Run apps, find and click the Hosting Bundle download link.
  • Run the downloaded installer and follow the prompts to complete the installation.

What is the Hosting Bundle?

The .NET Hosting Bundle installs the .NET Runtime, the .NET Library, and the ASP.NET Core Module. The ASP.NET Core Module is what allows IIS to forward requests to your .NET application.

You must install the Hosting Bundle after IIS is already enabled. If you install it before IIS, the ASP.NET Core Module registration will not work properly.

  • After installation completes, restart IIS to pick up the new module. Run this command in your elevated Command Prompt:
iisreset

You should see messages confirming that Internet services were stopped and restarted successfully.

Seeing 'iisreset' is not recognized?

Make sure your Command Prompt is running as administrator. The iisreset command requires elevated privileges. Close and reopen Command Prompt using Run as administrator.

Help me troubleshoot the iisreset command

Configure the IIS site

With the application published and the Hosting Bundle installed, you'll now tell IIS where to find your app and which port to serve it on.

  • Open IIS Manager. Press the Windows key to open your search bar.
  • Type IIS and click Internet Information Services (IIS) Manager.
  • In the Connections pane on the left, right-click the Sites node.
  • Click Add Website.
  • In the Site name field, type SupportLabApp.
  • In the Physical path field, enter C:\inetpub\wwwroot\SupportLabApp.
  • In the Port field, change the value from 80 to 8080.
  • Click OK.

Why port 8080?

The Default Web Site already uses port 80. Using port 8080 for your SupportLabApp avoids a port conflict so both sites can run simultaneously.

  • Open your browser and navigate to http://localhost:8080.

You should see the text "Hello World! - SupportLab Application v1.0" displayed in your browser.

Seeing an HTTP 500 error or a blank page?

  • Confirm the .NET 8 Hosting Bundle was installed after IIS was enabled, then run iisreset again in an elevated Command Prompt.
  • In IIS Manager, click Application Pools in the left pane. Find the SupportLabApp pool and confirm its .NET CLR Version is set to No Managed Code. ASP.NET Core apps require this setting because they bring their own runtime.
  • Check that the Physical path in your site settings matches C:\inetpub\wwwroot\SupportLabApp exactly.

Help me troubleshoot my IIS deployment

Your web application is now live on IIS. Next up, you'll create the incident tracking database that a support team would use alongside this application.

Create the Incident Tracking Database

Your web application is live on IIS. Now you need a place to track the incidents that application generates.

In a real operations team, every outage, error, and performance issue gets logged in a tracking system. You will build your own incident database in SQL Server and fill it with realistic support scenarios that mirror what L1 engineers handle daily.

In this step, get ready to:
  • Connect to SQL Server Express using SSMS.
  • Create the IncidentTracker database with Incidents and IncidentUpdates tables.
  • Insert sample incident data representing realistic L1 support scenarios.
Connect to SQL Server in SSMS

SSMS (SQL Server Management Studio) is the standard tool for managing SQL Server instances. You will use it to create your database and run queries throughout this project.

  • Press the Windows key to open your search bar.
  • Type SSMS and press Enter to open SQL Server Management Studio.
  • In the Connect to Server dialog that appears, set Server name to localhost\SQLEXPRESS.
  • Leave Authentication set to Windows Authentication.
  • Click Connect.

You should see the Object Explorer panel on the left populate with your server instance. This confirms you are connected to SQL Server Express.

What is Windows Authentication?

Windows Authentication lets you log in to SQL Server using the same credentials you use to sign in to your Windows computer. No separate username or password is needed because SQL Server trusts your Windows identity.

Can't connect to the server?

Make sure you typed localhost\SQLEXPRESS with a backslash, not a forward slash. Also confirm that the SQL Server (SQLEXPRESS) service is running by opening services.msc from the Start menu and checking its status.

Help me troubleshoot my SQL Server connection

Create the database and tables

Every incident tracking system needs a structured place to store records. You will create a database called IncidentTracker with two tables: one for incidents and one for the update history on each incident.

  • In SSMS, click New Query in the toolbar at the top.
  • Paste the following SQL into the query window to create the database and both tables:
-- Create the IncidentTracker database
CREATE DATABASE IncidentTracker;
GO

USE IncidentTracker;
GO

-- Create the Incidents table
CREATE TABLE Incidents (
    IncidentID INT IDENTITY(1,1) PRIMARY KEY,
    Title NVARCHAR(200) NOT NULL,
    Description NVARCHAR(MAX),
    Category NVARCHAR(50) NOT NULL,
    Priority NVARCHAR(20) NOT NULL,
    Status NVARCHAR(30) NOT NULL DEFAULT 'Open',
    AssignedTo NVARCHAR(100),
    CreatedDate DATETIME NOT NULL DEFAULT GETDATE(),
    ResolvedDate DATETIME NULL,
    ClientName NVARCHAR(100)
);
GO

-- Create the IncidentUpdates table
CREATE TABLE IncidentUpdates (
    UpdateID INT IDENTITY(1,1) PRIMARY KEY,
    IncidentID INT NOT NULL FOREIGN KEY REFERENCES Incidents(IncidentID),
    UpdateText NVARCHAR(MAX) NOT NULL,
    UpdatedBy NVARCHAR(100) NOT NULL,
    UpdatedDate DATETIME NOT NULL DEFAULT GETDATE()
);
GO

What does this script do?

CREATE DATABASE IncidentTracker creates a new database on your SQL Server instance. The USE statement switches context so all following commands run inside that database.

The Incidents table stores each issue with its priority, status, and who it is assigned to. The IncidentUpdates table tracks every action taken on an incident, linked by a foreign key. This lets you reconstruct the full resolution timeline for any issue.

  • Click Execute in the toolbar (or press F5) to run the script.
  • You should see "Commands completed successfully." in the Messages tab at the bottom of the query window.
  • In the Object Explorer on the left, right-click Databases and select Refresh. Expand IncidentTracker > Tables to confirm both tables appear.

Seeing a "database already exists" error?

This means you ran the script once before. Right-click IncidentTracker in Object Explorer, select Delete, check Close existing connections, click OK, then run the script again.

Help me fix my database creation error

Insert sample incident data

An empty database is not useful for practice. You will populate it with seven realistic incidents and eight timeline updates that represent the kinds of issues L1 support engineers handle: application crashes, database timeouts, and infrastructure warnings.

  • Open a new query window by clicking New Query in the toolbar again.
  • Paste the following SQL to insert the sample data:
USE IncidentTracker;
GO

-- Insert sample incidents
INSERT INTO Incidents (Title, Description, Category, Priority, Status, AssignedTo, CreatedDate, ClientName)
VALUES
('IIS Application Pool crash', 'The DefaultAppPool stopped unexpectedly causing HTTP 503 errors for all users.', 'Application', 'P1-Critical', 'Resolved', 'Ravi Kumar', '2026-06-01 08:15:00', 'GlobalTech Corp'),
('Database connection timeout', 'Users reporting slow page loads. SQL Server connection pool exhausted.', 'Database', 'P2-High', 'Open', 'Priya Sharma', '2026-06-03 14:30:00', 'FinanceFirst Ltd'),
('SSL certificate expiring', 'Production SSL certificate expires in 7 days. Renewal required.', 'Infrastructure', 'P2-High', 'In Progress', 'Amit Patel', '2026-06-04 09:00:00', 'GlobalTech Corp'),
('Slow response time on login page', 'Login page taking over 10 seconds to load during peak hours.', 'Application', 'P3-Medium', 'Open', NULL, '2026-06-04 16:45:00', 'RetailMax Inc'),
('Disk space warning on web server', 'C: drive is at 92% capacity. IIS logs consuming excessive space.', 'Infrastructure', 'P3-Medium', 'Open', 'Ravi Kumar', '2026-06-05 06:00:00', 'FinanceFirst Ltd'),
('Scheduled job failure', 'Nightly data sync job failed with error code 0x80040E14.', 'Database', 'P2-High', 'Resolved', 'Priya Sharma', '2026-06-02 03:00:00', 'RetailMax Inc'),
('HTTP 500 errors on checkout page', 'Intermittent 500 errors reported by end users during checkout.', 'Application', 'P1-Critical', 'Open', 'Amit Patel', '2026-06-05 10:20:00', 'RetailMax Inc');

-- Insert incident updates (timeline)
INSERT INTO IncidentUpdates (IncidentID, UpdateText, UpdatedBy, UpdatedDate)
VALUES
(1, 'Incident reported by monitoring alert. Application Pool DefaultAppPool stopped.', 'System Alert', '2026-06-01 08:15:00'),
(1, 'Restarted Application Pool. Service restored. Investigating root cause.', 'Ravi Kumar', '2026-06-01 08:22:00'),
(1, 'Root cause identified: unhandled exception in payment module. Escalated to dev team.', 'Ravi Kumar', '2026-06-01 09:00:00'),
(1, 'Dev team deployed hotfix. Monitoring for recurrence. Closing incident.', 'Ravi Kumar', '2026-06-01 14:30:00'),
(2, 'Users reporting timeouts. Checking SQL Server connection pool.', 'Priya Sharma', '2026-06-03 14:35:00'),
(2, 'Connection pool shows 95% utilization. Checking for blocking queries.', 'Priya Sharma', '2026-06-03 14:50:00'),
(6, 'Job failure detected in SQL Agent log. Error: invalid column name in sync query.', 'Priya Sharma', '2026-06-02 06:00:00'),
(6, 'Corrected column reference in sync stored procedure. Re-ran job successfully.', 'Priya Sharma', '2026-06-02 07:15:00');
GO

What does this data represent?

The seven incidents cover the most common categories an L1 engineer handles: application crashes (IIS pool failures, HTTP 500 errors), database issues (connection timeouts, failed jobs), and infrastructure warnings (disk space, expiring certificates).

The incident updates table records the timeline of actions taken. This is how operations teams track who did what and when, which is essential for root cause analysis and post-incident reviews.

  • Click Execute (or press F5) to run the insert script.
  • You should see "(7 rows affected)" and "(8 rows affected)" in the Messages tab.
  • Verify the data by running a quick check. Open another new query window and run:
USE IncidentTracker;
SELECT * FROM Incidents;

You should see a results grid with 7 rows, showing incidents ranging from P1-Critical application pool crashes to P3-Medium disk space warnings.

Seeing "Invalid object name 'Incidents'"?

Make sure the database dropdown at the top-left of your query window shows IncidentTracker, not master. If it shows a different database, select IncidentTracker from the dropdown and run the query again.

Help me fix my query error in SSMS

✔️ Awesome, I've got everything!

Great. Make sure you see 7 incident rows in the results grid and that the Messages tab confirmed both inserts completed successfully.

ⓧ I'd like to double check the full code

Here is the full database creation script:

-- Create the IncidentTracker database
CREATE DATABASE IncidentTracker;
GO

USE IncidentTracker;
GO

-- Create the Incidents table
CREATE TABLE Incidents (
    IncidentID INT IDENTITY(1,1) PRIMARY KEY,
    Title NVARCHAR(200) NOT NULL,
    Description NVARCHAR(MAX),
    Category NVARCHAR(50) NOT NULL,
    Priority NVARCHAR(20) NOT NULL,
    Status NVARCHAR(30) NOT NULL DEFAULT 'Open',
    AssignedTo NVARCHAR(100),
    CreatedDate DATETIME NOT NULL DEFAULT GETDATE(),
    ResolvedDate DATETIME NULL,
    ClientName NVARCHAR(100)
);
GO

-- Create the IncidentUpdates table
CREATE TABLE IncidentUpdates (
    UpdateID INT IDENTITY(1,1) PRIMARY KEY,
    IncidentID INT NOT NULL FOREIGN KEY REFERENCES Incidents(IncidentID),
    UpdateText NVARCHAR(MAX) NOT NULL,
    UpdatedBy NVARCHAR(100) NOT NULL,
    UpdatedDate DATETIME NOT NULL DEFAULT GETDATE()
);
GO

And here is the full sample data insertion script:

USE IncidentTracker;
GO

-- Insert sample incidents
INSERT INTO Incidents (Title, Description, Category, Priority, Status, AssignedTo, CreatedDate, ClientName)
VALUES
('IIS Application Pool crash', 'The DefaultAppPool stopped unexpectedly causing HTTP 503 errors for all users.', 'Application', 'P1-Critical', 'Resolved', 'Ravi Kumar', '2026-06-01 08:15:00', 'GlobalTech Corp'),
('Database connection timeout', 'Users reporting slow page loads. SQL Server connection pool exhausted.', 'Database', 'P2-High', 'Open', 'Priya Sharma', '2026-06-03 14:30:00', 'FinanceFirst Ltd'),
('SSL certificate expiring', 'Production SSL certificate expires in 7 days. Renewal required.', 'Infrastructure', 'P2-High', 'In Progress', 'Amit Patel', '2026-06-04 09:00:00', 'GlobalTech Corp'),
('Slow response time on login page', 'Login page taking over 10 seconds to load during peak hours.', 'Application', 'P3-Medium', 'Open', NULL, '2026-06-04 16:45:00', 'RetailMax Inc'),
('Disk space warning on web server', 'C: drive is at 92% capacity. IIS logs consuming excessive space.', 'Infrastructure', 'P3-Medium', 'Open', 'Ravi Kumar', '2026-06-05 06:00:00', 'FinanceFirst Ltd'),
('Scheduled job failure', 'Nightly data sync job failed with error code 0x80040E14.', 'Database', 'P2-High', 'Resolved', 'Priya Sharma', '2026-06-02 03:00:00', 'RetailMax Inc'),
('HTTP 500 errors on checkout page', 'Intermittent 500 errors reported by end users during checkout.', 'Application', 'P1-Critical', 'Open', 'Amit Patel', '2026-06-05 10:20:00', 'RetailMax Inc');

-- Insert incident updates (timeline)
INSERT INTO IncidentUpdates (IncidentID, UpdateText, UpdatedBy, UpdatedDate)
VALUES
(1, 'Incident reported by monitoring alert. Application Pool DefaultAppPool stopped.', 'System Alert', '2026-06-01 08:15:00'),
(1, 'Restarted Application Pool. Service restored. Investigating root cause.', 'Ravi Kumar', '2026-06-01 08:22:00'),
(1, 'Root cause identified: unhandled exception in payment module. Escalated to dev team.', 'Ravi Kumar', '2026-06-01 09:00:00'),
(1, 'Dev team deployed hotfix. Monitoring for recurrence. Closing incident.', 'Ravi Kumar', '2026-06-01 14:30:00'),
(2, 'Users reporting timeouts. Checking SQL Server connection pool.', 'Priya Sharma', '2026-06-03 14:35:00'),
(2, 'Connection pool shows 95% utilization. Checking for blocking queries.', 'Priya Sharma', '2026-06-03 14:50:00'),
(6, 'Job failure detected in SQL Agent log. Error: invalid column name in sync query.', 'Priya Sharma', '2026-06-02 06:00:00'),
(6, 'Corrected column reference in sync stored procedure. Re-ran job successfully.', 'Priya Sharma', '2026-06-02 07:15:00');
GO

Your incident database is loaded with realistic data. Next up, you will write SQL queries to analyze these incidents the same way an L1 engineer reviews their morning queue.

Analyze Incidents with SQL Queries

Your incident database is populated with realistic support data. Now you need to extract actionable information from it.

In a real operations role, you run SQL queries every day to check open incidents, generate reports for service reviews, and trace the full timeline of critical outages. In this step, you will write three analysis queries that mirror these daily tasks.

In this step, get ready to:
  • Query all open incidents sorted by priority.
  • Generate an incident summary grouped by category and status.
  • Trace the full timeline of a specific incident using a JOIN.
List all open incidents by priority

The first thing an L1 engineer does each morning is check which incidents are still open and which ones are the most critical. This query surfaces that exact view.

  • Switch back to SSMS where you are connected to localhost\SQLEXPRESS.
  • Click New Query in the toolbar to open a fresh query window.
  • Paste the following query to list all open incidents sorted by priority:
USE IncidentTracker;
GO

-- Query 1: All open incidents sorted by priority
SELECT 
    IncidentID,
    Title,
    Category,
    Priority,
    Status,
    AssignedTo,
    ClientName,
    CreatedDate,
    DATEDIFF(HOUR, CreatedDate, GETDATE()) AS HoursOpen
FROM Incidents
WHERE Status IN ('Open', 'In Progress')
ORDER BY 
    CASE Priority
        WHEN 'P1-Critical' THEN 1
        WHEN 'P2-High' THEN 2
        WHEN 'P3-Medium' THEN 3
        WHEN 'P4-Low' THEN 4
    END,
    CreatedDate ASC;
GO

What does this query do?

The WHERE Status IN ('Open', 'In Progress') clause filters out resolved incidents so you only see active work.

The CASE expression in the ORDER BY converts priority labels into numbers (P1 = 1, P2 = 2, etc.) so critical incidents appear first.

The DATEDIFF(HOUR, CreatedDate, GETDATE()) calculates how many hours each incident has been open. This helps you spot aging tickets that need escalation.

  • Click Execute in the toolbar (or press F5).
  • You should see 5 rows in the results grid. The P1-Critical incident (HTTP 500 errors on checkout page) appears at the top, followed by the P2-High and P3-Medium incidents.

Seeing zero rows?

Make sure you ran the insert-sample-data.sql script from the previous step. Without sample data, the query returns nothing.

Check that USE IncidentTracker; is at the top of your query. If you are running against the wrong database, you will get an error about the table not existing.

Help me troubleshoot my SQL query that returns no results.

Generate an incident summary report

In weekly service reviews, you present high-level metrics showing how many incidents hit each category. This summary query groups and counts incidents so you can spot patterns.

  • Place your cursor after the last GO statement in your query window.
  • Add the following query below it:
-- Query 2: Incident summary by category and status
SELECT 
    Category,
    Status,
    COUNT(*) AS IncidentCount,
    MIN(CreatedDate) AS OldestIncident,
    MAX(CreatedDate) AS NewestIncident
FROM Incidents
GROUP BY Category, Status
ORDER BY Category, Status;
GO

What does this query do?

The GROUP BY Category, Status clause collapses all rows into unique category-status combinations. COUNT(*) tells you how many incidents fall into each group.

The MIN(CreatedDate) and MAX(CreatedDate) columns show the date range per group. This helps you identify whether a category is experiencing a surge of new issues.

  • Highlight only Query 2 (from the comment line to GO) and click Execute.
  • You should see 7 rows. Each row is a unique combination of category and status with its incident count.

Run only part of a script

In SSMS, if you highlight a section of text before clicking Execute, only the highlighted portion runs. This lets you keep multiple queries in one file and run them independently.

Getting unexpected row counts?

If you ran the insert script more than once, you may have duplicate data. Run SELECT COUNT(*) FROM Incidents to check. If you see more than 7 rows, drop and recreate the database using your earlier scripts.

Help me fix duplicate data in my SQL Server database.

Trace the full timeline of an incident

When you investigate a past outage, you need to see every update in chronological order. This is called root cause analysis documentation. The query uses a JOIN to combine the Incidents and IncidentUpdates tables into a single timeline.

  • Place your cursor after the last GO statement.
  • Add the following query below it:
-- Query 3: Full incident timeline for a specific incident
SELECT 
    i.IncidentID,
    i.Title,
    i.Priority,
    i.Status,
    u.UpdateText,
    u.UpdatedBy,
    u.UpdatedDate
FROM Incidents i
INNER JOIN IncidentUpdates u ON i.IncidentID = u.IncidentID
WHERE i.IncidentID = 1
ORDER BY u.UpdatedDate ASC;
GO

What does this query do?

The INNER JOIN connects each incident to its updates using the shared IncidentID column. Only incidents that have updates appear in the results.

The aliases i and u are shorthand for the Incidents and IncidentUpdates tables. They make the query easier to read when referencing columns from both tables.

Ordering by u.UpdatedDate ASC gives you a chronological timeline showing exactly how the incident was handled from detection to resolution.

  • Highlight only Query 3 and click Execute.
  • You should see 4 rows showing the full story of the IIS Application Pool crash: from the initial alert, through the restart and root cause investigation, to the hotfix deployment.

Seeing zero rows for the timeline?

Make sure WHERE i.IncidentID = 1 references an incident that has updates. Only IncidentIDs 1, 2, and 6 have entries in the IncidentUpdates table.

If you get a syntax error, check that you included the table aliases (i and u) after each table name in the FROM and JOIN clauses.

Help me debug my SQL JOIN query.

  • Save the query file by pressing Ctrl+S.
  • In the Save As dialog, name the file analysis-queries.sql and save it to a location you can find later (your Desktop or project folder).

✔️ Awesome, I've got everything!

Great. Double check you have saved your file.

ⓧ I'd like to double check the full code

Here is the complete analysis-queries.sql file with all three queries:

USE IncidentTracker;
GO

-- Query 1: All open incidents sorted by priority
SELECT 
    IncidentID,
    Title,
    Category,
    Priority,
    Status,
    AssignedTo,
    ClientName,
    CreatedDate,
    DATEDIFF(HOUR, CreatedDate, GETDATE()) AS HoursOpen
FROM Incidents
WHERE Status IN ('Open', 'In Progress')
ORDER BY 
    CASE Priority
        WHEN 'P1-Critical' THEN 1
        WHEN 'P2-High' THEN 2
        WHEN 'P3-Medium' THEN 3
        WHEN 'P4-Low' THEN 4
    END,
    CreatedDate ASC;
GO

-- Query 2: Incident summary by category and status
SELECT 
    Category,
    Status,
    COUNT(*) AS IncidentCount,
    MIN(CreatedDate) AS OldestIncident,
    MAX(CreatedDate) AS NewestIncident
FROM Incidents
GROUP BY Category, Status
ORDER BY Category, Status;
GO

-- Query 3: Full incident timeline for a specific incident
SELECT 
    i.IncidentID,
    i.Title,
    i.Priority,
    i.Status,
    u.UpdateText,
    u.UpdatedBy,
    u.UpdatedDate
FROM Incidents i
INNER JOIN IncidentUpdates u ON i.IncidentID = u.IncidentID
WHERE i.IncidentID = 1
ORDER BY u.UpdatedDate ASC;
GO

You now have three production-style queries that mirror what L1 engineers run daily. Next up, you will analyze IIS logs and create your operational runbook to tie everything together.

Analyze IIS Logs and Create Your Runbook

Your incident database is ready with sample data and analysis queries. Now it is time to practice the other half of application support: reading raw logs and documenting what you know.

In a real operations role, you would diagnose issues by reading IIS access logs, checking Windows Event Viewer for system-level errors, and then following a documented runbook to resolve problems consistently. In this step, you will do all three.

In this step, get ready to:
  • Generate web traffic and analyze IIS log entries.
  • Review Windows Event Viewer for warnings and errors.
  • Create an operational runbook documenting troubleshooting procedures.
Generate traffic and analyze the IIS log file

IIS logs every HTTP request to your site in W3C Extended format. Each line captures details like the URL requested, the HTTP status returned, and how long the server took to respond. Before you can read a log, you need some entries in it.

  • Open your browser and navigate to http://localhost:8080.
  • Refresh the page 4-5 times to generate multiple successful request entries.
  • Now visit http://localhost:8080/nonexistent in your browser to generate a 404 error entry.
  • Refresh that 404 page 2-3 times so you have multiple error entries to spot in the log.

Why intentional errors?

In production, you would not cause errors on purpose. Here you are creating 404 entries so you can practice spotting failures in a log full of successful requests. This is the core skill of log analysis: finding the problem lines among thousands of normal ones.

Now you will open the log file and read the entries you just created.

  • Open File Explorer and navigate to C:\inetpub\logs\LogFiles\W3SVC2\.
  • You should see one or more log files with names like u_exYYMMDD.log. Open the most recent one by right-clicking it and selecting **Open with** > **Notepad**.

What is the W3SVC2 folder?

IIS stores logs in per-site subdirectories named W3SVC{siteID}. The Default Web Site is typically W3SVC1, so your SupportLabApp (the second site you created) logs to W3SVC2.

  • Look at the lines starting with #Fields: near the top of the file. This header tells you what each column means.
  • Identify these key fields in the log entries: date, time, client IP (c-ip), HTTP method (cs-method), URI stem (cs-uri-stem), status code (sc-status), and time-taken.
  • Find the lines where sc-status is 404. These are the intentional errors you generated.
  • Find the lines where sc-status is 200. These are the successful requests to your homepage.

What do these fields mean?

The sc-status field is the HTTP status code the server returned (200 = success, 404 = not found, 500 = server error). The time-taken field shows how many milliseconds the server spent handling that request.

When users report slow pages, you check time-taken. When they report errors, you check sc-status. This is the foundation of L1 log analysis.

Don't see any log files in the W3SVC2 folder?

IIS may buffer log entries before writing them to disk. Try making a few more requests to http://localhost:8080 and wait 60 seconds before checking again.

If the folder itself is missing, your SupportLabApp site may have a different site ID. Check IIS Manager for the site ID listed under **Sites** and look for the matching W3SVC folder.

Help me find my IIS log files for the SupportLabApp site.

Review Windows Event Viewer

IIS logs show HTTP-level activity. Windows Event Viewer shows system-level events: services crashing, application exceptions, and hardware warnings. L1 engineers check both when diagnosing issues.

  • Press the **Windows** key to open your search bar.
  • Type eventvwr and press **Enter** to open Event Viewer.
  • In the left panel, expand **Windows Logs**.
  • Click **Application**. This log shows events from applications like IIS, .NET, and SQL Server.
  • Look for any rows with a **Warning** (yellow triangle) or **Error** (red circle) icon in the **Level** column.
  • Click on one of those entries and note the **Source**, **Event ID**, and the description text in the bottom pane.
  • Now click **System** under **Windows Logs** in the left panel. This log shows operating system events like service start/stop and driver errors.
  • Look for any Warning or Error entries here as well.

What should I look for in Event Viewer?

You are looking for three pieces of information on each entry: the Source (which program generated the event), the Event ID (a number that identifies the specific issue), and the description (what actually happened).

In a real operations environment, you would include these details in your incident ticket so the development team can investigate.

Not seeing any warnings or errors?

A clean Event Viewer is perfectly normal on a fresh system. Note that you see only Information-level entries. In production, you would see a mix of levels depending on what is happening on the server.

Create your operational runbook

An operational runbook is a documented set of procedures that any support engineer can follow to diagnose and resolve common issues. Without a runbook, troubleshooting depends on individual knowledge. With one, any team member can respond consistently.

  • Open Notepad (or any text editor you prefer).
  • Create a new file and save it as runbook.md on your Desktop.
  • Copy the following runbook content into the file:
# Operational Runbook - SupportLab Application

## Application Overview
- **Application**: SupportLab Web Application
- **Technology**: ASP.NET Core 8.0 on IIS
- **URL**: http://localhost:8080
- **Database**: SQL Server 2022 Express (localhost\SQLEXPRESS)
- **Log Location**: C:\inetpub\logs\LogFiles\W3SVC2\

---

## Common Issues and Resolution Steps

### Issue 1: IIS Site Not Responding (HTTP 503)
**Symptoms**: Users receive "Service Unavailable" error
**Priority**: P1-Critical

**Steps**:
1. Open IIS Manager and check if the Application Pool is running
2. If stopped, right-click the Application Pool > Start
3. Check Windows Event Viewer > Application log for crash details
4. Review IIS logs for the time of failure
5. If recurring, escalate to development team with Event Viewer details

---

### Issue 2: HTTP 500 Internal Server Error
**Symptoms**: Users see a generic error page
**Priority**: P1-Critical

**Steps**:
1. Check IIS logs for the specific URL and timestamp
2. Look at the sc-status and sc-substatus fields in the log
3. Check Windows Event Viewer > Application for .NET exceptions
4. Enable stdout logging in web.config for detailed errors
5. Escalate to development team with error details and stack trace

---

### Issue 3: Slow Response Times
**Symptoms**: Pages taking longer than 5 seconds to load
**Priority**: P3-Medium

**Steps**:
1. Check IIS logs - look at the time-taken field (value is in milliseconds)
2. Run SQL query to check for blocking: SELECT * FROM sys.dm_exec_requests WHERE blocking_session_id > 0
3. Check server resource usage in Task Manager (CPU, Memory)
4. Check disk space on C: drive
5. If database-related, escalate to DBA team

---

### Issue 4: Database Connection Failures
**Symptoms**: Application errors mentioning "connection timeout" or "SQL Server"
**Priority**: P2-High

**Steps**:
1. Verify SQL Server service is running: Open Services (services.msc), find "SQL Server (SQLEXPRESS)"
2. Test connection from SSMS manually
3. Check SQL Server error log in SSMS > Management > SQL Server Logs
4. Verify no blocking queries: SELECT * FROM sys.dm_exec_requests WHERE blocking_session_id > 0
5. Restart SQL Server service if unresponsive (last resort)

---

## Escalation Matrix
| Priority | Response Time | Escalation After |
|----------|--------------|------------------|
| P1-Critical | 15 minutes | 30 minutes |
| P2-High | 30 minutes | 2 hours |
| P3-Medium | 4 hours | 8 hours |
| P4-Low | Next business day | 3 business days |

---

## Daily Checklist
- [ ] Check IIS Application Pools are running
- [ ] Review open P1/P2 incidents in tracker
- [ ] Check disk space on web server
- [ ] Review overnight error logs in Event Viewer
- [ ] Update incident tracker with overnight findings

What does this runbook cover?

The runbook documents four common issues an L1 support engineer would handle: IIS returning 503 (site down), HTTP 500 errors (application crashes), slow response times, and database connection failures. Each issue has a priority level, symptoms to look for, and numbered resolution steps.

The escalation matrix defines how quickly each priority level must be acknowledged and when to escalate to a senior engineer. The daily checklist is what you would run through every morning at the start of your shift.

  • Save runbook.md.
  • Scroll through the file and confirm you can see the Application Overview, all four issues with their resolution steps, the Escalation Matrix table, and the Daily Checklist at the bottom.

File saving as .md.txt instead of .md?

In Notepad's Save dialog, change the **Save as type** dropdown from "Text Documents (*.txt)" to "All Files (*.*)". Then type the filename as runbook.md including the extension.

Help me save my runbook as a .md file in Notepad.

✔️ Awesome, I've got everything!

Great. Double check you have saved runbook.md and it includes all four issues, the escalation matrix, and the daily checklist.

ⓧ I'd like to double check the full code

Here is the complete runbook.md file for comparison:

# Operational Runbook - SupportLab Application

## Application Overview
- **Application**: SupportLab Web Application
- **Technology**: ASP.NET Core 8.0 on IIS
- **URL**: http://localhost:8080
- **Database**: SQL Server 2022 Express (localhost\SQLEXPRESS)
- **Log Location**: C:\inetpub\logs\LogFiles\W3SVC2\

---

## Common Issues and Resolution Steps

### Issue 1: IIS Site Not Responding (HTTP 503)
**Symptoms**: Users receive "Service Unavailable" error
**Priority**: P1-Critical

**Steps**:
1. Open IIS Manager and check if the Application Pool is running
2. If stopped, right-click the Application Pool > Start
3. Check Windows Event Viewer > Application log for crash details
4. Review IIS logs for the time of failure
5. If recurring, escalate to development team with Event Viewer details

---

### Issue 2: HTTP 500 Internal Server Error
**Symptoms**: Users see a generic error page
**Priority**: P1-Critical

**Steps**:
1. Check IIS logs for the specific URL and timestamp
2. Look at the sc-status and sc-substatus fields in the log
3. Check Windows Event Viewer > Application for .NET exceptions
4. Enable stdout logging in web.config for detailed errors
5. Escalate to development team with error details and stack trace

---

### Issue 3: Slow Response Times
**Symptoms**: Pages taking longer than 5 seconds to load
**Priority**: P3-Medium

**Steps**:
1. Check IIS logs - look at the time-taken field (value is in milliseconds)
2. Run SQL query to check for blocking: SELECT * FROM sys.dm_exec_requests WHERE blocking_session_id > 0
3. Check server resource usage in Task Manager (CPU, Memory)
4. Check disk space on C: drive
5. If database-related, escalate to DBA team

---

### Issue 4: Database Connection Failures
**Symptoms**: Application errors mentioning "connection timeout" or "SQL Server"
**Priority**: P2-High

**Steps**:
1. Verify SQL Server service is running: Open Services (services.msc), find "SQL Server (SQLEXPRESS)"
2. Test connection from SSMS manually
3. Check SQL Server error log in SSMS > Management > SQL Server Logs
4. Verify no blocking queries: SELECT * FROM sys.dm_exec_requests WHERE blocking_session_id > 0
5. Restart SQL Server service if unresponsive (last resort)

---

## Escalation Matrix
| Priority | Response Time | Escalation After |
|----------|--------------|------------------|
| P1-Critical | 15 minutes | 30 minutes |
| P2-High | 30 minutes | 2 hours |
| P3-Medium | 4 hours | 8 hours |
| P4-Low | Next business day | 3 business days |

---

## Daily Checklist
- [ ] Check IIS Application Pools are running
- [ ] Review open P1/P2 incidents in tracker
- [ ] Check disk space on web server
- [ ] Review overnight error logs in Event Viewer
- [ ] Update incident tracker with overnight findings

Secret mission

Add Automated Health Monitoring

Real operations teams don't wait for users to report outages. They use automated monitoring to detect failures before anyone notices. In this secret mission, you'll build a PowerShell script that acts as a mini monitoring agent, polling your application every 30 seconds and logging failures directly to your incident database.

Clean Up Your Resources

Clean Up Your Resources

This project runs entirely on your local machine, so there are no ongoing cloud costs. Decide whether to keep your resources running, pause them to come back later, or delete them entirely.

Resources you used:

  • IIS site: SupportLabApp (http://localhost:8080).
  • SQL Server database: IncidentTracker on localhost\SQLEXPRESS.
  • Published app files at C:\inetpub\wwwroot\SupportLabApp.
  • IIS log files at C:\inetpub\logs\LogFiles\W3SVC2\.
  • Project source files (SupportLabApp folder, SQL scripts, runbook.md, monitor.ps1).
  • Installed software: .NET 8 SDK, SQL Server 2022 Express, SSMS 22, IIS Windows feature, SqlServer PowerShell module.

Keep everything running

No action needed. Choose this if you plan to keep building on your application support lab or want to practice running queries and updating your runbook.

  • Your IIS site stays running at http://localhost:8080.
  • SQL Server Express continues to run as a background service, using a small amount of memory.
  • IIS logs will continue to accumulate in C:\inetpub\logs\LogFiles\W3SVC2\ as you make requests.

Pause - I'll come back to this later

Stop running processes to free up memory while keeping all your files and configuration intact.

  • Stop the SupportLabApp site in IIS Manager. Right-click the site and select Stop.
  • Stop the SQL Server service to free memory. Open services.msc from the Start menu, find SQL Server (SQLEXPRESS), right-click, and select Stop.
  • Stop any running monitor.ps1 script by pressing Ctrl+C in its PowerShell window.
  • When you come back, restart SQL Server via services.msc and start your IIS site again from IIS Manager.

Delete - I don't want to use this again

Remove all project resources and installed software to start fresh. Follow these steps in order.

Remove the IIS site
  • Open IIS Manager (type inetmgr in the Start menu).
  • Expand the server node, then select Sites.
  • Right-click SupportLabApp and select Remove.
Drop the IncidentTracker database
  • Open SSMS and connect to localhost\SQLEXPRESS.
  • Open a New Query window and run this command:
DROP DATABASE IncidentTracker;
GO
Delete the published app and log files
  • Open an elevated Command Prompt (right-click Command Prompt, select Run as administrator) and run these commands:
rmdir /s /q C:\inetpub\wwwroot\SupportLabApp
rmdir /s /q C:\inetpub\logs\LogFiles\W3SVC2
Delete your project source files
  • Delete the project folder on your Desktop that contains SupportLabApp, your SQL scripts, runbook.md, and monitor.ps1. Run this in Command Prompt:
rmdir /s /q %USERPROFILE%\Desktop\SupportLabApp
Uninstall the SqlServer PowerShell module
  • Open PowerShell and run:
Uninstall-Module -Name SqlServer -Force
Uninstall .NET 8 SDK, SQL Server, and SSMS
  • Press the Windows key, type Add or remove programs, and press Enter.
  • Search for .NET SDK, select it, and click Uninstall.
  • Search for SQL Server, select the SQL Server 2022 Express entry, and click Uninstall. Follow the wizard prompts.
  • Search for SQL Server Management Studio, select it, and click Uninstall.
Disable IIS
  • Open Control Panel and go to Programs, then Turn Windows features on or off.
  • Uncheck Internet Information Services and click OK.
  • Wait for Windows to apply the changes. You may need to restart your computer.

Nice Work!

Nice Work!

You have built a complete local application support lab from scratch. This is exactly the type of environment Associate Systems Engineers manage daily.

You've learned how to:

  • Deploy an ASP.NET Core web application to IIS and configure it to serve traffic on a custom port.
  • Set up a SQL Server Express incident tracking database and write analysis queries for priority sorting, category summaries, and incident timelines.
  • Analyze IIS W3C logs and Windows Event Viewer entries, then document your troubleshooting workflow in an operational runbook.
  • Built an automated health monitoring script that polls your application every 30 seconds and logs incidents to your database when outages are detected.

Ready to quiz yourself?