Azure SQL Database – Extracting DDL Events

Assuming you are on v12, then Extended Events (with Ring Buffer target) are available but there is what is known as a reduced surface area, or less things you can do. If I run the query below to list available actions, events and targets (sourced from here) I get 941 rows on my SQL Server 2014 and only 71 rows on my v12 Azure database, as at today:

SELECT
        o.object_type,
        p.name         AS [package_name],
        o.name         AS [db_object_name],
        o.description  AS [db_obj_description]
    FROM
                   sys.dm_xe_objects  AS o
        INNER JOIN sys.dm_xe_packages AS p  ON p.guid = o.package_guid
    WHERE
        o.object_type in
            (
            'action',  'event',  'target'
            )
    ORDER BY
        o.object_type,
        p.name,
        o.name;

As you know with Azure, things get added so it may well catch up.

That said, there are three events available which would allow you to track DDL changes on your v12 Azure database:

object_altered
object_created
object_deleted

Based on these three events, I scripted this Extended Events session to track DDL on my v12 Azure database:

IF EXISTS ( SELECT * FROM sys.dm_xe_database_sessions WHERE name = 'xe_track_DDL_changes'  )
DROP EVENT SESSION xe_track_DDL_changes ON DATABASE
GO

CREATE EVENT SESSION xe_track_DDL_changes ON DATABASE
ADD EVENT  sqlserver.object_altered(
    ACTION ( sqlserver.username ) ),        -- <-- NB this was session_nt_username in on-premise SQL Server
ADD EVENT  sqlserver.object_created(
    ACTION ( sqlserver.username ) ),
ADD EVENT  sqlserver.object_deleted(
    ACTION ( sqlserver.username ) )
ADD TARGET package0.ring_buffer ( SET MAX_MEMORY = 4096 )
WITH ( EVENT_RETENTION_MODE = NO_EVENT_LOSS, MAX_DISPATCH_LATENCY = 5 SECONDS );
GO

ALTER EVENT SESSION xe_track_DDL_changes ON DATABASE STATE = START;
GO

-- Do some ddl (in order to track it)
CREATE TABLE test ( id INT IDENTITY PRIMARY KEY )
ALTER TABLE test ADD addedBy SYSNAME
DROP TABLE test
GO

-- Inspect the ring buffer
IF OBJECT_ID('tempdb..#tmp') IS NOT NULL DROP TABLE #tmp
GO

SELECT IDENTITY( INT, 1, 1 ) rowId, CAST( target_data AS XML ) AS target_data, GETDATE() dateAdded
INTO #tmp
FROM sys.dm_xe_database_sessions s      -- NB this is dm_xe_session_targets in on-premise SQL Server
    INNER JOIN sys.dm_xe_database_session_targets st ON s.[address] = st.event_session_address      -- NB this is dm_xe_sessions in on-premise SQL Server
WHERE s.name = 'xe_track_DDL_changes'
GO

ALTER TABLE #tmp ADD PRIMARY KEY ( rowId );
GO
--CREATE PRIMARY XML INDEX _pxmlidx_tmp ON #tmp ( target_data );  -- !!NB OPTIONAL - may improve performance but needs up to 5x original table size
GO

-- type of change and the user who made the change
SELECT 
    t.rowId,
    e.c.value( '@name', 'VARCHAR(100)' ) ddl_action,
    o.c.value( '.', 'VARCHAR(100)' ) objectName,
    a.c.value( '.', 'VARCHAR(100)' ) [user]

FROM #tmp t
    CROSS APPLY t.target_data.nodes('RingBufferTarget/event') e(c)
        CROSS APPLY e.c.nodes('data[@name = "object_name"]/value') o(c)
        CROSS APPLY e.c.nodes('action/value') a(c)
GO

My results:

Remember to drop your Azure Extended Event sessions when you're not using them. You might also consider an Event File target which writes to an Azure Storage container because the ring buffer can fill up and cycle.

HTH