Press "Enter" to skip to content

Curated SQL Posts

Full Query Logging in Cassandra 4.0

Published 2021-08-25 by Kevin Feasel

Shelby Carpenter explains a new feature in Apache Cassandra 4.0:

First off, we need to understand what counts as a full query log (FQL) in Cassandra. Full query logs record all successful Cassandra Query Language (CQL) requests. Audit logs (also a new feature of Cassandra 4.0), on the other hand, contain both successful and unsuccessful CQL requests. (To learn about the different forms of logging and diagnostic events in Cassandra 4.0, check out this blog by Instaclustr Co-Founder and CTO Ben Bromhead.)

The FQL framework was implemented to be lightweight from the very beginning so there is no need to worry about the performance. This is achieved by a library called Chronicle Queues, which is designed for low latency and high-performance messaging for critical applications.

Read on for information on use cases as well as how you can configure and access full query log information.

Comments closed

Loops in Powershell

Published 2021-08-25 by Kevin Feasel

Robert Cain takes us through different structured programming constructs in Powershell:

Continuing my series on PowerShell basics, in this installment we’ll talk bout looping in PowerShell. Looping is a mechanism for repeating a set of instructions multiple times, ending once a condition is met. Looping is also known as iterating, as in “iterating over an array”.

Loops continue until one of two things happen. In one type of loop, the loop continues until a certain condition is true. In the second type, the loop will iterate over a collection (also called an array) of objects until the entire collection has been gone through.

Read on for an enumeration of the different types of loops you can create with this language.

Comments closed

Dealing with Corruption around In-Memory OLTP

Published 2021-08-25 by Kevin Feasel

Chris Taylor has a tale of woe:

Late last week (20th) we had an emergency call from a company that had a production database go into Suspect mode and needed help. Now this isn’t a great situation to be in so when they then go on to tell us that the last valid backup they had was from the 12th and the backup job had been failing since then – even less of a great situation to be in

Read the whole thing.

Comments closed

Scanning and Classification with Azure Purview

Published 2021-08-25 by Kevin Feasel

Angela Henry continues a series on Azure Purview:

In our previous article for this series, Purview Part 2: Data Catalog, we examined the portion of the end user experience where people will spend the majority of their time. But the question is, how does that Data Catalog get populated? The Data Catalog is populated by the Scanning and Classification features of Purview, which is the focus of this article.

Click through to see what you need to set up and how the process works.

Comments closed

Getting Distinct Values before STRING_AGG

Published 2021-08-24 by Kevin Feasel

Greg Dodd shows how to remove duplicate values from a list before passing them to the STRING_AGG() function:

SQL introduced the new STRING_AGG feature in SQL 2017, and it works just like it suggests it would: it’s an aggregate function that takes all of the string values and joins them together with a separator. To see how it works, I’m using the StackOverflow users table, and let’s say we want to create a list of Display Names and we’re going to group it based on Location:

Click through for two methods, one of which is considerably better than the other.

Comments closed

Sending an E-Mail upon Database Creation

Published 2021-08-24 by Kevin Feasel

Thomas Williams has a job for SQL Server:

As part of commissioning a SQL Server, I create a server trigger to send an e-mail when a new database is created (adapted from https://www.mssqltips.com/sqlservertip/2864/email-alerts-when-new-databases-are-created-in-sql-server/), and a SQL Agent alert which e-mails when a database is restored (inspired by Jeremy Dearduff’s comment at https://www.brentozar.com/archive/2017/06/tracking-restores-hard/#comment-2446362).

After receiving the e-mail I can follow up and include the database in an inventory. See below for the trigger and alert scripts – feel free to use these as a basis for your own monitoring and inventory.

Click through for the script, as well as an important disclaimer.

Comments closed

The Cost of a Checkbox: Power Apps Edition

Published 2021-08-24 by Kevin Feasel

Paul Thurrott looks at a security issue:

Over 1000 web apps created with Microsoft’s Power Apps inadvertently exposed the data from over 38 million users thanks to a misconfiguration, according to a new report in Wired. The good news? The issue has been fixed and no customers are known to have been compromised.

“We found [a web app created with Power Apps] that was misconfigured to expose data and we thought, we’ve never heard of this, is this a one-off thing or is this a systemic issue?” UpGuard vice president Greg Pollock told Wired. “Because of the way the Power Apps portals product works, it’s very easy to quickly do a survey. And we discovered there are tons of these exposed. It was wild.”

“Known to have been compromised” probably needs a “yet” in there somewhere. Read the whole thing.

Comments closed

Improving Dataset Refresh with Query Folding and the Dataflows Connector

Published 2021-08-24 by Kevin Feasel

Chris Webb has a performance tip for us:

You may have noticed that a new dataflows connector was announced in the August 2021 release of Power BI Desktop, and that it now supports query folding between a dataset and a dataflow – which you may be surprised to learn was not possible before. In this post I thought I’d take a look at how much of an improvement in performance this can make to dataset refresh performance.

Click through for the demonstration.

Comments closed