Azure SQL Database – How to Import CSVs and Set Up Push Notifications

These times where your connection is forced closed is usually an indication of throttling. I have found that the main reason for throttling is from high numbers of outstanding requests within your database. Unfortunately there are no hard limits that can be monitored to identify when this throttling occurs. The good news is that the lower limits have been documented which means you can track when you are getting close to the upper limits.

To give a plug to our Cotega monitoring service, this is one of the main things we track to allow people to be notified when they approach these upper limits. Another useful thing to do when this happens is to do a deep database diagnostics to see what expensive queries, connections, etc are occurring within your database as described on How to Perform SQL Azure Performance & Diagnostics Analysis for SQL Azure.

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