Press "Enter" to skip to content

Curated SQL Posts

Column-Level Encryption in SQL Server

Published 2021-05-18 by Kevin Feasel

David Fowler takes us through a venerable (here by which I mostly just mean “old”) technique for encryption in SQL Server:

SQL Server gives us a few different options when it comes to encryption and I’m going to take a look at each of them at some point in this series but in this first post in the series I want to look at column level encryption.

Before we can even start thinking about encrypting our data, there are a few things that we’re going to need to set up first.

Although I joke about column-level encryption, David shows us just how easy it is to implement. It’s quite useful if you have just one or two columns in the database which need to be encrypted at rest and you don’t want to (or can’t) have the application handle it directly.

Comments closed

Grouping Outputs of Pester Tests

Published 2021-05-18 by Kevin Feasel

Shane O’Neill has fun with Pester:

I’ve been working with Pester v5 lately.

Pester v5 with PowerShell v5 at work & Pester v5 with PowerShell Core outside of work.

There are quite a few changes from Pester version 3, so it’s almost like learning a new language… except it’s based on slang. I think that I’m speaking eloquently, and then I’ve suddenly insulted someone and Pester no longer wants to play nice with me.

Read on to see how to make those Pester outputs look a lot nicer.

Comments closed

Running SQL Server Containers from Scratch

Published 2021-05-18 by Kevin Feasel

Andrew Pruski tells us there is no spoon:

I’ve been interested (obsessed?) with running SQL Server in containers for a while now, ever since I saw how quick and easy it was to spin one up. That interest has led me down some rabbit holes for the last few years as I’ve been digging into exactly how containers work.

The weirdest concept I had to get my head around was that containers aren’t actually a thing.

Containers are just processes running on a host that implement a set of Linux constructs in order to achieve isolation.

So if we know what constructs are used…shouldn’t we be able to build our own container from scratch?

Read on as Andrew breaks out the three necessary constructs and dives into it.

Comments closed

Plotting Correlation Analyses in R

Published 2021-05-17 by Kevin Feasel

Finnstats shows a few techniques for plotting correlation in R:

Correlation analysis, correlation is a term that is a measure of the strength of a relationship between two variables.

Pearson’s Product-Moment Correlation

One of the most common measures of correlation is Pearson’s product-moment correlation, which is commonly referred to simply as the correlation, or just the letter r.

Correlation shows the strength of a relationship between two variables and is expressed numerically by the correlation coefficient.

Click through for examples from several packages. H/T R-Bloggers.

Comments closed

Errors and Return Codes in SQL Agent Powershell Job Steps

Published 2021-05-17 by Kevin Feasel

Ron the Polymath has a framework:

PowerShell job steps offer a lot of advantages, but when things don’t work as expected, it can frustrating to understand why. Things like when a non-zero exit code reports the step as successful. Some important points I found with PowerShell steps (especially the first item):

Read on for those interesting points, for a block of Powershell code you can use to track errors, and a SQL Agent job template to boot.

Comments closed

Counts of Last-Known States of Items with DAX

Published 2021-05-17 by Kevin Feasel

Phil Seamark has an interesting problem:

The requirement was simple enough. Take the following dataset and, for any given day, produce a count of each possible State using the last known State for any given TestID. The dataset contains six unique Test IDs (A through F). At any given point in time, we first want to establish the last State for each TestID. We also want to group this by day and produce a count value for each possible State. Note, a given TestID can have more than one event in a day, and we only care about the last one.

I’m particularly interested in this because I find a lot of merit in the event-based structure in Phil’s input dataset, but it can be tricky going from that to data in a shape the customer likes.

Comments closed

To Cloud or Not to Cloud

Published 2021-05-17 by Kevin Feasel

That is the question, according to Guy Glantser:

This is not a regular blog post. I was looking for an old blog post that I wrote several years ago, and while searching, I found an even older blog post that I wrote back in 2009. It had the same title that you see here – To Cloud or Not to Cloud?

In 2009 the cloud was already a thing, but it was the early days. Microsoft’s cloud, Azure, wasn’t even announced yet until February 2010. The cloud has seen a tremendous advancement over the years. It’s interesting and also amusing to read what I wrote 12 years ago about the cloud. Some things are still true today, while others are completely irrelevant.

So here it is…

It’s good to reflect back on these thoughts to see how the industry has shifted. Issues which were show-stoppers may be completely eradicated by this point, while others remain trade-offs without an ideal answer.

Comments closed