Audit Events FK Upgrade

Audit Events FK Upgrade

This release includes two database migrations that restore the long-missing index and foreign key on audit_events.operation_id. They run automatically as part of the normal solution upgrade and are designed to be safe on customer databases with very large audit_events tables (100M+ rows), but on large tables the upgrade can take a long time. Read this page before you run the upgrade.

The same information is intended for operators / admins with some technical background but who aren't SQL specialists.

---

1. What the upgrade actually does

Two new EF migrations against the audit_events table:

1. AddAuditEventsOperationIndex — adds a non-clustered index on the operation_id column.
2. AddAuditEventsOperationFK — adds a foreign key constraint from audit_events.operation_id → event_operations.id.

Both of these things should have been there since the very first version of the schema, but on databases that were upgraded through the old Audit Log Migration.sql path, the index and the FK were silently dropped and never put back. New events have been inserted ever since without anything enforcing that operation_id actually points at a real row in event_operations.

In plain English, here's what happens step-by-step

For each step the actual time depends on how many rows are in audit_events.

1. Create the index on operation_id. This is like building an alphabetical index at the back of a phone book: it lets the database find rows by operation_id quickly. On Azure SQL Database (and on SQL Enterprise / Managed Instance) this is built ONLINE, meaning normal reads and writes continue while it's building. On Standard / Web / Express SQL Server editions it's built offline (writes wait).

2. Find and "clean" orphan rows. An orphan is a row in audit_events whose operation_id points at a row in event_operations that doesn't exist (because the lookup row was deleted, or never inserted, at some point in the past). The upgrade finds these rows and sets their operation_id to NULL. The audit row itself is preserved — only the broken reference is cleared. This is done in batches of 10,000 rows so the transaction log doesn't explode and other users aren't blocked for long.

3. Add the foreign key constraint as "untrusted" (instant). The FK is added with WITH NOCHECK. This is a metadata-only change and takes a fraction of a second regardless of table size. This is the Microsoft-recommended pattern for adding FKs to huge tables — it avoids holding a long schema-modification lock.

4. Validate the constraint and mark it as "trusted". This is the slow step. The database now scans every single row in audit_events to confirm that every non-NULL operation_id really does point at a row in event_operations. Once it's validated, SQL Server marks the FK as is_not_trusted = 0, which means the query optimiser can now use the FK for performance tricks (like join elimination).

What you end up with

