Extended Events – Using Azure Storage

Last year I wrote about Azure SQL Database extended events (https://blobeater.blog/2017/02/06/using-extended-events-in-azure/)  and gave an example where I was capturing deadlocks via the ring buffer. Ever since then I wanted to do a follow-up post but using Azure storage as the target for my XEL files.

This is more complicated than using the ring buffer as the target and requires a couple of things:

  • Azure storage account where you create a dedicated container for the files.
  • SAS key.
  • Database master key.
  • Database scoped credential.

Azure Storage

I decided to create a new storage account then a container within the account. To do this you navigate to the “Blobs” section shown below then click the add container option.

BlobCont

My files container as shown below.

FileBlob

Next I navigate to the settings section of the storage account and find the shared access signature option.

SASMain

Here you will need to setup the permissions allowed to what services, IP addresses and give a start and expiry time.

sasperms

Once you have tweaked this to YOUR requirements select the generate SAS blue button. This will give you something like (I have obfuscated the true SAS):

?sv=2023-07-29&ss= &se=2023-05-07T15:57:09Z&st=2018-05-02T07:57:09Z&spr=https,http&sig=svu9SdFSlLFFgdsp39Sf15ZsdsdHELLOSHANEM4%DDfssdD3D

This is really important and will be needed in the upcoming steps.

Everything Else

The rest is TSQL. At a high level we will create a database master key then a database scoped credential where its secret is the SAS we generated from Azure storage (all obfuscated).

Then we will be able to create an extended event session where the target is the storage account container. All this code needs to be executed within the user database.

Read the comment line – the secret starts AFTER the ? from the above SAS.

 CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'mv8f5D24DdQLAL0C34CsA96E3FC346'

CREATE DATABASE SCOPED CREDENTIAL

        [https://extendedeventsfiles.blob.core.windows.net/files]
    WITH
        IDENTITY = 'SHARED ACCESS SIGNATURE',  -- "SAS" token.
        -- Paste in the long SasToken here for Secret BUT AFTER the ? only
        SECRET = 'sv=2023-07-29&ss= &se=2023-05-07T15:57:09Z&st=2018-05-02T07:57:09Z&spr=https,http&sig=svu9SdFSlLFFgdsp39Sf15ZsdsdHELLOSHANEM4%DDfssdD3D'
    ;
GO

Next the extended events session, here I am capturing deadlocks.

CREATE EVENT SESSION azure_monitorV2
ON DATABASE

ADD EVENT sqlserver.sql_statement_completed
( ACTION (sqlserver.sql_text,sqlserver.database_name)),
ADD EVENT sqlserver.database_xml_deadlock_report

    ADD TARGET
        package0.event_file
            (

            SET filename =
                'https://extendedeventsfiles.blob.core.windows.net/files/code.xel'
            )
    WITH
        (MAX_MEMORY = 50 MB,
        MAX_DISPATCH_LATENCY = 3 SECONDS)
    ;
GO

When you start the session you do not want to see red error messages – if everything goes well you should see the file within your storage container.

ALTER EVENT SESSION azure_monitorV2
ON DATABASE
STATE = START;

pageblob

That’s great – but querying the file is most important.

 SELECT
        object_name,file_name,timestamp_utc,
        CAST(event_data AS XML) AS [event_data_XML]
    FROM
        sys.fn_xe_file_target_read_file
            (

                'https://extendedeventsfiles.blob.core.windows.net/files/code',
                null, null, null
            ) WHERE object_name = 'database_xml_deadlock_report';
GO

2DLs

Click the XML to give you the deadlock information.

exampleDL

Thanks for reading.

 

 

One thought on “Extended Events – Using Azure Storage

  1. Pingback: Writing Extended Events To Blob Storage – Curated SQL

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s