Cannot create and start Extended Events session in Azure SQL Managed Instance

Question

Hello,

I am trying to create an Extended Events session in Azure SQL Managed Instance.

First I am trying to use SSMS GUI: Extended Events -> Sessions -> New Session...

Add "error_reported" event, configure fields, select Data Storage as event_file.

Next, when I try to leave default "File name on a server" (Error), or type "C:\Error.xel", when pressing OK it throws an error:

A valid URL beginning with 'https://' is required as value for any filepath specified. (Framework Microsoft SqlClient Data Provider)

Error Number: 40538

Severity: 16

State: 3

Then I try to use Azure storage account. When I begin to type "https://", or even paste full URL ("https://storage.blob.core.windows.net/logs/Errors.xel") into "File name on a server" text box in GUI, "OK" button becomes greyed out immediately, and displays "File name cannot contain invalid characters", so I can't create the session.

Worth mentioning that my SSMS is version 20.1.

I try to bypass it by pressing Script button, with "C:\Errors.xel" file name, and then edit the script and replace it by URL to my storage account, and then try to run the creation script, it succeeds, BUT - when I try to start the newly created session in SSMS object explorer (Start Session), it fails with error:

The target, "5B2DA06D-898A-43C8-9309-39BBBE93EBBD.package0.event_file", encountered a configuration error during initialization.

Object cannot be added to the event session. (null) (Framework Microsoft SqlClient Data Provider)

Error Number: 25602

Severity: 17

State: 23

This way, I am unable to create an Extended Events session that would use file in storage account container as a target.

Also to mention that Azure SQL Managed Instance and storage account are in same region (West US 2), they do not use Zone Redundancy. Storage account is Locally Redundant Storage. SQL MI has credentials configured (SAS token), it can back up databases to this storage account to any container.

What is missing, how can above errors be solved, so I can create Extended Events session on Azure SQL Managed Instance ?

Answer

Creating an Extended Events session in Azure SQL Managed Instance (MI) requires some specific configurations, especially when it comes to setting up the event file target. Azure SQL MI does not support the traditional file paths like C:\Errors.xel and instead expects a URL to an Azure Blob Storage. Here’s how you can set it up correctly:

Steps to Create an Extended Events Session with Azure Blob Storage as the Target:

Create a Storage Account and Container:
- Ensure you have a storage account and a container within that account where the Extended Events files will be stored.
Generate a Shared Access Signature (SAS) Token:
- Generate a SAS token with appropriate permissions (Read, Write, List, Delete) for the container.
Create a Credential in SQL Managed Instance:
- Create a credential in your Azure SQL MI to use the SAS token.


CREATE CREDENTIAL [https://yourstorageaccount.blob.core.windows.net/yourcontainer]

WITH IDENTITY = 'SHARED ACCESS SIGNATURE',

SECRET = 'your_sas_token';

Create the Extended Events Session:
- Use T-SQL to create the Extended Events session with the correct URL format for the file target.

Example T-SQL Script:


-- Create the Extended Events session

CREATE EVENT SESSION [ErrorReportingSession] ON SERVER

ADD EVENT sqlserver.error_reported(

    ACTION(sqlserver.sql_text, sqlserver.database_id, sqlserver.nt_username)

    WHERE ([severity] >= 16))

ADD TARGET package0.event_file(

    SET filename = 'https://yourstorageaccount.blob.core.windows.net/yourcontainer/Errors.xel',

    max_file_size = 5, max_rollover_files = 2)

WITH (MAX_MEMORY = 4096 KB, EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS, MAX_DISPATCH_LATENCY = 30 SECONDS, MAX_EVENT_SIZE = 0 KB, MEMORY_PARTITION_MODE = NONE, TRACK_CAUSALITY = ON, STARTUP_STATE = OFF);

-- Start the session

ALTER EVENT SESSION [ErrorReportingSession] ON SERVER STATE = START;

Explanation:

Filename: The filename must be a valid URL starting with https://. This URL points to your Azure Blob Storage container where the event files will be stored.
SAS Token: The credential created with the SAS token allows the SQL Managed Instance to write to the specified blob storage.

Troubleshooting Common Issues:

Invalid Characters in File Name:
- Ensure that the filename does not contain invalid characters. It should be a valid URL.
Configuration Error During Initialization:
- Ensure the storage account and SQL MI are in the same region.
- Verify that the SAS token has the correct permissions.
- Ensure the credential is correctly created and matches the URL used in the Extended Events session.
Credential Configuration:
- Double-check the credential's identity and secret. The identity should be SHARED ACCESS SIGNATURE, and the secret should be the SAS token itself.

Example to Verify Configuration:


-- Verify Credential

SELECT * FROM sys.credentials

WHERE name = 'https://yourstorageaccount.blob.core.windows.net/yourcontainer';

-- Check Extended Events sessions

SELECT * FROM sys.server_event_sessions;

SELECT * FROM sys.dm_xe_sessions;

If you follow these steps and ensure the configuration is correct, you should be able to successfully create and start an Extended Events session in Azure SQL Managed Instance using Azure Blob Storage as the target. If you encounter any specific errors, examining the error messages and cross-referencing with the setup steps can help identify and resolve issues.

Answer

Answer:

I was having issue on Premium LRS BlockBlob. When we created new storage account for testing - Standard RA-GRS General Purpose v2 - we could create extended events session and start it.

Also got advice from Azure support:

"We have received your query regarding the issue you are facing while trying to upload a “.xel” file. I've reviewed the backend logs of the “storageaccountblockblob” and observed that you are getting "page blob is not supported for this account type" error. This error indicates that the "Premium block blobs" account type you are currently using does not support page blobs.

To resolve this issue, you will need to upgrade to a premium page blobs account type which will allow you to perform the desired operation without encountering an error."

@Oury Ba-MSFT this would be the answer to this question

Answer

@Aleksey Vitsko Issue: Cannot create and start Extended Events session in Azure SQL Managed Instance