Extracting DDL Events for a database on Azure

Question

I have been trying to extract DDL changes from SQL Server via Extended Events, we needed to know the type of change and the user who made the change. We wanted to avoid the use of triggers where possible and liked the idea of extended events as they are not part of the running transaction when a schema change is made.

Doing this on an on-premise server seems to work pretty well, populating a queue from a notification listening for DDL_DATABASE_LEVEL_EVENTS.

However, I need this solution to work on Azure so my first thought was to do exactly the same but the service broker does not work on Azure so I then looked at using EVENT SESSION sending the information to a ring buffer but this does not appear to be able to get the same events.

Is it possible to get this information on Azure via the ring buffer or do I have to revert to using triggers?

Answer 1

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