Press "Enter" to skip to content

Curated SQL Posts

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

Snippets in SQL Server Management Studio

Published 2025-02-17 by Kevin Feasel

Pablo Lozano creates a snippet:

The work of a DBA sometimes involves very repetive tasks:

  • Create a new login / users, or add/remove permissions
  • Run manual index / stats operations when the scheduled maintenance tasks are not enough and need a manual “push”
  • Taking a backup or restore one to troubleshoot issues or restore missing data…

There are many ways to accomplish this:

You can keep a folder full of scripts and just open the one you need and run it

Use a third-party tool to store all those scripts and paste them with a quick shortcut or a few clicks

Or the one I’ll be discussing, using snippets

Read on to see how you can create these. Snippets were great as a DBA, but I think I liked them even more when I was doing database development work, especially for repetitive code blocks like try-catch (or try-catch around a transaction) or searching in sys.sql_modules for a specific bit of code.

Leave a Comment

Checking out the mssqlsystemresource Database

Published 2025-02-17 by Kevin Feasel

Stephen Planck goes into single-user mode:

Have you ever wondered where SQL Server actually stores the definitions for system objects like built-in stored procedures, system views, and functions? Enter the mssqlsystemresource database—often just called the “Resource” database. While this database remains hidden from everyday use, it plays a pivotal role in the internal workings of SQL Server. In this post, we will explore what the Resource database is, why it’s necessary, and what every DBA should know about it.

Read on for a primer on what’s in this database and why we typically shouldn’t mess with it.

Leave a Comment