Press "Enter" to skip to content

Author: Kevin Feasel

The Internals of Backup Compression

Published 2023-10-10 by Kevin Feasel

Andy Yun continues a series on how backups work in SQL Server:

Welcome back to Part 4 of my Backup Internals series. Today, I’d like to spend a little time exploring backup compression.

When you take a regular FULL BACKUP, SQL Server is literally taking a byte-for-byte copy of your data files. Don’t believe me? Then go read this, then come back. Additionally, Microsoft architected BACKUP operations such that the resource utilization and impact would be minimal (when using default parameters).

This post taught me a few things about the practical impact of enabling compression. Even after reading this, however, I would almost always enable it for two reasons. First, storage is usually the bottleneck for organizations, so actions which reduce storage utilization can improve overall performance. Second, there are limits to how much we can store, so compressing backups may let me get away with holding more backups on a given LUN or drive.

Comments closed

Indexing in PostgreSQL

Published 2023-10-10 by Kevin Feasel

Henrietta Dombrovskaya continues a series on Postgres:

What is an index? One might assume that any person who works with databases knows what an index is. However, a surprising number of people, including database developers and report writers and, in some cases, even DBAs, use indexes, even create indexes, with only a vague understanding of what indexes are and how they are structured. Having this in mind, let’s start with defining what is an index.

Since there are many different index types in PostgreSQL (and new index types are constantly created) we won’t focus on structural properties to produce an index definition. Instead, we define an index based on its usage.

Indexing is one area in which SQL Server and Postgres differ, as SQL Server relies on clustered indexes for storage and “default” operations, whereas Postgres has a different model.

Comments closed

An Overview of Encryption Options in SQL Server

Published 2023-10-10 by Kevin Feasel

Rob Farley has a cipher:

Encryption is a funny thing. Since the dawn of communication, whenever people have wanted to keep their secrets secret, they’ve used some sort of encryption. I’m sure parents started spelling things so their kids wouldn’t understand as soon as there was spelling. Using words their kids wouldn’t understand, while the kid sits there thinking “Oh, Dad, you’re so embarrassing, thinking I don’t know what that means…”. Encryption is all about keeping information away from people, particularly those who could use it against you. Ask the folk from Bletchley Park if you don’t realise how this can impact world events.

Rob links to Enigma and Bomba (the British system for decrypting Enigma messages) but there’s another interesting story out of Bletchley Park as well: the Lorenz cipher, which was cracked by a Polish mathematician early on, but decryption was quite slow, on the order of a message or so per day. This led to Colossus, the first digital computer in existence. The National Museum of Computing in Bletchley Park has a working rebuild of a Mark 2 Colossus on display and we got to see it (and get the story behind it) on day 1 of Data Relay this year, so that was fun to see.

As an interesting side note, the British never told the Soviets that they had decrypted the Lorenz cipher, so when the Soviets took hold of these machines near the end of World War II, they assumed that nobody had cracked the code, so they continued to use these for a while, allowing the British access to certain sensitive communications for a time.

Comments closed

Common SQL Server Mistakes: Default Auto-Growth

Published 2023-10-10 by Kevin Feasel

Hemantgiri Goswami takes a look at auto-growth:

Auto Growth is a feature that allows database files (primary, secondary, and log) to expand when the database file becomes full – without manual intervention.

Auto Growth feature is handy when we do not want to increase the size of database files manually. There are two ways you can set auto growth – using SQL Server Management Studio (SSMS hereafter) and T-SQL. Auto Growth can be configured – In Percent and Megabytes.

Auto-growth isn’t a problem on its own, though growth sizes, especially in older versions of SQL Server, were far too low for medium- and large-sized databases.

I don’t particularly like the 2.5 MB example Hemantgiri shows. I have a quick rule of thumb which is 64MB for small databases, 256-512 for medium-sized databases, and 1GB for large databases (assuming my underlying disk is fast). This limits the number of auto-growth events and, for log files in particular, keeps virtual log file counts more reasonable.

