Press "Enter" to skip to content

Curated SQL Posts

Azure Blob Storage Operating System Error 86

Published 2023-09-20 by Kevin Feasel

Jose Manuel Jurado Diaz 86’d that option:

Today, I worked on a service request that our customer got the following error message: Cannot open backup device ‘https://XXX.blob.core.windows.net/NNN/YYY.bak‘. Operating system error 86(The specified network password is not correct.). RESTORE HEADERONLY is terminating abnormally. (Microsoft SQL Server, Error: 3201). Following I would like to share with you some details why this issue and the activities done to resolve it. 

Read on to get a better understanding of what this error actually means and how you can fix it.

Comments closed

Azure AD (or Entra ID) Authentication for SQL Server VMs

Published 2023-09-20 by Kevin Feasel

Deepthi Goguri enables Azure Entra ID security on a SQL Server VM in Azure:

To enable the SQL Server 2022 on a virtual machine to use Azure AD, we need below things:

Deepthi then includes the list of requirements, starting with a managed identity and moving on to permissions and other configuration. It looks like a fair number of steps, but it’s not that onerous a task once you get to it.

And this also gives me a chance to rant about Microsoft marketing a bit, as they are in the process of switching the name Azure Active Directory to Azure Entra ID. Granted, Azure Active Directory isn’t Active Directory (although it does perform a very similar task in a fairly similar way). So to remove confusion that I don’t think really existed, they changed the name to Entra ID. Except that most of the Microsoft documentation still says Azure Active Directory, and we have about a decade’s worth of blog content talking about Azure Active Directory, so when you go searching for the resolution to a problem, you’ll have to search for Azure Entra ID as well as its former name, which means people will still link the product to Azure Active Directory—at least, until the point when there’s enough blog content and documentation in place to replace the large majority of those existing blog posts—and so you renamed a product for no reason. Plus, they picked an ambiguous name that people will pronounce multiple ways: is the “ent” in Entra like “enter the dungeon” or Entra like “a delicious entrée”?

But then again, considering how many pronunciations of Azure there are, maybe this is the plan…

Comments closed

A Review of DataVeil for SQL Server Users

Published 2023-09-20 by Kevin Feasel

Brian Kelley tries out a product:

My organization typically moves production data to other environments. There are a variety of use cases:

  • Testing with the amount and frequency of production data.
  • Performing analytics on said data.
  • Delivering production-like data to a third party for their use.

We do not want to move production data around. Instead, we want to deliver “production-like” data for these use cases. Sometimes, we work with multiple systems integrated with each other, and in those cases, we need the data to match up. In other instances, we need the sensitive data, such as personal identifiable information (PII), to be altered so it’s no longer sensitive, but there’s no requirement for it to be consistent across systems.

Read on for Brian’s full review. I should also note that this is most definitely a paid product.

Comments closed

Verifying a Backup in SQL Server

Published 2023-09-20 by Kevin Feasel

Chad Callihan knows your last backup is only as good as your last restore:

Is the process of testing your backups something you know you should do but never get around to? Do you find yourself assuming all is well with backups while putting out other fires? Test-DbaLastBackup, part of the beloved dbatools, can solve your dilemma.

There are many options available when using Test-DbaLastBackup. Let’s explore a few of these options and see some examples of how to use them.

Click through to learn more about this. And you could easily put together Powershell scripts to stagger your restorations over a time frame (such as, 15% of your databases each day, so that you get to 100% by the end of the week).

Comments closed

An Introduction to R Markdown

Published 2023-09-19 by Kevin Feasel

Adrian Tam continues a series on R:

One reason people would like to use RStudio for their work is because of the R Markdown. This made the RStudio not only an IDE for programming in R, but also a notepad in which they could put down their thoughts with R code and results. In this post, you will learn how to use R Markdown. Specifically, you will learn

  • What is Markdown
  • How to use Markdown to create a technical document in RStudio

Click through to learn more. I’d also suggest diving into the docs for knitr.

Comments closed

Multivariate Histograms in R

Published 2023-09-19 by Kevin Feasel

Steven Sanderson wants multiple breakdowns:

Histograms are powerful tools for visualizing the distribution of a single variable, but what if you want to compare the distributions of two variables side by side? In this blog post, we’ll explore how to create a histogram of two variables in R, a popular programming language for data analysis and visualization.

We’ll cover various scenarios, from basic histograms to more advanced techniques, and explain the code step by step in simple terms. So, grab your favorite dataset or generate some random data, and let’s dive into the world of dual-variable histograms!

Click through for several techniques.

Comments closed

Power BI Shareable Cloud Connections and Multiple Connections

Published 2023-09-19 by Kevin Feasel

Chris Webb shows off an interesting aspect of a new feature:

A few weeks ago an important new feature for managing connections to data sources in the Power BI Service was released: Shareable Cloud Connections. You can read the blog post announcing them here. I won’t describe their functionality because the post already does that perfectly well; I want to focus on one thing in particular that is important for anyone using Power BI with Snowflake (and, I believe BigQuery and probably several other non-Microsoft sources): Shareable Cloud Connections allow you to have multiple connections to the same data source in the Power BI Service, each using different credentials.

Read on to see what Chris means and how you can take advantage of it.

Comments closed

Alternatives to GREATEST and LEAST in SQL Server 2022

Published 2023-09-19 by Kevin Feasel

Drupal Grupal gives us alternatives:

If you haven’t already heard, SQL 2022 has introduced a new built-in system function called GREATEST. Simply put, it is to a set of columns, variables, expressions etc. what the MAX function is to a set of values (i.e., rows) of a single column or expression. The opposite of GREATEST function is LEAST function which returns the smallest value from the supplied list of columns, variables, constants etc. GREATEST & LEAST can be considered a pair, just as MIN/MAX functions are.

In other situations, GREATEST() and LEAST() are known as ARGMAX() and ARGMIN(), respectively.

Drupal shows us two alternatives to the built-in function and includes a performance comparison.

Comments closed

Dueling Sequences for Positive and Negative Numbers

Published 2023-09-19 by Kevin Feasel

Jose Manuel Jurado Diaz hears banjo music:

SQL Server’s INT data type, by design, provides a range from -2,147,483,648 to 2,147,483,647. But often, developers only utilize the positive range for primary keys and other identifier fields, effectively wasting half of its potential. What if we could harness this full range to temporarily extend the capacity of an INT column? In this article, we explore this idea in-depth.

I’m of two minds here. On the one hand, surrogate keys don’t have a meaning (by definition!), so it doesn’t really matter if that number is positive or negative. Also, including negative numbers makes sense when you expect the steady state table size to be above 2 billion but below 4 billion rows (assuming that you’re using an INT datatype), or you’ve found out that the steady state size is that big after the fact.

On the other hand, I don’t like having a caller define whether they want positive or negative values, as that now imbues meaning to the surrogate key, where positive keys mean one thing and negative keys mean another.

Comments closed

Microsoft Fabric Data Warehouse in a Database Project

Published 2023-09-19 by Kevin Feasel

Kevin Chant creates a database project:

In this post I want to cover how you can share a Microsoft Fabric Data Warehouse Database Project with the new target platform.

Which is now possible thanks to the latest Azure Data Studio Insiders update. You can view the ‘Add projects support for Fabric DW‘ pull request in the public azuredatastudio GitHub repository.

Kevin takes us through creating the database project in Azure Data Studio and then using Azure DevOps or Azure Data Studio to deploy it back out.

Comments closed