Press "Enter" to skip to content

Month: May 2023

Combining Backup Encryption and Compression

Published 2023-05-25 by Kevin Feasel

Matthew McGiffen joins two great flavors:

In SQL Server you can also compress your encrypted backups. Unlike TDE this has been possible with Backup Encryption since the feature was first made available, and there have been no issues that have required fixing – though as always you should still test that restores work correctly. As mentioned in my post about compression with TDE, compressing backups has benefits not just in terms of file size but potentially also in reduced backup times as the time taken to write to disk is smaller.

Read on for more information. Microsoft did the right thing: they compress first and then encrypt; otherwise, you’re not getting any benefit from the compression.

Comments closed

Delayed Durability and Shutdown Behaviors

Published 2023-05-24 by Kevin Feasel

Guy Glantser does some explaining:

In the world of database management systems, ensuring data durability is of paramount importance. It involves guaranteeing that committed transactions are not lost in the event of unexpected failures or system crashes. SQL Server offers a feature known as Delayed Durability to address this challenge. Delayed Durability provides a mechanism for optimizing transaction logging and improving performance without compromising data integrity. This article aims to delve into the concept of Delayed Durability in SQL Server, specifically focusing on its behavior during expected shutdown scenarios.

Guy’s post surprised me, and I recommend reading it if you’ve thought about enabling the feature.

Comments closed

Elastic Pools for Azure SQL DB Hyperscale

Published 2023-05-24 by Kevin Feasel

Arvind Shyamsundar announces a new preview:

We are very excited to announce the preview of elastic pools for Hyperscale service tier for Azure SQL Database!

For many years now, developers have selected the Hyperscale service tier in a “single database” resource model to power a wide variety of traditional and modern applications. Azure SQL Hyperscale is based on a cloud native architecture providing independently scalable compute and storage, and with limits which substantially exceed the resources available in the General Purpose and Business Critical tiers.

Click through to learn more about what’s on offer.

Comments closed

Kusto Detective Agency Season 2

Published 2023-05-24 by Kevin Feasel

Anshul Sharma announces season 2 to a great program:

Greetings, esteemed investigators and data enthusiasts! We are thrilled to announce the highly anticipated launch of Kusto Detective Agency Season 2. After the immense success of Season 1, with over 10,000 participants diving deep into the world of data investigation, we cannot thank you enough for your incredible support and enthusiasm! 

Season 2 of Kusto Detective Agency is set to be an even grander adventure, filled with more challenges, mind-bending mysteries, and countless opportunities to showcase your analytical skills. Prepare yourself for a journey that will push the boundaries of your data prowess and reward you with an unforgettable experience. 

I just finished season 1 yesterday and saw the link to season 2, but didn’t touch it yet. If you’re learning the Kusto Query Language, this is a series of challenges which will really push your skills. As I was going through season 1, there were several times when I’d say “I know exactly how to answer this in T-SQL but how do I answer it in KQL?” If your KQL skills aren’t great, there are plenty of people who have shown their answers online as well, so you can walk through it with them.

Admittedly, I want more Poppy the goldfish lore. The twist in challenge 5 was not something I’d expected.

Comments closed

An Overview of CLR

Published 2023-05-24 by Kevin Feasel

Chad Callihan gives us the primer:

I was setting up a dev environment for a new application recently. All seemed to be well until I went to actually run the application. I was getting a vague error in the application and still didn’t know the exact cause. I fired up an XEvents session to find the query causing the issue and found a query failing with the following error:

Msg 6263, Level 16, State 1, Procedure dbo.MyProc, Line 60 [Batch Start Line 14]
Execution of user code in the .NET Framework is disabled. Enable “clr enabled” configuration option.

Incidentally, my single biggest gripe around CLR integration in SQL Server (and something I complain about almost every time someone brings up the feature) is that they really messed up in picking names. CLR has Safe and Unsafe modes.

