Using Extended Events Live Data With Azure

In my last post I showed some shortcomings of Extended Events, however, it is possible to use Live Data with Azure. Let’s explore exactly how that works.

To get started, you’ll need to follow the directions here to get set up with Azure Storage as the output target of your Extended Events session within your Azure SQL Database. There is a little bit of prep work, but it’s all laid out in Microsoft’s document. I found the Powershell to be a bit sketchy, but it shows you what’s needed. The T-SQL just works.

Live Data With Azure

Once you’ve created an Extended Events Session that is output to Azure Storage, you’ve done most of the work. The trick is really simple. Get the Azure Storage account set up with a Container. Create a Shared Access Signature (SAS) with the right permissions (rwl, read, write, list). Get the token from the SAS (it’s a long string). Use it, along with the path to the container to create a Database Scoped Credential. Create the session using the same path and container that you defined in the Credential. Done. You’ve got an Azure Extended Events session gathering data for you and outputting to a file in Azure Storage.

Now, what I’d like to tell you is that you can open up the Live Data window from SSMS. You can’t.

The limit I outlined in the last post was the inability to use ring buffer as a target and then look at Live Data. However, even if you have the file as a target, you still can’t look at the live data…. live. However, here’s what you can do.

First, go and look at your Azure Container. You should be able to see the *.xel output from your Extended Events Session:

I named the file “azurelivedata.xel’ in my Session definition for the target. You’ll not that it appended a string. This is for file rollover purposes, same as before.

Click on the file itself. You’ll see a bunch of detail about the file:

The import part is right at the top. There’s a Download button. Yes, I’m serious. But here’s the trick. My session is actively running. It’s collecting data. Yet, I can still download it with no issues. Open the file within SSMS, and you’ll see a pretty familiar sight, Live Data Explorer:

From there, all the tips & tricks and various stuff I’ve shown in how to use the Live Data window are all available to you.

Conclusion

Yeah, I’m not going to try to sugar coat it. This is a bit of a pain. There are ways to reduce the pain. Obviously, putting PowerShell to work to automate the download. You could use a VM in the cloud to browse the data there, it might be easier than downloading to your own machine. There are also ways to map your storage to a drive, which would enable you to open the file more directly. Otherwise, to take advantage of Live Data with Azure SQL Database, you’re going to have to go get the file.