Press "Enter" to skip to content

Curated SQL Posts

Content Security Policies and Posit Connect Apps

Published 2023-03-02 by Kevin Feasel

Theo Roe gets into some web security:

Heads up! We’re about to launch WASP, a Web Application Security Platform. The aim of WASP is to help you manage (well, you guessed it) the security of your Posit Connect application using Content Security Policy and Network Error Logging. More details soon, but if this interests you, please get in touch.

This blog post is aimed at those who are somewhat tech literate but not necessarily a security expert. We’re aiming to introduce the concept of Content Security Policy and teach some of the technical aspects.

This does provide a nice overview to the topic and explains the key “what” and “why” answers.

Comments closed

Sun Modeling and SunBeam

Published 2023-03-02 by Kevin Feasel

Shannon Bloye takes us through a new analytics systems modeling technique:

 Sun Modelling was a technique initially developed and taught by Mark Whitehorn as a professor of analytics at the University of Dundee. Which is where our own Terry McCann encountered the approach whilst studying for his MSc. He does a great talk on the topic in this video.

A core aim of the method is to offer a simplicity that makes it accessible to end users as well as the usual technical professionals. The approach is a high-level visual means to model data around a business process.

This feels a bit like a Kimball model but where you’re explicitly diagramming hierarchies and common slicers.

Comments closed

Working with Managed Private Endpoints in Synapse

Published 2023-03-02 by Kevin Feasel

Sergio Fonseca continues a series on Synapse connectivity:

When you create your Azure Synapse workspace, you can choose to associate it to an Azure Virtual Network. The Virtual Network associated with your workspace is managed by Azure Synapse. This Virtual Network is called a Managed Workspace Virtual Network or Synapse Managed VNET. 

I am 100% in favor of using managed vNETs with Synapse and about 40% in favor of using Data Exfiltration Protection—it’s a lot lower because of the impact it has on your developers, though if you need it, developers will just have to deal with the added pain.

Comments closed

Deciding on When to Automate

Published 2023-03-02 by Kevin Feasel

Jeffrey Hicks shares some hard-earned wisdom:

I’ve been scripting and automating things since the days of DOS 3.3, beginning with batch files. It always felt like magic. I could cast a charm simply by typing a few characters on a keyboard. Naturally, my magic skills went from batch files to VBScript to PowerShell. Throughout it all, I’ve also had an internal decision tree regarding automation. Over the years, I’ve seen IT pros new to scripting and automation needlessly struggle. Often it is due to a deficiency in their decision tree. Today, I thought I’d help you nurture yours.

There’s a lot of good advice here about where the automation inflection point is, choosing the right tool, and performing research first before trying to jump into a project.

Comments closed

Approximate Percentiles in Azure SQL DB and MI

Published 2023-03-02 by Kevin Feasel

Balmukund Lakhani announces a feature has gone generally available:

Today, we are announcing General Availability (GA) of native implementation of APPROX_PERCENTILE in Azure SQL Database and Azure SQL Managed Instance. We announced preview of these functions in October 2022. Since then, many customers have adopted these for the applications where response time of percentile calculation was more important than the accuracy of the result.

I have and will continue to extol the virtues of these two functions wherever I go. They’re considerably better than the originals once you start getting into the hundreds of thousands or millions of rows. They’re also available in SQL Server 2022.

Comments closed

Copy-Only Backup and Next Automatic Backup

Published 2023-03-02 by Kevin Feasel

Jose Manuel Jurado Diaz diagnoses an error:

Today, we worked on a service request that our customer got the following error message: BACKUP WITH COPY_ONLY cannot be performed until after the next automatic BACKUP LOG operation [SQLSTATE 42000] (Error 41937) BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013), running a manual backup.

Click through to learn when you might see this error and what you can do about it.

Comments closed

Disabling Classic Pipelines in Azure DevOps

Published 2023-03-02 by Kevin Feasel

Kevin Chant shares some thoughts:

In this post I want to share my thoughts about disabling classic pipelines in Azure DevOps. Which I know there are mixed feelings about.

In addition, I want to raise awareness that this is now possible. Due to the fact that towards the end of January Microsoft announced that you can now disable creation of classic pipelines in Azure DevOps.

In other words, you can now disable the use of the GUI-based Classic Editor and the Releases features in Azure Pipelines.

I agree with Kevin here: it’s generally time to bite the bullet on infrastructure as code if you haven’t already. We talk about it in the data platform context a lot (database schemas in source control, repeatable deployment processes, maintaining config files and applying them) and it matters just as much elsewhere.

Comments closed

Generating Nested Time Series Models

Published 2023-03-01 by Kevin Feasel

Steven Sanderson can’t stop at just one time series:

There are many approaches to modeling time series data in R. One of the types of data that we might come across is a nested time series. This means the data is grouped simply by one or more keys. There are many methods in which to accomplish this task. This will be a quick post, but if you want a longer more detailed and quite frankly well written out one, then this is a really good article

The quick post doesn’t include a lot of commentary but does show the code you’d use for the operation.

Comments closed

Brief Code File Analysis with Python

Published 2023-03-01 by Kevin Feasel

Matt Eland reviews the code:

Last year I devised some ways of analyzing the history and structure of code in a visual way, but I didn’t document much of that to share with the community. This article explores the process I used to build a CSV file containing the path, extension, project, and line of code count in all source files in a project.

Click through for the Python code and an explanation of what it’s doing.

Comments closed

The Most Common SQL Server Engine Errors Generating Support Tickets

Published 2023-03-01 by Kevin Feasel

Joseph Pilov collects a list:

About 6 months ago we decided to look at what SQL Server engine error messages are most commonly generating support cases to Microsoft. The end goal was to update the documentation for those error messages to allow our customers to find answers for themselves before they have to call Microsoft for technical assistance.

The task, as we suspected from previous experience, was not easy because we had to mine cases for error numbers and a relatively small number of support cases get reported with error messages when they are opened. Please report full error messages when you open support cases with Microsoft – it would help us get you answers faster. Still, we were able to find the trends even from the small percentages because were looking for the top 20 or so most common errors, based on case count, and we needed relative information – which error is reported more than another.

Click through for the list. A benefit from going through this exercise is that Microsoft has provided more information on each of those error IDs, hopefully making it easier for people to diagnose and resolve problems without needing to reach out to support.

Comments closed