Press "Enter" to skip to content

Author: Kevin Feasel

Migrating Always Encrypted to a Secure Enclave

Published 2024-03-13 by Kevin Feasel

Pieter Vanhove has an enclave, which is sort of like a Bat-cave:

Always Encrypted is a feature of Azure SQL and SQL Server that allows you to encrypt sensitive data in your database. The data is never exposed in plaintext to the database engine, or anyone who has access to it.

However, Always Encrypted has some limitations. For example, you cannot perform any computations or operations on the encrypted data, such as sorting, filtering, or indexing. Secondly, an initial encryption must be done on the application side which can be time consuming on a large set of data.

That’s where Always Encrypted with secure enclaves comes in.

Read on to see what secure enclaves give you, as well as how you can enable it and what changes your application might require.

Comments closed

Durability and Hekaton

Published 2024-03-12 by Kevin Feasel

Rob Farley ponders a pair of potential performance improvements and their effects on durability:

Durability in SQL is handled by making sure that data changes are written to disk before the calling application is informed that the transaction is complete. We don’t walk out of a shop with our goods before the cashier has confirmed that the credit card payment has worked. If we did, and the payment bounced, the cashier would be calling us back pretty quickly. In SQL this is about confirming that the transaction log entry has been written, and it’s why you shouldn’t use disks with write-cache for databases

And yet, in-memory features of SQL, commonly called “Hekaton” handles transactions without writing to disk. The durability is delayed. This month, Todd Kleinhans invites us to write about Hekaton.

In-Memory OLTP is one of those features that I wish worked better for most use cases or didn’t have as many limitations around only working within the context of a single database. In practice, instead of using In-Memory OLTP for most tables, you’re usually better off just jamming more RAM on the box and limiting how many scans of large tables flush your buffer pool.

Comments closed

Incremental Refresh on Large Power BI Semantic Models

Published 2024-03-12 by Kevin Feasel

Soheil Bakhshi needs to refresh a lot of data:

Implementing incremental refresh on Power BI is usually straightforward if we carefully follow the implementation steps. However in some real-world scenarios, following the implementation steps is not enough. In different parts of my latest book, Expert Data Modeling with Power BI, 2’nd Edition, I emphasis the fact that understanding business requirements is the key to every single development project and data modelling is no different. Let me explain it more in the context of incremental data refresh implementation.

Read on for that explanation, as well as a few tips to make things work a bit more smoothly.

Comments closed

Proper Logging of SQL Agent Job Outputs

Published 2024-03-12 by Kevin Feasel

Aaron Bertrand hits on an annoyance of mine:

If you haven’t migrated your workloads to a managed database platform yet, you’re probably still relying on SQL Server Agent for various maintenance and other scheduled tasks. Most of the time, these processes just work. But when it’s time to troubleshoot, it can be cumbersome to get to the root of some problems. In this post, I’ll share some ideas to help you minimize the level of annoyance and tedium when you have to figure out what went wrong with the execution of a job.

Recently, I was investigating a job that ran a stored procedure against multiple databases in sequence, and ultimately failed. Here was the output of the job step as seen in Management Studio’s View Job History dialog:

Read on for a few tips to help with SQL Agent job logging.

Comments closed

Tips for Using Powershell in Azure

Published 2024-03-12 by Kevin Feasel

Paul Harrison shares a few tips with us:

When I’m working with a new object in Azure I often don’t know where the information I care about is actually found in output. PowerShell makes it easy to navigate through objects, however it isn’t easy to get an overview of all properties available if they’re nested 5 levels deep. I like to use ConvertTo-JSON to help me get a general understanding for a new object and which properties are available and how to find them.

Read on for more information about converting to JSON and four other tips.

Comments closed

Dropping Extra tempdb Files

Published 2024-03-12 by Kevin Feasel

Vlad Drumea doesn’t want to restart SQL Server:

This is a brief post containing a script you can use to delete extra tempdb data files and avoid the “file not empty” (Msg 5042) error.

I’ve occasionally ran into situations where an instance is configured with more than the recommended number of tempdb data files.

If you’re dealing with more than a couple of extra files it can get annoying, especially on a busy instance.

Read on for the script and how you can use it on busy and not-so-busy instances.

Comments closed

VISUAL SHAPE and Visual Calculations

Published 2024-03-12 by Kevin Feasel

Marco Russo and Alberto Ferrari fit the square pegs into the square holes:

Visual calculations, introduced as a preview feature with the February 2024 release of Power BI, aim to simplify the creation of calculations tied to a specific visual. Using visual calculations for simple calculations is straightforward.

However, as soon as developers create more complex calculations, they should understand the technical details of visual calculation implementation. This requires understanding the hierarchical structure of the virtual table, the new visual context, the semantics of ROWS and COLUMNS, the behavior of CALCULATE, and the new visual context modifiers EXPAND and COLLAPSE.

In this first article about visual calculations, we introduce VISUAL SHAPE and the basics of visual calculation implementation, leaving the remaining topics to future articles. A complete whitepaper with a detailed explanation of all these topics will be available soon to SQLBI+ subscribers.

Even without a complete whitepaper, this serves as a useful primer on the topic.

Comments closed

Saving Money on Azure Storage

Published 2024-03-11 by Kevin Feasel

Rahul Miglani claws back some cash:

In today’s digital landscape, businesses are increasingly turning to cloud storage solutions to manage their data effectively. Microsoft Azure offers a wide range of storage options tailored to meet diverse business needs while optimizing costs. In this blog post, we’ll explore how organizations can leverage Azure storage options to achieve significant cost savings without compromising performance or reliability.

Read on for ten tips. A lot of it boils down to keeping just enough data and putting it in the right tier, but there’s a bit more to the story.

Comments closed

Analyzing TDS 8.0 Handshake Packets with Wireshark

Published 2024-03-11 by Kevin Feasel

Matt Changchien fires up the packet capture tool:

The main difference between TLS1.2 and TLS1.3 is that TLS 1.3 reduces the number of round trips from two to one during the handshake phase, making it faster and more secure than TLS 1.2. The server hello packet containing server certificate is encrypted and one Round Trip Time (1-RTT) resumption is discontinued, and replaced with 0-RTT resumption based on client key share. Added security of TLS 1.3 comes from discontinuing certain cyphers and algorithms. (TLS 1.3 support – SQL Server | Microsoft Learn)

Click through to see what the differences look like between TLS 1.2 and TLS 1.3, as well as the specific example of Azure SQL Database.

Comments closed