Press "Enter" to skip to content

Author: Kevin Feasel

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.

Comments closed

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.

Comments closed

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.

Comments closed

A Dive into Microsoft Fabric Real-Time Intelligence

Published 2025-02-17 by Kevin Feasel

Nikola Ilic builds us a guide:

Once upon a time, handling streaming data was considered an avanguard approach. Since the introduction of relational database management systems in the 1970s and traditional data warehousing systems in the late 1980s, all data workloads began and ended with the so-called batch processing. Batch processing relies on the concept of collecting numerous tasks in a group (or batch) and processing these tasks in a single operation. 

On the flip side, there is a concept of streaming data. Although streaming data is still sometimes considered a cutting-edge technology, it already has a solid history. Everything started in 2002, when Stanford University researchers published the paper called “Models and Issues in Data Stream Systems”. However, it wasn’t until almost one decade later (2011) that streaming data systems started to reach a wider audience, when the Apache Kafka platform for storing and processing streaming data was open-sourced. The rest is history, as people say. Nowadays, processing streaming data is not considered a luxury, but a necessity. 

This is all part of a book that Nikola and Ben Weissman are writing, and Nikola has an extended excerpt from the book available for us to read.

Comments closed

Creating Logins and Users via dbatools

Published 2025-02-17 by Kevin Feasel

Chad Callihan creates a new user:

I can’t remember where I heard the analogy, but think of a SQL Server Login as the key to a hotel. While a Login will get you in the hotel, you need a room-specific key (User) to access specific rooms (or databases) in that hotel.

When it comes to creating new logins and users, dbatools can help make it a more manageable process. This is especially helpful when you’re deploying the same login and/or user to multiple servers at a time.

That’s a nice analogy, and Chad follows it up with a pair of dbatools cmdlets you may find helpful.

Comments closed

A Short Break

Published 2025-02-12 by Kevin Feasel

Curated SQL will take the next day or two off. At latest, we’ll be back in the saddle by Monday the 17th, but you might even see a bit of lovingly hand-crafted blog posts before then, so stay tuned!

Comments closed

Tips for Scaling Apache Kafka

Published 2025-02-11 by Kevin Feasel

Narendra Lakshmana Gowda tunes a Kafka cluster:

Apache Kafka is known for its ability to process a huge quantity of events in real time. However, to handle millions of events, we need to follow certain best practices while implementing both Kafka producer services and consumer services.

Before start using Kafka in your projects, let’s understand when to use Kafka:

Much of the advice is pretty standard for performance tuning in Kafka, like setting batch size and linger time on the producer or managing consumers in a consumer group.

Comments closed

Azure Elastic Jobs to Run Powershell and T-SQL

Published 2025-02-11 by Kevin Feasel

Josephine Bush kicks off a job:

I’ve covered how to create Elastic Jobs in the portal (this one is important to read if you aren’t familiar with elastic jobs already), with Terraform, and with Bicep. Now, I’ll cover how to create them and their associated objects with PowerShell. Don’t do this in prod to start. Always test in a lower environment first.

Click through for the process, as well as the script.

Comments closed

REPLACENULL in SSIS versus DT_DBTIMESTAMP2 Columns

Published 2025-02-11 by Kevin Feasel

Slava Murygin notes an error:

 Using the “REPLACENULL” functionality frequently in the “Derived Column” component, the “Conditional Split” component, and other places in SSIS where formulas can be applied is common.

However, I recently encountered an issue with the “DT_DBTIMESTAMP2” data type.

The following formula produced an error:

REPLACENULL(TestDt, (DT_DBTIMESTAMP2,7)”1900-01-01 00:00:00.0000000″)

Error: 0xC020902A at Test Transformation, Derived Column [2]: The “Derived Column” failed because truncation occurred, and the truncation row disposition on “Derived Column.Outputs[Derived Column Output].Columns[TestDt]” specifies failure on truncation. A truncation error occurred on the specified object of the specified component.

Read on for an explanation and two alternatives.

Comments closed