Azure SQL: Extended Events and the use of slash “/”

Extended Events is a very important feature to monitor Azure SQL. Recently I faced a very interesting behavior of this feature in relation to the use of the slash (“/”).

URL’s use the “/” on it. However, the “/” has different meanings depending on where in the URL it appears.

Create a credential for authentication

Some features don’t support a direct link with a credential object to enable the authentication to private storage.

The work-around created for these features was to create a credential name equal to the URL of the storage. When SQL Server tries to access the storage, it identifies the credential and uses it for the authentication. Extended Events is one of the features which needs this work around.

I discovered on the worst way the problem a simple slash can cause: The credential can’t have a slash in the end. The syntax to create the credential is this:

CREATE database scoped CREDENTIAL [https://ift.tt/393tnvl]
WITH IDENTITY=‘SHARED ACCESS SIGNATURE’
, SECRET = ‘*** SAS KEY ***’

However, if you include the slash in the end of the credential [https://ift.tt/3k71YPp], it doesn’t work. When you try to enable an Extended Events session linked to this credential, SQL Server returns the following error message:

Msg 25602, Level 16, State 1, Line 26
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)

This message means the target of Extended Events could not be initialized, because the credential is wrong.

Extended Events Target URL

The syntax to create a simple Extended Events Session is this:

CREATE EVENT SESSION [BlockedProcess2] ON DATABASE
ADD EVENT sqlserver.blocked_process_report
ADD TARGET package0.event_file(SET filename=N’https://ift.tt/395awA8;)
WITH (STARTUP_STATE=ON)GO

On this example we are using the URL on the filename attribute of the target element on the session. The core question about this URL is: What exactly ‘queries’ means, a file name or a folder?

In order to understand the answer, we need to understand how XE will save the files in the destination. The events are saved in many files, not a single one. The default configuration will create 1GB files. Extended Events builds the name of the files dynamically.

Here comes the answer to the questions: What “queries” is? What difference the slash makes in this example?

If the URL has a slash in the end, “queries” will be considered to be a folder. The files will be stored inside the folder and the names will all start with “_” and have a generated code.

If the URL hasn’t a slash in the end,  XE will use “queries”  as part of the file name. The files will be stored directly inside “extendedevents” container and the file names will start with “queries_” and a generated code.

 

 

A simple slash can send the files to a different place.

Conclusion

Different features can use the slash in different ways and this can get quite confusing sometimes. In this blog I mentioned only Extended Events, there are many more with different uses for a slash.

One additional detail about this is that Extended Events don’t support Azure Blob Storage with Hierarchical Namespaces enabled. 

The post Azure SQL: Extended Events and the use of slash “/” appeared first on Simple Talk.

from Simple Talk https://ift.tt/3BZeLcS
via