Press "Enter" to skip to content

Curated SQL Posts

Deploying Azure Policies via Terraform

Published 2021-07-27 by Kevin Feasel

Jonathan D’Aloia shows how you can use Terraform to work with Azure Policies:

As you may all know Terraform serves a great purpose in deploying resources and infrastructure into your Azure environment, however, Terraform can also be used to automate and consistently deploy Azure Policies which can be defined prior to any resources being generated. In this blog, I will cover how you can import policies into your Terraform State to then deploy into an Azure Resource Group in order to secure your landing zone prior to deploying any resources.

Click through for an example.

Comments closed

Minimizing PAGELATCH_EX Waits in SQL Server 2019

Published 2021-07-27 by Kevin Feasel

Aaron Bertrand has some advice for us:

There is a common problem in SQL Server where contention when writing to a single page can lead to excessive waiting. This waiting comes in the form of the wait type PAGELATCH_EX, and often happens when the clustering key is a monotonically increasing value – like an IDENTITY or date/time column. Pedro Lopes has highlighted some non-trivial workarounds in his post, “PAGELATCH_EX waits and heavy inserts,” and there is some official guidance in the Microsoft Docs article, “Resolve last-page insert PAGELATCH_EX contention in SQL Server.” But are there any ways to address this issue without intrusive changes?

Read on to see what Aaron has in mind.

Comments closed

The Benefits of Instant File Initialization

Published 2021-07-27 by Kevin Feasel

Greg Larsen explains how instant file initialization works:

Instant file initialization is a SQL Server setup option that keeps SQL Server from writing binary zeroes to data files when a database is first created, expanded, or restored. By avoiding the writing of binary zeroes, there is a lower performance impact when disk space is allocated for several database operations.

By default, when SQL Server creates a database, increases the size of a database, or restores a database, it needs to initialize the disk space prior to allocating the space. This initialization process writes binary zeroes (“0”) across all the bits and bytes of space being allocated. By writing binary zeroes across the disk space, the SQL Server engine makes sure that data previously stored in the unused disk space is completely overwritten before the disk space is allocated to a database.

Read on for more details, as well as why this can be worth enabling.

Comments closed

Digital Forensics with Apache Kafka

Published 2021-07-26 by Kevin Feasel

Kai Waehner continues a series on using Apache Kafka as the backbone for computer security:

Storing data long-term in Kafka is possible since the beginning. Each Kafka topic gets a retention time. Many use cases use a retention time of a few hours or days as the data is only processed and stored in another system (like a database or data warehouse). However, more and more projects use a retention time of a few years or even -1 (= forever) for some Kafka topics (e.g., due to compliance reasons or to store transactional data).

The drawback of using Kafka for forensics is the huge volume of historical data and its related high cost and scalability issues. This gets pretty expensive as Kafka uses regular HDDs or SDDS as the disk storage. Additionally, data rebalancing between brokers (e.g., if a new broker is added to a cluster) takes a long time for huge volumes of data sets. Hence, rebalancing takes hours can impact scalability and reliability.

But there is a solution to these challenges: Tiered Storage.

Click through to learn more.

Comments closed

Detecting a Fraud: Power BI Edition

Published 2021-07-26 by Kevin Feasel

Brett Powell has an interesting post on determining a Power BI consultant’s chops:

This post discusses the forms, origins, and indications of fraud and misrepresentation in the context of Power BI consulting and contract engagements. The intent of this content is to help the reader determine whether a fraud or misrepresentation has occurred and identify characteristics that suggest the potential for a significant variance between expectations and deliverables.

Brett comes at this from the viewpoint of an expert sharing information on the types of things you should expect to see from a qualified consultant. Incidentally, this also works to give you an idea of the types of skills necessary to become a Power BI consultant so if that’s your goal, study this and you’ll be in a solid mindset—especially because this isn’t just a knowledge quiz.

1 Comment

Working with Complex Return Types in Power Query

Published 2021-07-26 by Kevin Feasel

Gerhard Brueckl works with some complex data:

When working with Power Query, you have probably already realized that every expression you write returns a value of a specific type. Usually this will be a primitive type like text, number, or date. (A full list of types available in Power Query can be found here: https://docs.microsoft.com/en-us/powerquery-m/m-spec-types). If for some reason the type of an expression cannot be defined, the special type *any* will be used. For sure you already encountered this when using Table.AddColumn which, by default, results in the new column being of type *any*.

Read on to learn more, including what you can do if you’re creating Power Query functions.

Comments closed

Setting Resource Constraints on Containers

Published 2021-07-26 by Kevin Feasel

Anthony Nocentino won’t let this container run amok:

Docker gives you the ability to control a container’s access to CPU, Memory, and network and disk IO using resource constraints, sometimes called Limits. You define limits as parameters when creating containers. In its default configuration, a container will have no resource constraints for accessing resources of the host operating system. This post will look at how to configure resource constraints in Docker and look at how SQL Server sees the resources when CPU and Memory resource constraints are in place.

In this post, we will focus on using CPU and Memory Limits using the parameters --cpus <value> and --memory <value>. These provide the essential resource controls for access to CPU and Memory available on the host. If you want to dive deep into the various types of resource constraints available in Docker, check out this page https://docs.docker.com/config/containers/resource_constraints/. The goal of this post is to introduce the SQL Server DBA into resource constraints in containers.

Read on to learn how you can use CPU and memory limits to control resource allocation for SQL Server containers—including at runtime.

Comments closed

Threat Intelligence and Kafka

Published 2021-07-23 by Kevin Feasel

Kai Waehner continues a series on using Apache Kafka as the foundation for a security solution:

Threat intelligence, or cyber threat intelligence, reduces harm by improving decision-making before, during, and after cybersecurity incidents reducing operational mean time to recovery, and reducing adversary dwell time for information technology environments.

Threat intelligence is evidence-based knowledge, including context, mechanisms, indicators, implications, and action-oriented advice about an existing or emerging menace or hazard to assets. This intelligence can be used to inform decisions regarding the subject’s response to that menace or hazard.

Threat intelligence solutions gather raw data about emerging or existing threat actors & threats from various sources. This data is then analyzed and filtered to produce threat intel feeds and management reports that contain information that automated security control solutions can use.

Threat intelligence keeps organizations informed of the risks of advanced persistent threats, zero-day threats and exploits, and how to protect against them.

Read the whole thing.

1 Comment