Config & instance system procedures

Status: Reviewed
Stack: PowerShell → SQL / stored procs → Blazor
Depends on: SQL-CLI-09 (overview), SQL-CLI-01 (calling)
Goal: Deep practical use of sp_configure / RECONFIGURE and related instance helpers from CLI scripts — with DMV/catalog read paths for Blazor inventory.

Sources: Microsoft Learn (sql-server-ver17 / 2026). Expands SQL-CLI-09 §3.1 only — do not treat this as a rewrite of the overview. Patterns for staging → Blazor: SQL-CLI-07.

1. Scope map

Write path stays sp_configure + RECONFIGURE.
Read path for Blazor is always sys.configurations (and friends) — never scrape sp_configure text output into a grid.

Database-scoped knobs (MAXDOP, etc.) are ALTER DATABASE SCOPED CONFIGURATION, not this doc. Soft-NUMA / some affinity paths may also use ALTER SERVER CONFIGURATION.

2. sp_configure deep dive

2.1 Mental model

sp_configure 'option', N   →  writes pending value (config_value / sys.configurations.value)
RECONFIGURE [WITH OVERRIDE] →  installs running value when dynamic (run_value / value_in_use)
is_dynamic = 0             →  still needs service restart after RECONFIGURE

Partial name match: Engine accepts any unique substring of the option name. Prefer the full name in scripts ('max degree of parallelism', not 'max degree' unless you own uniqueness forever).

2.2 Show advanced

Advanced options (affinity, MAXDOP, recovery interval, surface-area XPs, …) are invisible until:

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
-- … do work …
EXEC sp_configure 'show advanced options', 0;
RECONFIGURE;

Ops rule: leave advanced off in steady state. Leaving it on is not a security boundary (any user who can run sp_configure with one arg already sees names once it’s on), but it is noise and a tell that someone left a change window open. The setting is global — not session-scoped.

2.3 RECONFIGURE vs RECONFIGURE WITH OVERRIDE

RECONFIGURE is not allowed inside an explicit/implicit transaction. Batch several sp_configure calls, then one RECONFIGURE — if any part fails validation, none of the pending installs apply.

Changes are written to the SQL Server error log.

2.4 Restart-required (is_dynamic = 0)

After RECONFIGURE, if value <> value_in_use and is_dynamic = 0, schedule a controlled restart. There is no catalog flag that proves “RECONFIGURE already ran for this non-dynamic option” — so always RECONFIGURE before restart so the pending value is staged.

Common RR options Rick may actually touch: fill factor (%), tempdb metadata memory-optimized, scan for startup procs, user connections, remote access, lightweight pooling, priority boost (don’t), affinity I/O masks, c2 audit mode / common criteria, hardware-offload / enclave options on newer builds.

2.5 High-value options (ops table)

Defaults and restart flags from Learn Server configuration options (sql-server-ver17). “Default spirit” = what a fresh / typical install implies, not every edition nuance.

Also note (inventory, rarely set by hand): priority boost (RR — leave 0), lightweight pooling (RR — almost never), affinity I/O masks (RR — NUMA specialists only).

SQL Server 2025 (17.x) watchlist (appear in Learn option list; confirm on your build before scripting): allow server scoped db credentials, availability group commit time (ms), external rest endpoint enabled, max ucs send boxcars, max lock manager cache memory (%) (RR, CU-gated). Treat as “inventory + ticket,” not casual toggles.

3. Reading config safely for inventory

3.1 Canonical query

SELECT
    @@SERVERNAME AS instance_name,
    c.configuration_id,
    c.name,
    CAST(c.value AS bigint) AS config_value,
    CAST(c.value_in_use AS bigint) AS value_in_use,
    CAST(c.minimum AS bigint) AS minimum_value,
    CAST(c.maximum AS bigint) AS maximum_value,
    c.is_dynamic,
    c.is_advanced,
    c.description,
    CASE
        WHEN CAST(c.value AS bigint) <> CAST(c.value_in_use AS bigint) THEN 1
        ELSE 0
    END AS is_pending_or_mismatch,
    CASE
        WHEN c.is_dynamic = 0
         AND CAST(c.value AS bigint) <> CAST(c.value_in_use AS bigint)
        THEN 1 ELSE 0
    END AS needs_restart,
    SYSUTCDATETIME() AS collected_utc
FROM sys.configurations AS c;

