Press "Enter" to skip to content

Curated SQL Posts

Migrating to Azure PostgreSQL Flexible Server from Single Server

Published 2024-10-18 by Kevin Feasel

Josephine Bush performs a migration:

Why Migrate to Flexible Server?

  • High availability and disaster recovery: Flexible Server provides higher availability with zone-redundant architecture.
  • Customizable maintenance windows: More control over when updates and maintenance tasks occur.
  • Performance improvements: Fine-tuned scaling and performance adjustments without downtime.
  • Enhanced security: With VNet integration and more advanced networking options.

Read on to learn more about by when you have to migrate and how you can perform the migration.

Comments closed

Data Masking in Snowflake: Tagging Policies

Published 2024-10-17 by Kevin Feasel

Kevin Wilkie creates some tags:

In our last post, we discussed data masking and setting it up for specific columns. Now, I don’t know about y’all, but I deal with hundreds of tables with several columns in each on a daily basis that truly need data masking. If I have to go through and set up a masking policy one by one, I might go crazier than I normally am.

Let’s say that I have an existing table, Employee, in my database that I want to apply a policy. As I’m sure many of you know, any table with Employee data should have at least some of its data masked. The first thing that we need to do is to set up the tag that we’ll be using for, in our case, a column named Salary.

Read on to learn more about how to create tags and why you might want to.

Comments closed

Troubleshooting Chains of Common Table Expressions

Published 2024-10-17 by Kevin Feasel

Jared Westover calls me out:

I started using Common Table Expressions (CTEs) in Oracle before Microsoft SQL Server and found them easier to read than a derived table. Something about reading from the top down just clicked in my brain. As the years have passed, I’m less of a fan for one reason: troubleshooting a chained CTE with several links is hard—especially when it’s someone else’s code. Even the ones I wrote years ago fill me with unease when something breaks. Is there an easier way to break down each link in the chain when things go wrong?

This is, I would argue, the biggest inconvenience around using common table expressions. The fact that SQL Server cannot materialize the contents of CTEs is likely a more prevalent problem, but challenges in how you can troubleshoot the individual parts of common table expressions are real.

Comments closed

Thoughts on Blobs in PostgreSQL

Published 2024-10-17 by Kevin Feasel

Stefanie Janine Stölting shares some information on using blobs in PostgreSQL:

PostgreSQL does not have a BLOB data type as specified in the SQL standard. The nearest implementation is the data type BYTEA. Since PostgreSQL 9.0 it does handle data by standard as hexadecimal data.

Click through to learn more about how that data type works, some of the limitations around it, and why it’s not a smart idea to store blob-like data in PostgreSQL The arguments are very similar to not doing this in SQL Server, and the counter-arguments are similarly there and exceptional in nature.

Comments closed

Configuring the Fabric Service Principal to Support Storage APIs

Published 2024-10-17 by Kevin Feasel

Gilbert Quevauvilliers grants some permissions:

This blog post explains how to configure access for my Service Principal to interact with the Azure Storage API to use the API to get details for Microsoft Fabric Storage.

This is part of a blog post series where I am going to show you how to “View Total Storage consumed in Microsoft Fabric”

When I started this blog post I realized that I first need to explain how to configure the Service Principal authentication to interact with the Azure Storage API permissions. This is because in my notebook these steps are required for the notebook to run successfully.

Read on to find out how.

Comments closed

Regarding pg_dump as a Backup Tool

Published 2024-10-17 by Kevin Feasel

Robert Haas shares some thoughts:

Recently, I’ve been hearing a lot of experienced PostgreSQL users reiterate this line: “pg_dump is not a backup tool.” In fact, the documentation has recently been updated to avoid saying that it is a backup tool, to widespread relief. Experienced PostgreSQL users and developers have been publicly called out for having the temerity to assert that pg_dump is, in fact, a backup tool. I find this narrative deeply frustrating, for two reasons.

Click through for those reasons.

Comments closed

A Primer on Pandas

Published 2024-10-16 by Kevin Feasel

Rajendra Gupta talks about Pandas:

Have you heard about Pandas in Python? It is widely used open-source library for analyzing and manipulating data in the Python programming language. Let’s explore it with use cases and examples.

Click through for an overview of the library. Pandas isn’t the quickest performer as your data sets get large, but for ease of use on moderately-sized datasets (up to hundreds of thousands of rows, or maybe millions if you manage things well), it does a good job.

Comments closed

Simplifying Azure Authorization with Managed Identities

Published 2024-10-16 by Kevin Feasel

MIka Sutinen doesn’t need a password:


Managed Identities in Azure provide an efficient and secure way for managing credentials when accessing databases, or other Azure resources. In this post, we build on my recent post about using Service Principal for database authentication, further exploring how we can leverage Managed Identities to simplify the process.

While Service Principals can make some parts of the access management simpler in Azure, you’re still left with several responsibilities in their management.

These responsibilities include rotating secrets and managing the lifecycle of the Service Principal, to name maybe the two most crucial ones. And if you have multiple applications, each with a separate Service Principal, this can be complex to manage.

Click through to learn more about managed identities and service principals, and how they work to link together Azure resources behind the scenes.

Comments closed

Backup Types and Recovery Models in SQL Server

Published 2024-10-16 by Kevin Feasel

I have a new video:

In this video, I cover the three recovery models available to SQL Server, describe (most of) the types of backups you can take, and spend a bit of time covering Recovery Point Objective & Recovery Time Objective.

The advice for video length that people tend to give is 8-10 minutes. I typically average closer to 15 minutes per video. This one is nearly 30 minutes long because there’s just so much information to cover, even with me repeatedly saying “Don’t worry, I’ll cover this bit in a future video.”

Comments closed