Comments closed

Conditional Formatting in Power BI with Field Parameters and Calculation Groups

Published 2023-10-10 by Kevin Feasel

Marco Russo and Alberto Ferrari perform some formatting:

If you want to build a report where the user can choose what measure to show, you have two features available in Power BI: field parameters and calculation groups. There are pros and cons to either technique – however, we are not about to talk about those. We narrow our scenario down to a specific requirement: we want to change the color of the value depending on the measure selected.

For example, suppose we let users choose between Sales Amount, Margin, or Total Cost. In that case, we might provide visual feedback about the measure selected through different colors: black for Sales Amount, green for Margin, and red for Total Cost.

Click through for that example, though I will say that the color choices are hard to differentiate if you have protanopia and even more difficult if you have deuteranopia, so about 2% of the male population would struggle with interpreting this measure. People with protanomaly and deuteranomaly (about 6% of men) wouldn’t have too much difficulty with this particular color pairing.

Comments closed

Power BI Themes and Gallery

Published 2023-10-09 by Kevin Feasel

Seth Bauer has an announcement:

The Power BI Tips Theme Generator tool already allows you to easily interact with, and adjust, all the visual properties, wireframes, etc… How could we possibly make Power BI Theme building an effortless experience? We start with building it all for you, then letting you adjust it!
The all new Gallery feature represents a significant leap forward in simplifying the theming process for all. This feature is especially for the business users! But, it also opens up exciting opportunities for the Power BI community to contribute in the future as well.

Read on to see how this works.

Comments closed

Package Management in Python

Published 2023-10-09 by Kevin Feasel

Georgia Atkinson wraps things up with a bow:

Python is a general purpose, high level language which, thanks to its simplicity and versatility, has become very popular, especially within the data science community. The extensive Python community has developed and contributed thousands of libraries and packages over the years in a plethora of different disciplines to aid developers with their applications. Managing these packages can be a challenging task without the correct tools. That’s where Python package managers come in. In this blog post we will explore what a package manager is and why they are important. We will then cover some popular examples, including how to use them, how to install them and the pros and cons of each.

Whilst we will briefly touch on virtual environments in places, we will explore these in more depth in an upcoming post.

Read on for a primer on three options, including how they compare to one another for CI/CD purposes.

Comments closed

Vacuuming in PostgreSQL

Published 2023-10-09 by Kevin Feasel

Muhammad Ali keeps things tidy:

If you’re a PostgreSQL user, you’ve undoubtedly come across the term “vacuum“. This operation plays a pivotal role in maintaining the optimal performance of your database while preventing unnecessary data bloat. In this blog, we’ll understand how vacuum works on high level, its significance, types, server parameters that influence autovacuum operations, and general FAQ’s on vacuum.

Read on to learn more about what vacuuming does and why it is important. It also turns out that there are multiple types of vacuuming.

Comments closed

SortByColumn Set to Invalid Column ID in SSAS Tabular

Published 2023-10-09 by Kevin Feasel

Olivier Van Steenlandt troubleshoots an error:

After making these changes, we pushed our changes into Azure DevOps and our deployment pipeline started to deploy the changes to the requested environment.

While the deployment process was executing, it stopped and failed promptly. We ran into an issue: “SortByColumn property set to an invalid column ID”

Read on to see the ultimate cause of and solution to the problem.

Comments closed

Workaround for Primary Keys in Fabric Data Warehouses

Published 2023-10-09 by Kevin Feasel

Gilbert Quevauvilliers needs a key:

When I started looking into using the data warehouses feature in Fabric, I did see that there were limitations on Primary Key columns.

Below is my blog post on how I still use keys in my data warehouse, instead of using GUID’s which to me are long and hard to use.

In my example I am going to create a simple data warehouse which is going to consist of two-dimension tables (Date and Country) and a fact table with the Sales amounts.

This seems sub-optimal, though at least Gilbert shows us a workaround.

Comments closed