What the terms mean:

  • Safe = managed mode. Your .NET language of choice (at the time, VB.Net or C#) handles pointers for you, so you can’t dereference a null pointer. Well, you still can but will instead get an “Object reference not set to an instance of an object” exception rather than some potentially unexpected behavior
  • Unsafe = developers control pointers. There were some things, especially in 2005, that made sense to create in Unsafe mode because it was considerably faster

What far too many DBAs interpreted it as: “I don’t want unsafe code running in my systems! Ban CLR!”

This fundamental misunderstanding of terms killed a smart integration and capability for extremely fast functions—for example, the fastest way to split a string in T-SQL is via CLR, and there’s a lot of impressive functionality you can get at near-native speeds (i.e., if the SQL Server team wrote the code as a function instead of you, allowing them to optimize the database engine for that function) with a few lines of code. But the leads at most places saw the word “Unsafe” and nope’d out.

Comments closed

Finding SQL Server Installation Media on Azure VMs

Published 2023-05-24 by Kevin Feasel

Bob Pusateri does a search:

I was recently wanting to test out some PolyBase features in SQL Server. Azure being my test environment of choice these days, I spun up an Azure SQL Virtual Machine, but I quickly found that PolyBase wasn’t installed. To add it I would need the install media of course, but how does one get that in an Azure SQL Virtual Machine?

Everybody should test out PolyBase, but that’s because I’m wildly biased. Anyhow, Bob shows us where we can find the installation media.

Comments closed

Microsoft Fabric Compendium

Published 2023-05-24 by Kevin Feasel

This one’s going to be a little different from your average Curated SQL post, because there are a whole bunch of Microsoft Fabric-related blog posts. Consider this more a round-up than highlighting any single post.

Overviews

Trying It Out

Size and Scope

Direct Lake

The Name

Comments closed

Opening a Browser in Powershell

Published 2023-05-23 by Kevin Feasel

Robert Cain starts a process:

As part of my ArcaneBooks Project I described how to use the OpenLibrary Simple API to get book data.

In that post I also showed a way to bring up the webpage for an ISBN. I had a thought, why not build a function to add to the module to do that? This way a user would have an easy way to compare the output of the web API call to what the site holds.

In this post I’ll describe how to use the Start-Process cmdlet to open a target webpage.

Read on for the code to do this.

Comments closed

Contrasting Two Visuals: Stacked Column Charts and Line Charts

Published 2023-05-23 by Kevin Feasel

Steve Jones performs a comparison:

I ran across an interesting post from Rita Fainshtein that looked at the different types of graphs for a set of data. I thought that was interesting, so I ran my own experiment. I found for my data, a line graph was better, but let me know what you think.

My data set was simple, a few players across a few events and their number of kills. I coach volleyball and I’m always trying to present stats in a useful way. Here was the small set I picked.

Something to keep note of is that line charts generally imply time series data: you are looking at some periodic activity and analyzing changes between periods.

The stacked column (or bar) chart tells you two things: the total and the first event. It’s really hard to discern any other events from a stacked chart comparison, as Steve points out.

If you don’t have periodic data or if you’re more focused on general trends than what happened at a specific event, the Cleveland dot plot (available in custom visuals) could plot each of the events, with the Y axis representing player names and the X axis representing number of kills, and each dot being a specific game. This saves a lot of real estate and gives you an idea of how points cluster, especially if you look at a larger number of games or players.

Comments closed

Comparing Two Objects in Powershell

Published 2023-05-23 by Kevin Feasel

Patrick Gruenauer sees which of these is not like the others:

In this short blog post, I will show you how to compare two PowerShell objects to find differences between them. I will also give you an example on how to create objects for testing purposes. Let’s dive in.

If you’re familiar with the diff command, you’ll have some idea here. One advantage to Compare-Object, however, is that Powershell is dealing with objects rather than strings, allowing for more complex comparison scenarios assuming your objects are set up for it.

Comments closed