Press "Enter" to skip to content

Curated SQL Posts

QA Refreshes via CI/CD

Published 2023-03-15 by Kevin Feasel

Hiram Fleitas rebuilds the QA environment:

In this post I am going to cover how to automatically refresh a lower environment commonly used for testing as part of your release (CD) pipeline.

Well, why? – you may be asking.

  1. In some cases, developers and testers need to test their application code-changes against a fresh copy of production-like data. This helps them do validations prior to publishing their changes to production where their apps are bombarded by end-user live workloads.
  2. Also, the lower environment may be used for testing, and we can’t overwrite the test data constantly. It needs to be a hot-standby refresh, made available when necessary.

Click through for notes on the process.

Comments closed

Investigating Powershell Object Members

Published 2023-03-15 by Kevin Feasel

Jeffrey Hicks wants to know what he can do:

A few weeks ago, I was working on content for a new PowerShell course for Pluralsight. The subject was objects. We all know the importance of working with objects in PowerShell. Hopefully, you also know that the output you get on your screen from running a PowerShell command is not the whole story. Formatted presentation is separate from the underlying objects in the pipeline. That’s why it is important to know how to use Get-Member to discover how an object is defined.

But, as Jeffrey points out, this doesn’t work for static members. Read on to learn what does.

Comments closed

RBAC with Kubernetes

Published 2023-03-15 by Kevin Feasel

Mercy Bassey locks down some containers:

Have you been searching for a way to manage your resources effectively in Kubernetes? Why not consider Kubernetes Role-Based Access Control (RBAC)? With Kubernetes RBAC, you can securely manage containers.

Kubernetes RBAC allows administrators like yourself to define roles with specific permissions to access resources in a Kubernetes cluster. And in this tutorial, you will learn how to create a user and define roles with specific permissions.

There are enough steps involved that I’d definitely want to manage this at the group level.

Comments closed

DBCC CHECKALLOC

Published 2023-03-15 by Kevin Feasel

Steve Stedman helps us understand a DBCC command:

DBCC CHECKALLOC is a database console command (DBCC) in Microsoft SQL Server that can be used to check the allocation and structural integrity of the data and index pages in a database. Checking the allocation and structural integrity of the pages can be useful for identifying and correcting issues with the database that could cause errors or performance issues.

Read on to learn more and see a couple examples of it in action.

Comments closed

End of Month in Snowflake and SQL Server

Published 2023-03-15 by Kevin Feasel

Kevin Wilkie is ready for that end-of-month paycheck:

When you work with data, you’ll probably need to work with dates at least once a month. That is the nature of the beast. Today, let’s compare working with them in SQL Server and Snowflake. I want to focus only on adding and subtracting months when provided with a specific day.

Along the way, I would also push for a calendar table, so that you can remove some of the more difficult (or even most common) date calculations.

Comments closed

The Importance of Naming Constraints in SQL Server

Published 2023-03-15 by Kevin Feasel

Eitan Blumin gives everything a name:

This article was published by Aaron Bertrand a few years ago, talking about system-named constraints in SQL Server.

The article mostly focuses on the issue of naming conventions as the main issue with system-named constraints and provides a useful stored procedure script to generate sp_rename commands for all system-named constraints.

However, the script in the article provides the solution for only one database and doesn’t support the new “Edge Constraints” that were introduced in SQL Server 2019.

Check out Aaron’s article and Eitan’s follow-up piece.

Comments closed

Thoughts on Linear Regression

Published 2023-03-14 by Kevin Feasel

John Mount shares some thoughts:

I want to spend some time thinking out loud about linear regression.

As a data science consultant and teacher I spend a lot of time using linear regression and teaching linear regression. I have found each of these pursuits can degenerate into mere doctrine or instructions. “do this,” “expect this,” “don’t do that,” “you should know,” and so on. What I want to do here is take a step back and think out loud about linear regression from first principles. To do attempt this I am going to start with the problem linear regression solves, and try to delay getting to the things so important that “everybody should known them without question.” So let’s think about a few things in a particular order.

For thinking out loud, this is laid out rather well, so give it a read.

Comments closed

Understanding Azure Cognitive Search Costs

Published 2023-03-14 by Kevin Feasel

Matt Eland doesn’t want to break the bank:

Let’s continue my recent trend in exploring pricing tips for the various parts of AI and Machine Learning on Azure with a dive into Azure Cognitive Search.

Sometimes confused with the AI offerings of Azure Cognitive Services, the entirely different Azure Cognitive Search is a rich service that allows you to index a variety of files and documents, extract meaning from those documents, and provide rich search results to users.

In this article we’ll explore the pricing structure of Azure Cognitive Search and highlight some things you should be aware of as you plan and develop your Cognitive Search resources.

Read the whole thing if you’re thinking of using Azure Cognitive Search. It’s a good service and I think the pricing model is fairly straightforward, though there are always nuances to these things.

Comments closed

Object Tagging in Snowflake

Published 2023-03-14 by Kevin Feasel

Warner Chaves tags a table:

A tag is a user-defined label that can be attached to a Snowflake object, such as a database, table, or column. Tags can categorize objects based on any criteria that you choose, such as sensitivity, business unit, project, or owner. Once tags have been applied, you can use them to control access to the tagged objects, track usage and costs, and apply policies and rules.

Now let’s apply tagging to a specific use case: identifying sensitive customer data. For example, let’s assume that you have a table in Snowflake called “customers” that contains customer information, including their addresses. We want to categorize the “address” column as sensitive so that we can apply data protection policies and controls.

Click through for a few examples of how to create tags, apply tags to database objects, and review tagged objects.

Comments closed

A Review of Postgres Memory Parameters

Published 2023-03-14 by Kevin Feasel

Henrietta Domborvskaya takes a look at memory parameters in Postgres:

Ordinary PostgreSQL users often do not know that PostgreSQL configuration parameters exist, let alone what they are and what they mean. There is a good reason for such ignorance since, in real life, ordinary users don’t have any say in how these parameters are set. Configuration parameters are set not just for a database but for the whole instance, which may have multiple databases, so any individual user will get the same as others get. To be completely transparent, in some cases, the said ordinary users can specify some parameters just for their own uses, but let’s hold our horses for now.

There are over three hundred PostgreSQL configuration parameters, so no wonder that even experienced DBAs often do not know what each of these parameters does. That is perfectly fine; however, there is a widespread belief that somewhere, in the secret vaults of many consulting companies, there is a treasure chest of perfect PostgreSQL parameter settings.

Read on for more information about config parameters in general, followed by several memory-related parameters you can tweak and some guidance on where to begin with them.

Comments closed