Permissions: historically public. On SQL Server 2022+, Learn documents VIEW SERVER PERFORMANCE STATE for sys.configurations — grant that (or broader VIEW SERVER STATE) to the inventory login. Do not assume anonymous public works on locked-down 2022/2025 builds.

3.2 Pending vs running — expected mismatches

Filter “real” pending for alerts:

SELECT name, value, value_in_use, is_dynamic
FROM sys.configurations
WHERE value <> value_in_use
  AND name NOT IN (N'max server memory (MB)', N'min server memory (MB)');

3.3 sqlcmd / Invoke-Sqlcmd → staging shape (Blazor)

Prefer one result set from sys.configurations, not EXEC sp_configure.

Staging table sketch (inventory DB):

CREATE TABLE dbo.SqlInstanceConfig (
    SnapshotId           bigint         NOT NULL,      -- FK to collection run
    InstanceName         nvarchar(128)  NOT NULL,
    ConfigurationId      int            NOT NULL,
    OptionName           nvarchar(35)   NOT NULL,
    ConfigValue          bigint         NULL,
    ValueInUse           bigint         NULL,
    MinimumValue         bigint         NULL,
    MaximumValue         bigint         NULL,
    IsDynamic            bit            NOT NULL,
    IsAdvanced           bit            NOT NULL,
    Description          nvarchar(255)  NULL,
    IsPendingOrMismatch  bit            NOT NULL,
    NeedsRestart         bit            NOT NULL,
    CollectedUtc         datetime2(3)   NOT NULL,
    CONSTRAINT PK_SqlInstanceConfig PRIMARY KEY (SnapshotId, ConfigurationId)
);

Blazor grid: filter NeedsRestart = 1, IsPendingOrMismatch = 1, or “surface area” name list (xp_cmdshell, Ole Automation Procedures, clr enabled, Ad Hoc Distributed Queries, …).

sqlcmd CSV:

sqlcmd -S "$srv" -E -d master -b -s"," -W -Q "SET NOCOUNT ON; SELECT ..." -o config.csv

Invoke-Sqlcmd → objects:

$rows = Invoke-Sqlcmd -ServerInstance $srv -Database master -Query $q
$rows | Export-Csv .\config.csv -NoTypeInformation
# or ConvertTo-Json for Blazor API staging — see SQL-CLI-07

4. Related instance helpers

Be honest: outside sp_configure, the “instance helper” shelf is thin. Prefer catalog/DMVs for anything that feeds Blazor.

sp_who / sp_who2 rules for this stack

• OK: break-glass session on a sick box when DMV query muscle memory is slower than EXEC sp_who2 'ACTIVE'.
• Not OK: Blazor inventory, Agent-collected history, or anything that assumes column stability (sp_who2 is undocumented).
• Capture into #t + filter is a smell — write the DMV query once instead.

sp_helpserver: useful confirmation that a linked server exists; scripting should still land on sys.servers.

sp_monitor: stateful between calls (shows deltas). Not a substitute for baseline monitoring. Requires elevated permission historically (sysadmin / explicit EXECUTE).

sp_server_info: public; ODBC-oriented. Don’t build config dashboards on it.

5. Permissions & audit

5.1 Who can do what

Least privilege for Blazor inventory: login with VIEW SERVER PERFORMANCE STATE (and whatever else your collector needs) — not serveradmin.
Config change jobs: dedicated admin principal; never the Blazor app pool.

5.2 Logging changes

Recipe mindset: before change → snapshot table; after → snapshot; diff config_value / value_in_use.

6. Gotchas & anti-patterns

1. Set without RECONFIGURE — config_value moves; nothing runs differently. Always verify value_in_use.
2. WITH OVERRIDE as habit — hides mistakes (recovery interval, conflicting masks).
3. Leaving show advanced options = 1 — noisy; reset when done.
4. Treating value <> value_in_use as always urgent — exclude known memory display quirks.
5. Assuming dynamic = instant for every session — engine may apply on its schedule; still no restart.
6. Non-dynamic without restart — ticket closes green, production still on old value_in_use.
7. Scraping sp_configure / sp_who2 into Blazor — multi-set / undocumented. Use catalog + DMVs.
8. Enabling surface area from “just testing” — xp_cmdshell, Ole Automation, Ad Hoc Distributed Queries, clr without review → 09e.
9. Instance MAXDOP only — ignore DB-scoped / Resource Governor / query hints and you’ll fight yourself.
10. priority boost = 1 — classic foot-gun; leave off.
11. RECONFIGURE inside a transaction — illegal; will fail.
12. Partial option names in automation — uniqueness can break when new options appear.
13. Azure SQL DB — most sp_configure instance knobs don’t apply the same way; this doc targets SQL Server / MI-class instances.
14. Plan cache — some reconfigs invalidate plans (see Learn); expect compile storms after big parallelism/memory flips.

