Press "Enter" to skip to content

Curated SQL Posts

Dealing with Schema Drift in Azure Data Factory

Published 2025-02-18 by Kevin Feasel

Rayis Imayev deals with change:

I will jump straight to the problem statement without a “boring” introduction, which, in a sense, already feels like an opening statement.

Moving data between two or more endpoints is a common task. Sometimes it’s as simple as migrating data from one place to another. Other times, it’s a request to copy specific documents from source environments. In more complex cases, you might need to consolidate multiple data files into the same destination, such as loading several separate files into a single database table.

This was the bete noir of SSIS’s existence. Minor metadata changes would cause the entire system to break down.

Leave a Comment

Receiving Notification when a Microsoft Fabric Notebook Fails

Published 2025-02-18 by Kevin Feasel

Gilbert Quevauvilliers gets an e-mail:

What I have found is that when I created a pipeline in Microsoft Fabric that uses a notebook, when there is an error with the notebook, I do not get an alert that the notebook has failed.

This has happened to me in the past and I have found this pattern below to work consistently to notify me of errors.

In this blog post I will show you how I get notified when a notebook fails in a pipeline.

Read on to learn how.

Leave a Comment

Backup to URL via Managed Identity in SQL Server 2022

Published 2025-02-18 by Kevin Feasel

Joey D’Antoni doesn’t trust user logins:

Backing up databases to the cloud is not a new thing. Microsoft introduced the BACKUP TO URL functionality to SQL Server 2012 SP1 CU2. I’m not going to tell you how long ago. Still, it wasn’t last month, and Microsoft recently celebrated the 15th anniversary of Azure so that you can get an idea. When the feature started—it was minimal; you could only backup a database of up to a single terabyte and couldn’t stripe over multiple files. Additionally, you had to use the access key to the storage account, which gave complete control over the storage account—that wasn’t a good thing.

Read on for a quick overview of the feature and guidance on how it all works.

Leave a Comment

Migrating or Copying a Semantic Model across Microsoft Fabric Workspaces

Published 2025-02-18 by Kevin Feasel

Sandeep Pawar makes a move:

Here is a quick script to copy a semantic model from one workspace to another in the same tenant, assuming you are contributor+ in both the workspaces. I tested this for a Direct Lake model but should work for any more other semantic model. This just copies the metadata (not the data in the model) so be sure to set up other configurations (RLS members, refresh schedule, settings etc.). That can also be changed programmatically, thanks to Semantic Link Labs, but I will cover that in a future post.

Read on for the script, as well as an update from Sandeep on how you can do this even more easily.

Leave a Comment

Reading Buffers Numbers in PostgreSQL Explain Plans

Published 2025-02-18 by Kevin Feasel

Brent Ozar busts out the calculator:

You’re tuning a Postgres query and you’ve been told to run EXPLAIN with the BUFFERS option, like this:

EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS)

But the output like this is confusing, because the numbers don’t add up vertically or horizontally. It’s just a whole mess of numbers that seem unrelated:

Read on to learn more about which numbers to pay attention to and what they all mean.

Leave a Comment

The Pain of Permissions

Published 2025-02-18 by Kevin Feasel

Hugo Kornelis talks about pain:

But I have been in contracts where I was the only employee able to spell SQL, and hence all other database tasks also fell in my lap. Including permissions.

And yes. I have been in projects where the idea was to investigate all current permissions, check which are and which are not needed, and then correct everything that was wrong.

We never got past stage 1. Even in a moderate sized company, with moderate sized database applications, getting a full overview of who has which permission was sheer hell.

The best I’ve ever been able to do is execute as each user and then query sys.fn_my_permissions. Otherwise, you won’t know the full scope of a user’s permissions because there are group permissions that querying other DMVs as a sysadmin won’t give you. And frankly, even this isn’t a foolproof operation.

Leave a Comment

Migrating a SQL Server Password without Knowing the Plaintext Value

Published 2025-02-18 by Kevin Feasel

Vlad Drumea rebuilds a machine:

Export-DbaLogin does a great job of exporting all logins, but it doesn’t offer a way to migrate the sa password to another instance.
In this case, I didn’t have the sa password and was required to ensure that the rebuilt instance is a 1:1 copy of the original one (edition excluded).

This also meant that the existing password used for sa had to be transferred to the rebuilt instance.

Read on to see how you can accomplish this.

Leave a Comment

Financial KPIs in T-SQL

Published 2025-02-17 by Kevin Feasel

I continue a series:

In this video, we will dive into three of the most common financial key performance indicators: revenue, cost, and profit. We’ll also take a look at several T-SQL techniques, including aggregation, window functions, and common table expressions.

Click through for the video. This is the first video in the series in which I really dig into specific KPIs and SQL techniques.

Leave a Comment

The 8 Worst Things Microsoft Did with SQL Server

Published 2025-02-17 by Kevin Feasel

Brent Ozar has a list:

Last week I wrote about the 6 best things Microsoft ever did to SQL Server, but now we gotta pull up a chair and discuss the stinkers.

To be fair, I excluded anything that’s basically ANSI standard. I’m sorry that you don’t like functions and cursors, but the reality is that Microsoft adds that stuff because they have to. And honestly, I don’t have a problem with, say, functions or cursors – it’s Microsoft’s implementation of them in SQL Server that causes performance problems. They could write the engine in a way that was optimized for ’em – but they didn’t. Anyhoo, moving on.

I do disagree with number 8 (SQL Server on Linux), not because of using Linux per se, but rather because Microsoft parlayed that into SQL Server on containers. And Windows containers don’t count because they’re an abomination.

Looking through the list, there are fewer “I liked that…” things than I anticipated. I do tend toward the weird side of SQL Server, so I gravitate toward the misfit toys of PolyBase, ML Services, and the like.

As for Big Data Clusters, that was close to but not quite what I really want: scale-out in SQL Server with better ability to control what data we cache. It didn’t quite hit the mark, but I do appreciate them trying.

Leave a Comment

Testing a SQL Server Operation with a Container

Published 2025-02-17 by Kevin Feasel

Jess Pomfret performs a test:

Today, my colleague wanted to quickly test out some dbatools commands to install the Ola Hallengren maintenance solution. They had a local instance of SQL installed, but it already had the maintenance jobs running, so it wasn’t a fresh, out of the box instance.

So let’s spin a SQL Server instance in seconds to test against! (Ok it’s seconds if you have the pre-requisites installed, but I’ll get you setup in a few minutes if not)!

Click through for a primer on using SQL Server in a container.

Leave a Comment