Inventory Collect → Catalog UI (MVP)

Status: Reviewed (spike v1)
Path: Spike #1 of approved series (then #2 Backup heat map → #3 Agent fail board → #7 Alert ack)
Stack: PowerShell → SQL/stored procs → Blazor Server
Builds on: SQL-01 Inventory & configuration baseline
Goal: One end-to-end path: discover/collect instance facts → upsert SQL → Blazor catalog grid.

In scope (MVP)

Host list from CSV or AD group (parameterized)
Detect SQL instances + core properties (version, edition, Agent running, max server memory, LastSeen)
Upsert into inventory DB
Blazor page: filterable grid + simple detail
CollectRun audit (start/end/error)

Out of scope (later)

Full config history / drift UI polish
Linked servers, tempdb file detail, AG topology
Patching targets, alerts (spikes #7/#8)

A. Database objects (inventory DB)

-- Natural key: HostName + InstanceName (use N'(local)' / N'MSSQLSERVER' for default)
CREATE TABLE dbo.SqlInstance (
  SqlInstanceId   INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
  HostName        NVARCHAR(128) NOT NULL,
  InstanceName    NVARCHAR(128) NOT NULL,
  ProductVersion  NVARCHAR(64) NULL,
  ProductLevel    NVARCHAR(32) NULL,
  Edition         NVARCHAR(128) NULL,
  Collation       NVARCHAR(128) NULL,
  IsClustered     BIT NULL,
  MaxServerMemoryMb INT NULL,
  AgentRunning    BIT NULL,
  LastSeenAt      DATETIME2(0) NOT NULL,
  FirstSeenAt     DATETIME2(0) NOT NULL CONSTRAINT DF_SqlInstance_FirstSeen DEFAULT SYSUTCDATETIME(),
  IsActive        BIT NOT NULL CONSTRAINT DF_SqlInstance_IsActive DEFAULT (1),
  CONSTRAINT UQ_SqlInstance UNIQUE (HostName, InstanceName)
);

CREATE TABLE dbo.CollectRun (
  CollectRunId INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
  CollectorName NVARCHAR(64) NOT NULL,
  StartedAt DATETIME2(0) NOT NULL,
  EndedAt DATETIME2(0) NULL,
  HostCount INT NULL,
  InstanceCount INT NULL,
  ErrorCount INT NULL,
  Message NVARCHAR(2000) NULL
);

Procs (signatures):

CREATE PROC dbo.usp_CollectRun_Start @CollectorName NVARCHAR(64), @CollectRunId INT OUTPUT AS ...
CREATE PROC dbo.usp_CollectRun_Complete @CollectRunId INT, @HostCount INT, @InstanceCount INT, @ErrorCount INT, @Message NVARCHAR(2000) = NULL AS ...

CREATE PROC dbo.usp_SqlInstance_Upsert
  @HostName NVARCHAR(128),
  @InstanceName NVARCHAR(128),
  @ProductVersion NVARCHAR(64) = NULL,
  @ProductLevel NVARCHAR(32) = NULL,
  @Edition NVARCHAR(128) = NULL,
  @Collation NVARCHAR(128) = NULL,
  @IsClustered BIT = NULL,
  @MaxServerMemoryMb INT = NULL,
  @AgentRunning BIT = NULL
AS
BEGIN
  SET NOCOUNT ON;
  MERGE dbo.SqlInstance AS t
  USING (SELECT @HostName AS HostName, @InstanceName AS InstanceName) AS s
    ON t.HostName = s.HostName AND t.InstanceName = s.InstanceName
  WHEN MATCHED THEN UPDATE SET
    ProductVersion = @ProductVersion, ProductLevel = @ProductLevel, Edition = @Edition,
    Collation = @Collation, IsClustered = @IsClustered, MaxServerMemoryMb = @MaxServerMemoryMb,
    AgentRunning = @AgentRunning, LastSeenAt = SYSUTCDATETIME(), IsActive = 1
  WHEN NOT MATCHED THEN INSERT (HostName, InstanceName, ProductVersion, ProductLevel, Edition, Collation, IsClustered, MaxServerMemoryMb, AgentRunning, LastSeenAt)
    VALUES (@HostName, @InstanceName, @ProductVersion, @ProductLevel, @Edition, @Collation, @IsClustered, @MaxServerMemoryMb, @AgentRunning, SYSUTCDATETIME());
END;

CREATE PROC dbo.usp_SqlInventory_GetCatalog
  @StaleHours INT = 48,
  @ActiveOnly BIT = 1
AS
BEGIN
  SELECT SqlInstanceId, HostName, InstanceName, ProductVersion, ProductLevel, Edition,
         MaxServerMemoryMb, AgentRunning, LastSeenAt, FirstSeenAt, IsActive,
         CASE WHEN LastSeenAt < DATEADD(HOUR, -@StaleHours, SYSUTCDATETIME()) THEN 1 ELSE 0 END AS IsStale
  FROM dbo.SqlInstance
  WHERE (@ActiveOnly = 0 OR IsActive = 1)
  ORDER BY HostName, InstanceName;
END;

CREATE PROC dbo.usp_SqlInventory_GetInstanceDetail @SqlInstanceId INT AS
BEGIN
  SELECT * FROM dbo.SqlInstance WHERE SqlInstanceId = @SqlInstanceId;
END;

B. PowerShell collector (shape)

Inputs: -HostListPath (CSV with HostName) or -AdGroup; -InventorySqlConnectionString; optional -Credential.

Flow:

usp_CollectRun_Start → $runId
Resolve hosts
Per host (try/catch; tally errors): - Services: MSSQLSERVER / MSSQL$*, SQLSERVERAGENT / SQLAgent$* - For each instance: build ServerInstance (HOST or HOST\NAME) - Query via Invoke-Sqlcmd (or SMO):

SELECT
  CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(64)) AS ProductVersion,
  CAST(SERVERPROPERTY('ProductLevel') AS nvarchar(32)) AS ProductLevel,
  CAST(SERVERPROPERTY('Edition') AS nvarchar(128)) AS Edition,
  CAST(SERVERPROPERTY('Collation') AS nvarchar(128)) AS Collation,
  CAST(SERVERPROPERTY('IsClustered') AS bit) AS IsClustered,
  CAST(value AS int) AS MaxServerMemoryMb