7. Copy-paste recipes

R1 — List all options (advanced on temporarily)

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure;
EXEC sp_configure 'show advanced options', 0;
RECONFIGURE;

R2 — Pending / restart debt

SELECT name,
       CAST(value AS bigint) AS config_value,
       CAST(value_in_use AS bigint) AS value_in_use,
       is_dynamic
FROM sys.configurations
WHERE value <> value_in_use
ORDER BY is_dynamic, name;

R3 — Set MAXDOP + cost threshold (dynamic)

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'max degree of parallelism', 4;          -- example only
EXEC sp_configure 'cost threshold for parallelism', 50;    -- example only
RECONFIGURE;
EXEC sp_configure 'show advanced options', 0;
RECONFIGURE;

SELECT name, value, value_in_use
FROM sys.configurations
WHERE name IN (N'max degree of parallelism', N'cost threshold for parallelism');

R4 — Cap max server memory

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'max server memory (MB)', 65536;  -- example: 64 GB
RECONFIGURE;
EXEC sp_configure 'show advanced options', 0;
RECONFIGURE;

R5 — Enable backup compression + checksum defaults

EXEC sp_configure 'backup compression default', 1;
EXEC sp_configure 'backup checksum default', 1;
RECONFIGURE;

R6 — Remote DAC

EXEC sp_configure 'remote admin connections', 1;
RECONFIGURE;

R7 — sqlcmd: export config inventory CSV

sqlcmd -S "$SQL_SERVER" -E -d master -b -s"," -W \
  -Q "SET NOCOUNT ON;
SELECT @@SERVERNAME AS instance_name, name,
  CAST(value AS bigint) AS config_value,
  CAST(value_in_use AS bigint) AS value_in_use,
  is_dynamic, is_advanced,
  CASE WHEN value <> value_in_use THEN 1 ELSE 0 END AS is_pending
FROM sys.configurations ORDER BY name;" \
  -o "./SqlConfig_${SQL_SERVER}.csv"

R8 — Invoke-Sqlcmd: stage into objects + JSON

$q = @"
SET NOCOUNT ON;
SELECT @@SERVERNAME AS InstanceName, configuration_id AS ConfigurationId,
  name AS OptionName, CAST(value AS bigint) AS ConfigValue,
  CAST(value_in_use AS bigint) AS ValueInUse, is_dynamic AS IsDynamic,
  is_advanced AS IsAdvanced,
  CASE WHEN value <> value_in_use THEN 1 ELSE 0 END AS IsPendingOrMismatch,
  CASE WHEN is_dynamic = 0 AND value <> value_in_use THEN 1 ELSE 0 END AS NeedsRestart,
  SYSUTCDATETIME() AS CollectedUtc
FROM sys.configurations;
"@
$rows = Invoke-Sqlcmd -ServerInstance $env:SQL_SERVER -Database master -Query $q -TrustServerCertificate
$rows | ConvertTo-Json -Depth 3 | Set-Content -Path ".\config-$($env:SQL_SERVER).json" -Encoding UTF8

R9 — Before/after change audit pair

function Get-SqlConfigSnapshot([string]$Server) {
  Invoke-Sqlcmd -ServerInstance $Server -Database master -TrustServerCertificate -Query @"
SELECT name, CAST(value AS bigint) AS v, CAST(value_in_use AS bigint) AS u, is_dynamic
FROM sys.configurations;"
}
$before = Get-SqlConfigSnapshot $srv
# ... run change script with -b / check exit code (SQL-CLI-01) ...
$after  = Get-SqlConfigSnapshot $srv
Compare-Object $before $after -Property name,v,u,is_dynamic | Format-Table

R10 — Interactive who (break-glass only)

sqlcmd -S "$SQL_SERVER" -E -d master -Q "EXEC sp_who2 'ACTIVE';"
# Automation / Blazor: use DMVs instead — see SQL-CLI-09 §2

8. Pointers to 09b–e

Related stack docs: SQL-CLI-01 (calling), SQL-CLI-07 (Collect→Stage→Blazor).

Learn anchors (ver17): sp_configure, RECONFIGURE, sys.configurations, Server configuration options, sp_helpserver, sp_who, sp_monitor, sp_server_info.