Press "Enter" to skip to content

Curated SQL Posts

Using Query Labels in Azure Synapse Analytics

Published 2021-09-27 by Kevin Feasel

Gauri Mahajan shows one of the pieces of functionality in Azure Synapse Analytics dedicated SQL pools that I’d like to see on-premises:

Azure Synapse supports a concept known as “query labels” that allows tagging any DDL or DML queries that are executed on the dedicated SQL pool. These labels can be queried using the dynamic management views (DMVs). One can use these labels to describe the purpose of the query or add any metadata to the query being executed and the same can be used later for instrumenting the queries, specifically to identify the queries that meet the desired search criteria. Let’s walk through a step-by-step exercise to understand this concept practically.

Click through for the process.

Comments closed

Setting File Permissions inside a SQL Server Container

Published 2021-09-27 by Kevin Feasel

Anthony Nocentino wants to set some permissions:

This post will walk you through setting file permissions on database files copied into a container. The SQL Server process sqlservr running in containers runs as the non-privileged user mssql. The appropriate permissions on files are needed, so the SQL Server process has the proper access to any database files, log files, and backup files.

Click through for the process.

Comments closed

SQL Server Environment Variable Files in Containers

Published 2021-09-27 by Kevin Feasel

Andrew Pruski shows how you can pre-set environment variables when building a SQL Server on Linux container:

Do we really want to be typing all that out every time we run a container? Ok, we could drop this into a script and execute that but another option is to use environment variable files.

Nice and simple, we take all the environment variables in the statement above and drop them into a file on our Docker host: –

Read on and gauge for yourself how nice and simple it is.

Comments closed

The Limitations of ORMs

Published 2021-09-27 by Kevin Feasel

Erik Darling gives us some hints on when it might be time to stop using that ORM:

There are, unfortunately, some times when developers refuse to put the ORM down.

I mean, it’s mostly unfortunate for them, because they’ll continue to have performance problems.

Me? I’ll be okay.

The items in this post are issues I’ve run into constantly when working with people who use ORMs, but don’t spend any time looking at the queries they generate.

An important note is that “stored procedure or ORM” is a false choice—most modern ORMs will allow you to generate objects based off of stored procedures, so you can stick with the ORM for the parts it does well but switch to a stored procedure when things get real. Or just use stored procedures across the board and have your ORM act as an auto-mapper for them. That’s an option too.

Comments closed

Toggling Word Wrap in SSMS

Published 2021-09-27 by Kevin Feasel

Ronen Ariely shows how to enable word wrap in SQL Server Management Studio:

Line breaking, also known as word wrapping, is breaking the displayed of a section of text into lines so that the text will fit into the available width of the editor. When writing queries this feature is not so useful as breaking the script line may make the query less readable, but when writing long comments this feature become one of the most useful feature. 

This post simply shows you how to use word-wrap by default or add a command button to Toggle Word Wrap – it’s a built-in feature which is less known and if you did not used it yet, then it is time to use the power of word wrap

Because T-SQL is not line or whitespace sensitive, my preference is to break lines well before they hit the point where word wrap makes sense. But if you’re working with some lengthy lines of code or on a low-resolution laptop, this can help a lot.

Comments closed

Synapse vs Snowflake

Published 2021-09-24 by Kevin Feasel

Travis Manning has a throw-down:

Data warehousing has become a hot topic for most organizations as data volume grows exponentially, and yet the capacity to manually manage it all but diminishes. The ecosystem is replete with options, each with a host of features and integrations. In this article, we will discuss two of the most common (and commonly discussed!) data warehousing services, Azure Synapse and Snowflake Data Warehouse (DW). For this article, we will try to focus on use cases, and which option is appropriate in that context.

Click through for the product comparison. One big difference not covered is pricing uncertainty. If you have a good understanding of the number of executions and computational complexity of your queries, as well as data quantities, Snowflake can be very competitively priced. But what can happen is that the competitive price turns into a much-less-competitive price by the time you’re fully up to speed.

1 Comment

Deploying Synapse Artifacts to a Managed vNet Workspace

Published 2021-09-24 by Kevin Feasel

Rui Cunha takes us through an Azure Synapse Analytics deployment scenario:

In my previous article, I demonstrated how we could easily use the Synapse Workspace Deployment extension to accomplish this second stage of the process. I’m now coming back to this topic as I realized that many of our customers were reporting difficulties in completing this second stage of their Synapse CICD process because they were failing to deploy Synapse artifacts to a Managed VNET Synapse Workspace.

In this particular scenario, the deployment was failing because their target workspace was not allowing access from public networks.

Fortunately, the answer isn’t “Allow access from public networks.” Click through to see what you can do instead.

Comments closed

Using Azure Queue Storage as a Trigger for Function Apps

Published 2021-09-24 by Kevin Feasel

Aveek Das shows how you can use Azure Queue Storage as a way to trigger an Azure Function App:

In this article, we are going to learn how to trigger Function Apps from Queue Storage in Azure. Function Apps has been one of the most popular cloud services of Microsoft Azure. Function Apps allow users to write code in any language and then execute the code in the cloud. There is no infrastructure to be managed and hence is very flexible for writing and building applications on the go. Every Function App can be triggered in multiple ways, for example, by calling the function URL using an HTTP endpoint or from some other functions in Azure. In this article, we are going to trigger the Function App from Queue Storage in Azure and see how to pass a message from the queue to the Function App.

Queue Storage in Azure is another service in Azure that allows users to store multiple messages in it. Users can use a queue to create a list of items that need to be processed one by one. Messages to Queue Storage in Azure can be added by using the HTTP or HTTPS endpoints. Usually, a queue can store data up to 64 KB in size. We can add millions of messages in a queue if it is supported by the storage account.

Click through to see how. Though now I wonder why I might use Queue Storage instead of an Event Hub or an Event Grid. But I suppose that’s a question for a different article.

Comments closed

Capturing SQL Server Login Details with extended Events

Published 2021-09-24 by Kevin Feasel

Jack Vamvas shows how to track SQL Server logins:

I have to capture logon information details for a specific logon on a SQL Server.   Specifically – the client_hostname, nt_username & username. What i’m looking for is a log recording a successful connection made to the server.     The event should be triggered a) when a connection is made & b)   from a connection pool. 

Click through to see how.

Comments closed