Press "Enter" to skip to content

Curated SQL Posts

Four Years of sp_CheckSecurity

Published 2026-11-11 by Kevin Feasel

Jeff Iannucci reflects on a tool:

In recent weeks I’ve been conducting a bit more of our own internal SQL Server Security Checks than usual for our clients. This shouldn’t be a surprise, what with all the stories about “rouge AI” breaches and with Microsoft releasing A LOT more security fixes in their recent Windows and SQL Server updates.

In all these Security Checks we use our sp_CheckSecurity stored procedure to collect a big chunk of the findings. I wrote the original version of this tool a while back, and out of curiosity I wanted to see how long ago that was. It turns out I created the first working version of the tool four years ago this week. Wow, four years! And the things that I have found using that tool could, as my late stepfather might say, make your eyes water.

Inspired by my colleague Andy Kelly’s post introducing the sp_CheckActivity tool that he’s been working on in various forms for years, I thought I would share a bit about the history and intention of sp_CheckSecurity in a similar manner.

Read on for its origin story. Between what Jeff has put together and what Andreas Wolter has created, there are two really good procedures for evaluating the security posture of a given SQL Server instance.

Leave a Comment

Building a Python API atop SQL Server Data

Published 2026-11-11 by Kevin Feasel

Hristo Hristov builds an API:

SQL Server and its cloud cousin Azure SQL are among the most popular relational database management systems. Data hosted in SQL Server sometimes need be securely exposed to outside services and applications. How to build an application programming interface (API) layer that securely exposes data stored in SQL Server to downstream services or consumers?

The example here uses FastAPI in Python, which is a good choice for a simple API layer. Regardless of the exact tooling or programming language you use, there’s a lot to proper API design, documentation, versioning, security, and optimization.

Leave a Comment

Optimizing DAX with AI

Published 2026-11-11 by Kevin Feasel

Marco Russo and Alberto Ferrari speed up operations:

The debate over whether AI will replace human work is heating up. However, our vision is different: AI is not a replacement; it is a powerful tool, and when AI and humans work together on a problem, the outcome is better than when either works on their own.

Optimizing DAX is a fascinating task. However, in the process of optimizing DAX, you need to pay attention to several details:

  • The new formula needs to be equivalent to the old one; this requires extensive testing.
  • The shape of the query being executed matters: a formula may be better than another in a specific test, but not in others.
  • The data distribution matters: a database with 100K customers and 100M transactions requires a different optimization compared to one with 10K customers and 10B transactions.

You will often get about as much out of using generative AI tools as you are willing to put in. This article does a good job of showing that.

Leave a Comment

Thoughts on Blog Relevance

Published 2026-11-11 by Kevin Feasel

I’m at PASS Summit this week, so it’s a good thing Jeff Iannucci teed up a topic for me. Jeff, of course, has the honor of putting together the full round-up, but this topic has drawn quite a bit of interest and I get to link to a lot of people.

Andy Brownsword has thoughts on who your reader is, and how that has shifted over the years.

Niko Neugebauer answers in the negative but adds a few caveats.

Mike Walsh says they are, so long as you’re willing to take the time and effort.

Reitse Eskens says yes, especially for the writer.

Marlon Ribunal says yes, and that AI assistance can make the experience better.

Brent Ozar thinks about how relevance has shifted and brings up a good point about managers: blogs provide a signal of your quality to people, including the people who might hire you.

Chad Callihan asks, what’s in it for you?

Mala Mahadevan focuses on a critical aspect: originality. Not necessarily in topic, but in how you present information and what questions you’re asking.

Rob Farley sees writing as more relevant than reading.

Lee Markum uses illicit cat technology to brainwash people into reading his blog. And also has some good thoughts on the relative cost of blogging versus other skill-sharing activities.

Andy Yun takes a trip down memory lane.

Andy Levy digs into history as well, and notes where human experiences can augment (or supplant) technical documentation.

In short, this might amaze you, but most bloggers think that blogging is still relevant.

Leave a Comment

The Continuing Relevance of Blogs

Published 2026-11-10 by Kevin Feasel

Aaron Bertrand lays out an argument:

I read fewer technical blog posts than I used to. This is partly because I find information differently, and partly because I’m 30 years into this. My reading habits say more about where I am in my career than about the value of blogging.

I write fewer technical blog posts than I used to, too. This is mostly a function of time and juggling work and life priorities, rather than a lack of ideas or perceived value in sharing them.

I still value blogs as places to learn, share ideas, and express opinions.

Given my entire schtick of finding and linking to interesting blog posts, I am naturally inclined to agree with Aaron’s points.