FROM sys.configurations WHERE name = N'max server memory (MB)';

AgentRunning from service status
Call usp_SqlInstance_Upsert 4. usp_CollectRun_Complete

Module sketch: Collect-SqlInventory.ps1 + private Get-SqlInstancesOnHost. Idempotent; safe to schedule hourly.

Rights: PS account needs service query on hosts + SQL login with EXECUTE on upsert/collect procs (and read for the property query: public + VIEW SERVER STATE preferred).

C. Blazor Server (MVP UI)

Page: /inventory (or /sql/inventory)

Services: IInventoryService → SqlConnection + CommandType.StoredProcedure only (usp_SqlInventory_GetCatalog, GetInstanceDetail). No ad-hoc SQL in UI.

Filters: text search host/instance; toggle “Stale only”; toggle “Agent stopped”

Detail: simple offcanvas/page with same fields + FirstSeenAt

Auth: reuse existing Blazor auth; app pool login = EXECUTE on read procs only (not upsert).

D. Acceptance checks

[ ] Run collector against ≥1 real or lab instance → row in SqlInstance with fresh LastSeenAt
[ ] Second run updates same row (no duplicate key)
[ ] Blazor grid shows row; stale badge true if you age LastSeenAt manually
[ ] Failed host increments ErrorCount on CollectRun without aborting whole run
[ ] UI login cannot write inventory tables

E. Suggested file layout (repo)

/sql/inventory/001_tables.sql
/sql/inventory/002_procs.sql
/ps/Inventory/Collect-SqlInventory.ps1
/src/.../Pages/Inventory/Index.razor
/src/.../Services/InventoryService.cs

Next spike (wait for go-ahead)

#2 Backup coverage heat map — uses SqlInstance population from this MVP (SQL-02).

SQL Dude — SQL-SPIKE-01 Inventory MVP v1