Skip to content

Audit Events FK Upgrade Runbook

Sam Betts edited this page Nov 4, 2026 · 3 revisions

Updating the solution → Audit Events FK Upgrade → Audit Events FK Upgrade Runbook

This page is for administrators running the audit-events foreign-key upgrade from a build before 1604. It lists what to do before, during and after the upgrade, with a check that it worked. Size your maintenance window first, with the estimates in Audit Events FK Upgrade.

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, open the App Service → WebJobs and stop Office365ActivityImporter and AppInsightsImporter.
  • 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 FK and its supporting index are present, enabled and trusted, with the check in §7.
  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 of the main page 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. It checks the relationship by its columns, not by name: databases built from the original bootstrap script (Create DB.sql) can have the same foreign key and index under the legacy names FK_events_event_operations and IX_FK_events_event_operations, and the migrations accept those as they are.

SELECT
    (SELECT COUNT_BIG(*) FROM dbo.audit_events) AS audit_events_total,
    (SELECT COUNT_BIG(*) FROM dbo.audit_events AS ae
       WHERE ae.operation_id IS NOT NULL
         AND NOT EXISTS (SELECT 1 FROM dbo.event_operations eo
                         WHERE eo.id = ae.operation_id)
    ) AS orphan_op_id_rows,           -- should be 0
    (SELECT COUNT(*) FROM sys.foreign_keys AS fk
       JOIN sys.foreign_key_columns AS c ON c.constraint_object_id = fk.object_id
       WHERE fk.parent_object_id = OBJECT_ID('dbo.audit_events')
         AND fk.referenced_object_id = OBJECT_ID('dbo.event_operations')
         AND COL_NAME(c.parent_object_id, c.parent_column_id) = 'operation_id'
         AND COL_NAME(c.referenced_object_id, c.referenced_column_id) = 'id'
         AND fk.is_disabled = 0
         AND fk.is_not_trusted = 0) AS trusted_fk,
                                       -- should be 1 (enabled and trusted, whatever its name)
    (SELECT COUNT(*) FROM sys.indexes AS i
       JOIN sys.index_columns AS ic ON ic.object_id = i.object_id AND ic.index_id = i.index_id
       WHERE i.object_id = OBJECT_ID('dbo.audit_events')
         AND ic.key_ordinal = 1
         AND COL_NAME(ic.object_id, ic.column_id) = 'operation_id'
         AND i.is_disabled = 0) AS operation_id_indexes;
                                       -- should be 1 or more

Expected: orphan_op_id_rows = 0, trusted_fk = 1, operation_id_indexes at least 1, and audit_events_total unchanged from before. The names don't matter: don't create a second index or foreign key just to get the canonical name. If trusted_fk is 0 but a relationship exists, it is disabled or untrusted; plan its repair with a DBA, as §2a describes.

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: check the FK is enabled and trusted and an operation_id index is present (§7, by columns, not by name), 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).


Return to Audit Events FK Upgrade · All topics in this guide

Previous: Audit Events FK Upgrade

Start here

Copilot analytics

Using the portal

Install & configure

Beyond Copilot

Operate

Upgrading from an older build

Reference

Project

Clone this wiki locally