Press "Enter" to skip to content

Curated SQL Posts

Comparing Encryption Options in SQL Server

Published 2023-01-18 by Kevin Feasel

David Fowler has a table for us:

The question of encryption seems to be coming up a lot recently. I’ve had a number of people asking me about how to go about encrypting SQL Server.

SQL can encrypt our data at a number of different levels and gives us a quite a few options when doing so. I want to use this post to put together a matrix so you can easily see which method of encryption suits your purpose. I’ll look in to each method in more detail in a future series of posts.

Click through for a rather comprehensive table and remember that “several of these all at once” is usually the correct answer for which to choose.

Comments closed

Pivoting with KQL

Published 2023-01-17 by Kevin Feasel

Robert Cain continues a series on KQL:

Business Analysis is becoming mainstream in today’s corporate world. A big part of that analysis is done with pivot tables. Think of an Excel spreadsheet where data is organized into rows and columns.

The pivot plugin will take one data column from your query, and flip it to become new columns in the output data grid. The other column will become the rows, and an aggregation function will be at the cross section of the rows and columns, supplying the main data. You’ll get a better understanding through the demos in this post.

You may be wondering “plugin? What’s a plugin?”

I did, in fact, wonder. And Robert explains what a plugin is, as well as examples of how to pivot.

Comments closed

PolyBase and S3 Integration in SQL Server 2022 on Containers

Published 2023-01-17 by Kevin Feasel

Amit Khandelwal combines a bunch of things together:

One of the new features introduced with SQL Server 2022 is the ability to connect to any S3-compatible object storage and SQL Server supports both Backup/Restore and data lake virtualization with Polybase integration.  In this blog, we will demonstrate both of these features for SQL Server 2022 Containers running on Kubernetes. As usual, I will use the Azure Kubernetes Service as my Kubernetes environment

Most of the work is in the container configuration, which is good on net, as it means you only have to do it once.

Comments closed

SSMS and Additional Connection Parameters

Published 2023-01-17 by Kevin Feasel

Tom Zika gets spun around a bit:

The other day I managed to confuse myself. I was looking up some information from an Extended Events (XE) session, but my eyes were playing a trick on me. The database ids were off by one, and I couldn’t find some query hashes in the Query Store, even when they were supposed to be there. So my first thought was that I must be connected to a different server with a drift. But the information in the SSMS tab, status bar and even colour coding (courtesy of Redgate’s SQL Prompt) – all pointed to the correct server.

I don’t think I’ve ever run into this before but could see how it’d be really confusing.

Comments closed

Debugging DAX Measures in Power BI

Published 2023-01-17 by Kevin Feasel

Marco Russo and Alberto Ferrari bust out the oscilloscope:

Finding errors in DAX measures has always been difficult because a single DAX measure produces different results in different cells of the same report, and we do not have a direct way of executing the code step by step in Power BI. This article describes four techniques to find an error in a DAX formula: we start with variable manipulation that does not require any external tool, and then we see how to leverage features available in DAX Studio, DAX Debug Output, and Tabular Editor 3.

Do check this out, especially if you spend a lot of time writing DAX code.

Comments closed

CETAS in the Serverless Pool and Blob Storage Variants

Published 2023-01-17 by Kevin Feasel

Dennes Torres gives us a warning:

While making some CETAS tests, I discovered an interesting new behaviour. The following error message was displayed and it was very strange:

Msg 16539, Level 16, State 1, Line 1
Operation failed since the external data source ‘https://euwe01devqigsa01.blob.core.windows.net/dennes/filescsv/’ has underlying storage account that is not in the list of Outbound Firewall Rules on the server. Please add this storage account to the list of Outbound Firewall Rules on your server and retry the operation.

Read on for the cause of this error message.

Comments closed

Using Power BI Powershell Cmdlets

Published 2023-01-17 by Kevin Feasel

Reza Rad explains the value of the Power BI Powershell cmdlet set:

Power BI has a set of PowerShell Cmdlets that help automate part of the operations with Power BI. However, PowerShell is not a commonly familiar technology. In the world of Power BI, we are used to working with graphical options and settings provided in the tools and the service. However, using commands provided for Power BI in a command/scripting tool such as PowerShell can be an excellent asset for a Power BI administrator, architect, and developer. In this article and video, you will learn about the PowerShell Cmdlets for Power BI, what they are, how they can be helpful, and examples of using them.

There are more modules than I expected there to be and Reza does a good job of walking through them.

Comments closed

Azure SQL DB Lessons Learned

Published 2023-01-17 by Kevin Feasel

Kendra Little shares a few lessons:

When I think about service objectives, I tend to assume we’re talking about guaranteed uptime, often expressed in some number of 9’s.

But Azure SQL Database has its own language. The best resource I know of to learn that language is the Azure SQL glossary of terms.

Read on for three lessons, two of which are pretty straightforward but the third one is liable to strike without you realizing.

Comments closed

Spark Structured Streaming with Synapse

Published 2023-01-16 by Kevin Feasel

Ryan Adams builds a demo:

In this post we are going to look at an example of streaming IoT temperature data in Synapse Spark.  I have an IoT device that will stream temperature data from two sensors to IoT hub. We’ll use Synapse Spark to process the data, and finally write the output to persistent storage. Here is what our architecture will look like: 

Click through for the architectural diagram and step-by-step on how to put the demo together.

Comments closed