Change tracking & licence advisor

Two features that need a word of setup beyond the default VIEW SERVER STATE grant. Both are on by default and both degrade gracefully — if a permission is missing, the affected part is skipped and the rest keeps working.

---

Change tracking (/changes)

Builds a durable timeline of what changed on each monitored instance: DDL, sp_configure, global trace flags, database options, logins and role membership.

Why the harvest interval matters

SQL Server's default trace is the source for DDL and security events. It rolls over after five files of 20 MB and the oldest data is then permanently gone. On a busy instance that can be under an hour.

The harvest interval is therefore the real retention control. Anything the app has not collected before the rollover does not exist:

"ChangeTracking": {
  "Enabled": true,
  "IntervalMinutes": 10,
  "RetentionDays": 180
}

Docker / environment variables:

ChangeTracking__IntervalMinutes=10
ChangeTracking__RetentionDays=180

If /changes shows gaps on a write-heavy instance, lower IntervalMinutes before anything else.

Permissions

Capability	Grant needed	Missing it means
DDL + security events	ALTER TRACE	No schema/security rows; config diffing still runs
Config, DB options, database lifecycle	VIEW SERVER STATE	Nothing is captured
Global trace flags	sysadmin	Trace-flag rows are skipped silently

GRANT ALTER TRACE TO [monitoring_login];

The default trace must also be enabled — it is by default, but installers and hardening baselines sometimes turn it off:

SELECT * FROM sys.traces WHERE is_default = 1;   -- one row expected

-- If empty:
EXEC sp_configure 'show advanced options', 1; RECONFIGURE;
EXEC sp_configure 'default trace enabled', 1;   RECONFIGURE;

When no default trace is available (turned off, or Azure SQL Database, which has none), the Changes page says so once per instance and falls back to configuration diffing. Schema and security changes are then unavailable; the other categories are unaffected.

First run is quiet on purpose

The first harvest against an instance records only the baseline — no events. Without that, switching monitoring on would manufacture a hundred "changed" rows for settings that had merely never been seen before, and would backfill whatever happened to still be in the trace buffer. From the second harvest onward, real changes are reported.

Consequence worth knowing: if you delete rows from ServerConfigBaselines, the next harvest treats that instance as new and stays silent for one cycle.

Chart overlay

Performance → History has a Show changes switch that overlays recorded changes as vertical markers on every chart. Only Notable and Critical changes are drawn — Info-level churn would paint the charts solid. The choice is remembered per browser.

Retention

ChangeTracking:RetentionDays (default 180) is swept daily by the cleanup service. ServerConfigBaselines is deliberately not swept: it is current state, not history, and deleting it would trigger the silent-first-run behaviour above.

---

License optimization (/reports/license)

Reports whether an instance running Enterprise could run on Standard, and what that would be worth.

Configure your own prices

The app ships no Microsoft price list, and the prices default to 0. With no prices the report shows cores and 2-core packs; enter your terms and it reports amounts:

"LicenseCostSettings": {
  "Currency": "EUR",
  "EnterpriseCorePackAnnualPrice": 0,
  "StandardCorePackAnnualPrice": 0,
  "UtilisationWindowDays": 30,
  "Enabled": true,
  "IntervalHours": 24,
  "RetentionDays": 400
}

Use your actual contract terms (EA, SPLA, Open Value), not list prices. Both figures are the annual price of one 2-core pack.

Permissions

VIEW SERVER STATE plus read access (CONNECT / VIEW DATABASE STATE) on each database to be checked. Databases the login cannot reach are skipped and counted in the assessment — the run does not fail, but the verdict is incomplete, so treat a nonzero skip count as "not yet conclusive".

How to read the verdict

Verdict	Meaning
Can downgrade to Standard	No Enterprise feature in use on this version, hardware within 24 cores / 128 GB
Enterprise required	At least one feature genuinely blocks Standard — the blocker list names the database
Hardware exceeds Standard limit	No feature blockers, but downgrading would mean downsizing the machine
No change needed	Not Enterprise, or Azure PaaS (not core-licensed at all)
Cannot assess	Assessment failed — see the error on the row

Features that the DMV reported but that do not block on your version are listed separately under "reported features that are not blockers". This is the part that matters: SQL Server 2016 SP1 moved compression, partitioning, columnstore and in-memory OLTP into Standard, and 2019 did the same for TDE, but sys.dm_db_persisted_sku_features still names them. Tools that skip this distinction will tell you to keep paying for Enterprise you do not need.

Consolidation candidates

Instances whose peak CPU over the utilisation window stayed below 25 %. Peak rather than average on purpose — month-end batch is often the only reason a machine is that size, and an average hides it.

This section is driven by collected history, so a freshly added instance has no consolidation verdict until enough samples exist (at least 100).

What it cannot see

Enterprise capabilities used only at runtime leave no trace in the database and are therefore invisible here — online index rebuilds and parallel index operations in particular. Check your maintenance jobs before downgrading.

This report is technical decision support, not licensing advice. It does not know your agreement, Software Assurance, or virtualisation rights. Confirm any downgrade with your licensing partner.