Press "Enter" to skip to content

Author: Kevin Feasel

String Padding in T-SQL

Published 2021-07-16 by Kevin Feasel

Kenneth Fisher pads some work:

I’ve been working on converting a piece of DB2 code into T-SQL and one of the functions I had to replace was lpad. It’s a simple enough function that will pad a string on the left with another string. So changing 1234 to 00001234. It’s a common enough task when formatting strings. And both DB2 and Oracle provide both lpad and rpad functions. However, guess what? SQL Server doesn’t. So how do we handle that in T-SQL? It’s a pretty easy pattern.

Click through for the answer.

Comments closed

SQL Server 2012 End of Support

Published 2021-07-16 by Kevin Feasel

Debbi Lyons and Vijay Kumar have a reminder for us:

While new innovations keep lighting up in the latest releases of SQL Server and Windows Server, support for older versions along with security updates will eventually end. This can lead to the potential for compliance gaps for workloads that still rely on these versions and create missed opportunities to apply innovation to business-critical workloads. SQL Server 2012 and Windows Server 2012, and 2012 R2 End of Extended support is approaching:

– SQL Server 2012 Extended Support will end on July 12, 2022.

– Windows Server 2012 and 2012 R2 Extended Support will end on October 10, 2023.

The news this week has mostly been about SQL Server 2016 ending mainstream support, but this is a bigger one. Fortunately, there’s still a year to procrastinate plan.

Comments closed

Naming Worksheets in Power BI Paginated Report Excel Outputs

Published 2021-07-16 by Kevin Feasel

Paul Turley answers a question:

This question comes up every few years in SQL Server Reporting Services. Of course, in Power BI Paginated Reports, we have the same features. A couple of days ago, Karthik posted this question as a comment to my post titled Chapter 7 – Advanced Report Design:

I am working on a SSRS report where the grouping is done to group the records in to multiple tabs/worksheets. When the report is exported to excel, the worksheets has the default name (Sheet1, Sheet2, Sheet3,…). Here I want to override the default worksheet name with (Tab1, Tab2, Tab3, …). The number of tabs/worksheets varies each time depending on the parameter selection by the user. How to address this? any suggestions please.

Click through for the answer.

Comments closed

Using Kafka for Security Situational Awareness

Published 2021-07-15 by Kevin Feasel

Kai Waehner continues a series on using Apache Kafka on security teams:

Apache Kafka became the de facto standard for processing data in motion across enterprises and industries. Cybersecurity is a key success factor across all use cases. Kafka is not just used as a backbone and source of truth for data. It also monitors, correlates, and proactively acts on events from various real-time and batch data sources to detect anomalies and respond to incidents. This blog series explores use cases and architectures for Kafka in the cybersecurity space, including situational awareness, threat intelligence, forensics, air-gapped and zero trust environments, and SIEM / SOAR modernization. This post is part two: Cyber Situational Awareness.

Click through for the high-level discussion.

Comments closed

The Benefits of Cluster Sampling

Published 2021-07-15 by Kevin Feasel

Muhammad Touhidul Islam explains what cluster sampling is and why it can be useful:

Cluster sampling is defined as a sampling method where multiple clusters of people are created from a population where they are indicative of homogenous characteristics and have an equal chance of being a part of the sample. In this sampling method, a simple random sample is created from the different clusters in the population. This is a probability sampling procedure.

Click through for a few examples of where this can be useful.

Comments closed

The Benefits of a Cache Layer

Published 2021-07-15 by Kevin Feasel

Monica Rathbun espouses upon the utility of Azure Cache:

One of the biggest impacts on resource consumption for Azure SQL DB are repeated data pulls by the application layer. No matter how fast those queries execute calling the same procedure or issuing the same SQL statements hundreds, thousands, or million times a day can wreak havoc on database performance. Death by a thousand cuts can easily bring a system to its knees. Sometimes it’s hard for DBAs to troubleshoot these actively as the execution of the statements happens so quickly they don’t even show in tools like sp_whoisactive. It’s not until you begin to dive into things like Query Performance Insights or Query Store that you start to see the real issue.

Check it out. And if you want to get into implementation, I’ve found the Cache-Aside design pattern to be useful. Bowen Li has a rundown of several caching patterns as well.

Comments closed

High-Performance ETL via Buffer Table

Published 2021-07-15 by Kevin Feasel

Daniel Hutmacher needs things to zoom:

It’s almost like a myth – one that I’ve heard people talk about, but never actually seen myself. The “shock absorber” is a pretty clever data flow design pattern to ingest data where a regular ETL process would choke on the throughput or spikes. The idea is to use a buffer table to capture incoming data, and then run an asynchronous process that loads that data in batches from the buffer into its intended target table.

While I’ve seen whitepapers and blog posts mention the concept loosely along with claims of “7x or 10x performance”, none of them go into technical detail on how it’s done, so I decided to try my hand at it.

I’ve compiled my findings, along with some pre-baked framework code if you want to try building something yourself. Professional driver on closed roads. It’s gonna get pretty technical.

Combine that with Eitan Blumin’s post yesterday and you’d think it were buffer week.

This shock absorber pattern works well for warehouse loading, especially when you’re trickle-loading data into columnstore indexes and don’t want to have open rowgroups slowing everything down.

Comments closed

Renaming a YAML Pipeline in Azure DevOps

Published 2021-07-15 by Kevin Feasel

Hamish Watson figures out what’s in a name:

I had created a pipeline using YAML – which was called InfrastructureAsCode as the YAMP file was in the root directory.

However I wanted to move it into a folder .\InfrastructureAsCode\pipelines\… and run the YAML file from there – as I would have a non-prod and PROD version of them (as the schedule was different for each).

Click through to see how Hamish was able to resolve this.

Comments closed