Before	After
No index on audit_events.operation_id	IX_operation_id (non-clustered)
No FK	FK_audit_events_event_operations (trusted)
Some rows have orphan operation_id values	Those rows have operation_id = NULL; the audit data itself is preserved
Orphan event_operations rows (lookup rows that aren't referenced by any audit event)	Left untouched — the migration only touches the FK child side

What it does NOT do

• It does not delete any audit events. Row count is identical before and after.
• It does not modify event_operations. Lookup rows with no references stay as-is.
• It is not reversible without losing the FK and index. Down migrations are provided but the orphan cleanup is one-way.

---

2. Measured baseline performance

The upgrade was benchmarked against a freshly seeded database in a controlled environment:

Property	Value
audit_events row count	10,000,000
Distribution	~95% valid op_id / ~2% NULL / ~3% orphan
event_operations row count	1,050 (1,000 referenced + 50 orphan)
SQL Server	SQL Server 2025, default instance, on an Azure VM with Standard HDD LRS disks (~500 IOPS, ~60 MB/s, ~10ms latency — the slowest Azure storage tier)
Total DatabaseUpgrader.CheckDbUpgraded elapsed	8 min 06 sec (486 seconds)
Per-row cost	~0.049 ms/row
Post-state	FK present and trusted, index present, 0 orphans, row count unchanged

The dominant cost was step 4 above (the FK validation scan). Steps 1–3 together accounted for less than a minute.

Why this is a "best-case at 10M, worst-case at 100M+" baseline. The test VM has Standard HDD storage, which is the slowest disk type Azure offers — about 500 IOPS and 60 MB/s with high (~10ms) latency. At 10M rows the audit_events table is only ~2 GB and fits entirely in the SQL Server buffer pool, so the FK validation scan was served almost entirely from memory and the slow HDD barely mattered. As the table grows past what fits in memory (somewhere between 50M and 100M rows on a typical SQL instance), the Standard HDD becomes the bottleneck and the per-row cost gets dramatically worse. This is the opposite of what most Azure SQL Database tiers will experience, because all Azure SQL DB tiers (even the cheapest) use SSD storage. So this test is actually a useful lower-bound for IO-bound performance — most Azure SQL DB customers will do better than the worst-case rows in the tables below, not worse.

---

2a. How many rows do you have?

Before you look at the time estimates, run this against your database to see which row in the tables you should be looking at. It also surfaces how many orphans the cleanup step will have to NULL out, and whether the FK / index are already in place (so the upgrade would be a no-op).

-- Instant estimate (reads metadata only, no table scan - safe on any DB size)
SELECT
    OBJECT_NAME(p.object_id) AS table_name,
    SUM(p.rows)              AS estimated_row_count
FROM sys.partitions p
WHERE p.object_id IN (OBJECT_ID('dbo.audit_events'),
                      OBJECT_ID('dbo.event_operations'))
  AND p.index_id IN (0, 1)   -- heap or clustered index only
GROUP BY p.object_id
ORDER BY table_name;

-- Orphan count (full scan - may take a while on huge tables; safe to skip
--   if you just want time estimates and don't care about the exact orphan count)
SELECT COUNT_BIG(*) AS orphan_operation_id_rows
FROM dbo.audit_events ae
WHERE ae.operation_id IS NOT NULL
  AND NOT EXISTS (
      SELECT 1 FROM dbo.event_operations eo
      WHERE eo.id = ae.operation_id
  );

-- Already-applied check - if both come back 1, the upgrade is a no-op
SELECT
    (SELECT COUNT(*) FROM sys.foreign_keys
       WHERE parent_object_id = OBJECT_ID('dbo.audit_events')
         AND referenced_object_id = OBJECT_ID('dbo.event_operations')) AS fk_already_present,
    (SELECT COUNT(*) FROM sys.indexes
       WHERE object_id = OBJECT_ID('dbo.audit_events')
         AND name IN ('IX_operation_id', 'IX_FK_events_event_operations')) AS index_already_present;

What to do with the numbers:

• audit_events estimated_row_count → pick the corresponding row in §3 (if you self-host SQL on a VM) or §4 (if you're on Azure SQL DB).
• orphan_operation_id_rows → roughly proportional to the orphan-cleanup time. In the benchmark ~3% of rows were orphans and the cleanup took only seconds; if yours is ≪ 10% you can mostly ignore this step in the timing.
• fk_already_present and index_already_present → if both are 1, the migration will detect this and exit in seconds. No action needed.

---

3. Expected upgrade time vs table size

The work the upgrade does is essentially linear in the number of rows in audit_events — as long as the table fits in memory. Once it doesn't, every page has to be read from disk and the per-row cost balloons on slow storage.

On the test VM (Azure VM, Standard HDD — the slowest option)

audit_events rows	Estimated upgrade time	Notes
1,000,000 (1M)	~50 seconds	Fits in memory
10,000,000 (10M)	~8 minutes (measured)	Fits in memory; HDD largely bypassed
50,000,000 (50M)	~1 – 2 hours	Starting to spill from memory; HDD becomes painful
100,000,000 (100M)	~4 – 8 hours	Mostly disk-bound; 500-IOPS / 60 MB/s HDD is the bottleneck
250,000,000 (250M)	~14 – 24 hours	Sequential scan throughput-limited by HDD
500,000,000 (500M)	~1.5 – 2.5 days	HDD throughput is the wall; not realistic without better storage

If you're running SQL Server on an Azure VM and you have 50M+ audit_events, the single highest-impact change you can make is to switch the data disk from Standard HDD to Premium SSD (P30 or above) before applying the migration. This alone would shave 5–10× off the times above. Even Standard SSD would be a major improvement over Standard HDD for tables that no longer fit in memory.

---

4. Expected upgrade time on Azure SQL DB (DTU model, Standard tier)

Good news for Azure SQL DB customers: every Azure SQL DB tier — even the cheapest — uses SSD storage under the hood. So the "table doesn't fit in memory" cliff that hits hard on a Standard HDD test VM is much gentler in Azure SQL DB. The dominant cost on Azure SQL DB is the DTU cap on CPU and log throughput, not raw disk IO.

These are estimates with a wide error margin (±50%). Real-world numbers depend on how busy your DB is, what's already cached, log throttling, and other concurrent workload. Treat them as orders of magnitude.

Tier	DTUs	Max log throughput	Max data IOPS	Notes
S3	100	~2.5 MB/s	~320	Orphan-cleanup UPDATEs will be log-throughput limited
S4	200	~5 MB/s	~640
S6	400	~10 MB/s	~1,280	A reasonable sweet spot for a one-off upgrade
S7	800	~20 MB/s	~2,560	Recommended if you have 100M+ rows

Estimated upgrade time by row count and tier

Rows	S3 (100 DTU)	S4 (200 DTU)	S6 (400 DTU)	S7 (800 DTU)
1M	~3 – 5 min	~2 min	~1 min	<1 min
10M	~30 – 50 min	~15 – 25 min	~8 – 12 min	~5 – 8 min
50M	~3 – 5 h	~1.5 – 2.5 h	~45 m – 1.5 h	~25 – 45 min
100M	~6 – 10 h	~3 – 5 h	~1.5 – 2.5 h	~1 – 1.5 h
250M	~16 – 24 h	~8 – 12 h	~4 – 6 h	~2 – 3 h
500M	~1.5 – 2 days	~16 – 24 h	~8 – 12 h	~4 – 6 h

How to read this table: if you're seeing hours-on-end (let alone days), that's a strong signal to scale up before the upgrade — see §5. The cost of running on a higher tier for a few hours is trivial compared to a multi-day outage window.

Premium tier is dramatically faster. If your audit_events table is in the 100M+ range, scaling temporarily to Premium (P2 / P4 / P6) is the best move. Premium has order-of-magnitude better IOPS (P2 = 4,000 IOPS, P6 = 16,000 IOPS) and log throughput. As a rough guide, P2 will finish the upgrade in roughly half the time of S7, and P6 in roughly a quarter — and you can scale back down immediately afterwards.

---

5. Pre-upgrade recommendations

Do these before you start the upgrade, in this order:

5.1 Stop the analytics web jobs

The web jobs continuously insert into audit_events. Leaving them running during the FK validation will (a) make the upgrade much slower because of lock contention, and (b) potentially cause the web jobs themselves to time out and fail.

• In the Azure Portal, navigate to your App Service / Function App and stop the analytics web jobs (WebJob.Office365ActivityImporter, WebJob.AppInsightsImporter, and any other importer jobs).
• Wait ~30 seconds for any in-flight batches to finish committing.

5.2 Pause / unschedule Power BI dataset refreshes

A Power BI refresh against a busy audit_events table during the migration will:

• Compete for IO with the FK validation scan.
• Probably fail anyway because the new index/FK temporarily change query plans.

Open the dataset(s) in the Power BI Service, disable scheduled refresh (or change the schedule to skip the upgrade window), and cancel any refresh that's already in progress.

5.3 Temporarily scale up your Azure SQL Database

If your DB is on a lower DTU tier (S3 / S4 / S6) and you have more than ~50M audit_events, scale up before the upgrade. The cost of running S7 (or even Premium) for a few hours is trivial compared to the time saved.

A reasonable pattern:

1. Scale up to S9 (1600 DTU) or a Premium P2 / P4 tier just before starting.
2. Apply the migration.
3. Verify the new FK and index are present and trusted (you can check sys.foreign_keys.is_not_trusted = 0 and sys.indexes for IX_operation_id).
4. Scale back down to your normal tier.

Scaling Azure SQL DB up or down is online — your DB stays available during the resize, with a brief (single-digit-second) reconnect at the end. Plan to do the scale-up at least a few minutes before kicking off the migration so it's settled in.

5.4 Make sure you have a recent backup

Azure SQL DB takes automatic backups, but confirm the most recent backup is recent (last few hours) before starting. If anything goes wrong you want a clean rollback point.

5.5 Allow plenty of time in your maintenance window

Use the estimates in §3 and §4 and multiply by 2 when picking the maintenance window. Things you don't want to discover at the 90% mark:

• The DB was busier than you thought.
• A long-running query held a lock on audit_events and the FK validation was waiting on it.
• The auto-generated tempdb / log file growth was throttled.

---

6. During the upgrade

• Watch the SQL session running the migration. The scripts use RAISERROR ... WITH NOWAIT to surface live progress — you'll see messages like batch cleared 10000 (total 290000) for the orphan cleanup, and a validating FK (WITH CHECK CHECK CONSTRAINT) line right before the slow step.
• It is safe to leave it running, however long it takes. The installer's EF migration runner sets CommandTimeout = 0 (infinite), so the upgrade will not give up part-way through the FK validation scan even if it runs for many hours. The migration also runs outside the EF migration transaction (suppressTransaction: true), so each step commits as it completes. If the connection drops mid-way through the FK validation, you can simply re-run the upgrade — the early steps are idempotent (they check whether they've already been done) and the orphan-cleanup batches that already committed are preserved.

---

7. After the upgrade — quick sanity check

Run this against your DB:

SELECT
    (SELECT COUNT_BIG(*) FROM dbo.audit_events) AS audit_events_total,
    (SELECT COUNT_BIG(*) FROM dbo.audit_events
       WHERE operation_id IS NOT NULL
         AND NOT EXISTS (SELECT 1 FROM dbo.event_operations eo
                         WHERE eo.id = dbo.audit_events.operation_id)
    ) AS orphan_op_id_rows,           -- should be 0
    (SELECT TOP 1 is_not_trusted FROM sys.foreign_keys
       WHERE name = 'FK_audit_events_event_operations') AS fk_is_not_trusted,
                                       -- should be 0 (i.e. trusted)
    (SELECT COUNT(*) FROM sys.indexes
       WHERE object_id = OBJECT_ID('dbo.audit_events')
         AND name = 'IX_operation_id') AS ix_present;
                                       -- should be 1

Expected: orphan_op_id_rows = 0, fk_is_not_trusted = 0, ix_present = 1, audit_events_total unchanged from before.

Once that's confirmed:

1. Re-enable Power BI scheduled refresh.
2. Restart the web jobs.
3. Scale the DB back down to its normal tier.

---

8. Quick reference summary

• What changes: audit_events gets a new index + FK on operation_id. Orphan references are NULL'd (audit row preserved).
• Time: ~0.05 ms per row when the table fits in memory; significantly worse once it doesn't on slow storage like Standard HDD. Azure SQL DB (all tiers use SSD) degrades much more gracefully.
• Worst-case time: ~2 days for 500M rows on S3 (100 DTU), or longer on a Standard HDD VM. Scale up.
• Best ROI tweak:
  • Azure SQL DB customers: temporarily scale to S7 or Premium (P2 / P4 / P6) for the upgrade — the runtime drops several-fold.
  • VM-hosted SQL customers: if your data disk is Standard HDD or Standard SSD and you have 50M+ rows, switch the data disk to Premium SSD before the upgrade.
• Before: stop web jobs, disable Power BI refresh, scale up, confirm backup.
• During: watch the SQL session messages; safe to leave running for as long as it takes — the installer's EF migration command timeout is infinite, so it won't abort mid-validation.
• After: verify FK is trusted and index present, then scale back and re-enable jobs.

---

Based on a controlled benchmark of DatabaseUpgrader.CheckDbUpgraded against a freshly seeded database with 10,000,000 audit_events rows on an Azure VM with Standard HDD disks (the slowest Azure storage tier).