Audit DDL changes in Azure SQL Database

On an on-premises SQL Server, the first stop after an unexpected schema change is the default trace and the Schema Changes History report in Management Studio. On Azure SQL Database neither exists: SQL Trace is not available in the single database service, and server-scoped DDL triggers cannot be created because there is no server to attach them to. Several of the usual answers simply do not apply, and the ones that remain need to be switched on before the change happens.

Database-scoped DDL triggers still work

A trigger ON DATABASE is supported, and it is the most direct way to keep a permanent record inside the database itself:

CREATE TABLE dbo.schema_change_log (
    id int IDENTITY PRIMARY KEY,
    event_time datetime2 DEFAULT SYSUTCDATETIME(),
    login_name sysname,
    event_type nvarchar(100),
    object_name nvarchar(256),
    tsql nvarchar(max)
);
GO
CREATE TRIGGER trg_log_schema_changes ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @e xml = EVENTDATA();
    INSERT INTO dbo.schema_change_log (login_name, event_type, object_name, tsql)
    VALUES (
        @e.value('(/EVENT_INSTANCE/LoginName)[1]', 'sysname'),
        @e.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'),
        @e.value('(/EVENT_INSTANCE/SchemaName)[1]', 'nvarchar(128)') + '.' +
        @e.value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(128)'),
        @e.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'nvarchar(max)')
    );
END;

The trade-offs are the same as on SQL Server. The trigger runs inside every DDL transaction, so a failure in it blocks deployments, it must be created in each database separately, and an administrator can disable it. On Azure there is one more: a database restored from a point in time or copied to another server carries the trigger with it, which is usually what you want and occasionally a surprise.

Azure SQL Auditing

Auditing is the managed answer. It is enabled at server or database level in the portal or with Azure CLI, and it writes to a storage account, a Log Analytics workspace or Event Hubs. The default audit action groups record every completed batch together with authentication events, so DDL statements are already in the log once auditing is on. For a narrower trail, SCHEMA_OBJECT_CHANGE_GROUP and DATABASE_OBJECT_CHANGE_GROUP cover object changes specifically.

Reading the audit from storage looks like this:

SELECT event_time, server_principal_name, database_name, object_name, statement
FROM sys.fn_get_audit_file('https://<account>.blob.core.windows.net/sqldbauditlogs/<server>/', DEFAULT, DEFAULT)
WHERE statement LIKE 'ALTER %' OR statement LIKE 'DROP %' OR statement LIKE 'CREATE %'
ORDER BY event_time DESC;

With Log Analytics the same question becomes a KQL query on the AzureDiagnostics table. Retention is whatever the storage account or workspace keeps, and the cost grows with the volume of batches audited, which is the reason some teams narrow the action groups.

Extended Events, scoped to the database

Azure SQL Database supports Extended Events sessions created ON DATABASE, with an event file target in Azure Blob Storage. A session on object_created, object_altered and object_deleted gives a lightweight record without code inside the DDL transaction. It needs a storage container and a credential, and reading it means shredding XML from sys.fn_xe_file_target_read_file, which is fine for investigations and awkward as a daily view.

Snapshots, for the history that answers "what changed"

All three methods record statements. None of them tells you, without further work, which changes were likely to break the application or what the schema looked like before last month's release. A snapshot approach reads the catalog on a schedule and compares the result object by object.

Taavik's schema change detection works this way against Azure SQL Database through the same SQL Server provider used for on-premises instances. An agent that can reach the database reads the catalog with a read-only login every 6, 12 or 24 hours or weekly, with daily as the most frequent option on the free plan. Each difference becomes a change with a severity: a dropped column, a changed type, a nullability flip or a removed unique index is likely breaking, a modified view or procedure needs attention, an added object is informational. Renamed tables are reported as renames. Alerts route the changes that match a rule to Slack, email or a webhook.

Snapshots do not record the login that made a change, and a change reverted between two scans leaves no trace. Pairing them with Azure SQL Auditing covers both sides: the audit says who ran the statement, the snapshot history says what the schema became and how serious the difference was.

For on-premises instances, the SQL Server schema change history guide covers the default trace and the SSMS report that Azure SQL Database lacks. Teams deploying with migration tools may also want what Flyway and Liquibase drift checks miss.