Leave a Comment

Building a Type 2 SCD with dbt Snapshot

Published 2026-11-10 by Kevin Feasel

Koen Verbeeck creates a dimension:

An SCD Type 2 dimension preserves history by inserting a new record every time a tracked column changes, rather than overwriting the old value. Building one by hand usually means writing custom merge logic, but dbt’s built-in dbt snapshot feature can do this for you without any extra code.

This article picks up where my previous guide on loading a Type 1 slowly changing dimension with macros left off. This time, we’ll use dbt snapshots to build a full SCD Type 2 customer dimension in a Microsoft Fabric warehouse: from the underlying view, through the snapshot configuration, to the final dimension model.

Click through to see how.

Leave a Comment

The IsAvailableInMDX Flag in Power BI Semantic Models

Published 2026-11-10 by Kevin Feasel

Phil Seamark explains:

There is a property in Power BI semantic models you can set per column called IsAvailableInMDX. When most people discuss this property, the first question often asked is, “Are you using Excel to query the semantic model?”. If the answer is no, the usual follow up is “turn it off everywhere, you will save memory and your refresh will be faster”.

For Import models, turning it off can save memory by removing a supporting structure from a column. But a DAX query may have been making good use of that structure, particularly when filtering the column. Direct Lake is different: it uses its column dictionary for these DAX optimisations, but the setting still matters for Excel field availability.

Read on to learn whether your environment might benefit from this, as well as other considerations.

Leave a Comment

Reviewing the Always Encrypted Paper

Published 2026-11-10 by Kevin Feasel

Anna Hoffman continues a series on reviewing important academic papers in SQL Server’s history:

Encryption at rest solves one threat. It stops someone who walks off with the disk. It does nothing about someone with administrative access to the running server, because the database decrypts data into memory to do anything useful with it.

For a lot of workloads that gap doesn’t matter. For health records, financial data, or anything where the customer’s threat model includes the cloud operator, it matters a great deal.

Azure SQL Database Always Encrypted (SIGMOD 2020) describes how SQL Server runs real queries, including range comparisons and LIKE patterns, against data it never holds the keys to.

Read on for Anna’s thoughts about the paper, its claims at the time, and how Always Encrypted has changed (for the better) since the original paper.

Leave a Comment

Standing up Active Directory and a Windows Cluster

Published 2026-11-10 by Kevin Feasel

Dualcore DBA is doing some sysadmin work. First up is setting up a domain controller:

In this post, we’ll walk through the process of how I set up an Active Directory (AD) in my home lab. I know, this blog is normally database focused, so why are we looking at Active Directory? Well, yes, it isn’t strictly database related. However, where there is an enterprise SQL Server estate, there is normally Active Directory. In the next few posts, we’ll be looking at Availability Groups, which, whilst configurable without AD, generally are run in AD environments. Also, it isn’t really a great deal of work to set up in the home lab. It takes longer to install the Windows server than to actually set up AD, so it’s not really an issue to set it up to have a more realistic lab environment.

I am using Windows Server 2022 and have set up a specific VM for this purpose which is fairly modest with 2 CPU cores and 4GB of memory.

After that, try on creating a cluster without a domain setup:

This post documents the process of setting up a non-domain Windows Server Failover Cluster, officially known as a Workgroup Cluster. The process is similar to Setting Up a Windows Cluster, however there is a slight difference in this scenario which is that the nodes in the cluster are not part of an Active Directory domain. This process can be used to set up a SQL Server Availability Group where the nodes are not in an Active Directory environment.

And creating a cluster inside of a domain:

In this post, we’ll walk through setting up a Windows Server Failover Cluster (WSFC). This is a requirement of SQL Server Availability Groups, something which we’ll be using on this blog and so I will be setting one up that can be used in the home lab I use to write my blogs. A Windows Failover Cluster is a group of independent servers that work together, continuously monitoring each other, so that if one server fails or goes offline, another can automatically take over its workload with minimal interruption to users. WSFC handles the “which node should be primary and what do we do if it isn’t” on behalf of a SQL Server Availability Group.

Leave a Comment

DOP and Tipping Points

Published 2026-11-09 by Kevin Feasel

Erik Darling gets caught DOPing:

Erik Darling here with Darling Data. And I’ve got a question that I thought was interesting enough to actually answer with a brief demonstration, so I’m going to do that today. But it is about DOP and tipping points, or as I will lovingly refer to them from now on, DOPing points, because why not?

This video is more conjectural in nature and shows an interesting use of MAXDOP(1) to nudge the optimizer toward seeks rather than scans given a certain style of query.

Leave a Comment