{"id":92109,"date":"2021-09-13T17:00:48","date_gmt":"2021-09-13T17:00:48","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=92109"},"modified":"2021-09-21T17:51:11","modified_gmt":"2021-09-21T17:51:11","slug":"azure-sql-extended-events-and-the-use-of-slash","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/blogs\/azure-sql-extended-events-and-the-use-of-slash\/","title":{"rendered":"Azure SQL: Extended Events and the use of slash “\/”"},"content":{"rendered":"

Extended Events<\/strong> 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 (“\/”).<\/p>\n

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

\nCreate a credential for authentication<\/h2>\n

Some features don’t support a direct link with a credential object to enable the authentication to private storage.<\/p>\n

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

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:<\/p>\n

CREATE<\/span>\u00a0database<\/span>\u00a0scoped<\/span>\u00a0CREDENTIAL<\/span>\u00a0[https:\/\/maltacentralstorage.blob.core.windows.net\/extendedevents]<\/span>
\nWITH<\/span>\u00a0IDENTITY<\/span>=<\/span>‘SHARED\u00a0ACCESS\u00a0SIGNATURE’<\/span>
\n,<\/span>\u00a0SECRET<\/span>\u00a0=<\/span>\u00a0‘***\u00a0SAS\u00a0KEY\u00a0***’<\/span> <\/span><\/div>\n

However, if you include the slash in the end of the credential [https:\/\/maltacentralstorage.blob.core.windows.net\/extendedevents\/]<\/em>, it doesn’t work. When you try to enable an Extended Events<\/strong> session linked to this credential, SQL Server<\/strong> returns the following error message:<\/p>\n

Msg 25602, Level 16, State 1, Line 26
\nThe target, “5B2DA06D-898A-43C8-9309-39BBBE93EBBD.package0.event_file”, encountered a configuration error during initialization. Object cannot be added to the event session. (null)<\/h6>\n

This message means the target of Extended Events<\/strong> could not be initialized, because the credential is wrong.<\/p>\n

\nExtended Events Target URL<\/h2>\n

The syntax to create a simple Extended Events<\/strong> Session is this:<\/p>\n

CREATE\u00a0EVENT<\/span>\u00a0SESSION<\/span>\u00a0[BlockedProcess2]<\/span>\u00a0ON<\/span>\u00a0DATABASE<\/span>
\nADD<\/span>\u00a0EVENT<\/span>\u00a0sqlserver<\/span>.<\/span>blocked_process_report<\/span>
\nADD<\/span>\u00a0TARGET<\/span>\u00a0package0<\/span>.<\/span>event_file<\/span>(<\/span>SET<\/span>\u00a0filename<\/span>=<\/span>N’https:\/\/maltacentralstorage.blob.core.windows.net\/extendedevents\/queries’<\/span>)<\/span>
\nWITH<\/span>\u00a0(<\/span>STARTUP_STATE<\/span>=<\/span>ON<\/span>)<\/span>GO<\/span> <\/span><\/div>\n

\nOn this example we are using the URL<\/strong> on the filename attribute of the target element on the session. The core question about this URL is: What exactly ‘queries’<\/em> means, a file name or a folder?<\/p>\n

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.<\/p>\n

Here comes the answer to the questions: What “queries”<\/em> is? What difference the slash makes in this example?<\/p>\n

If the URL<\/strong> has a slash in the end, “queries”<\/em> will be considered to be a folder. The files will be stored inside the folder and the names will all start with “_”<\/em> and have a generated code.<\/p>\n

\"\"<\/p>\n

If the URL<\/strong> hasn’t a slash in the end,\u00a0 XE will use\u00a0“queries” <\/em>\u00a0as part of the file name. The files will be stored directly inside “extendedevents”<\/em> container and the file names will start with “queries_”<\/em> and a generated code.<\/p>\n

 <\/p>\n

\"\"<\/p>\n

 <\/p>\n

A simple slash can send the files to a different place.<\/p>\n

Conclusion<\/h2>\n

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

One additional detail about this is that Extended Events<\/strong> don’t support Azure Blob Storage<\/strong> with Hierarchical Namespaces<\/strong> enabled.\u00a0<\/p>\n","protected":false},"excerpt":{"rendered":"

Many DBA skills transfer when moving to the cloud, but you’ll have to make some adjustments in many cases. In this post, Dennes Torres explains one of the differences when setting up Extended Events sessions in Azure SQL Database.…<\/p>\n","protected":false},"author":50808,"featured_media":92446,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[2],"tags":[5364,136322,5679,4151],"coauthors":[6810],"class_list":["post-92109","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-blogs","tag-azure","tag-azure-sql","tag-extended-events","tag-sql-server"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/92109","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/50808"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=92109"}],"version-history":[{"count":5,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/92109\/revisions"}],"predecessor-version":[{"id":92462,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/92109\/revisions\/92462"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media\/92446"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=92109"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=92109"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=92109"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=92109"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}