The Bit Bucket

I know it’s been a while, but there’s a new SQL Down Under show published!

While I’ve been busy with https://cosmosdownunder.com and https://fabricdownunder.com , I haven’t forgotten the SQL Down Under shows. It’s time for some more SQL Server love.

I had the great pleasure yesterday to record a podcast with one of my UK based friends Rob Sewell. Rob’s one of the rare breed that is both a Data Platform MVP and an Azure MVP.

2023-11-11

One of the conferences that I enjoy speaking at each year is the Data Science Summit that comes out of Warsaw.

Once again, the speaker lineup looks excellent, and there are early bird discounts available now. I’m always amazed at how low cost these Eastern European conferences are.

https://dssconf.pl/user.html?lang=en#!/login

2023-11-02

I had the great pleasure to get to spend time today, talking with Will Thompson. We were discussing Data Activator in Microsoft Fabric. Data Activator has just entered public preview.

Will is a Program Manager at Microsoft, where he’s working as part of the Microsoft Fabric team. During the show, Will discusses the two typical use cases for Data Activator: one that’s about taking action on standard analytics, and one that’s about working with streaming data.

2023-10-13

In a previous post, I wrote about how to create a hash of all the columns in a table, by using FOR JSON PATH and HASHBYTES. This is incredibly useful if you need to check if incoming data is different to existing table data.

The code that I suggested (based on WideWorldImporters) was as follows:

SELECT po.PurchaseOrderID,
       HASHBYTES('SHA2_256', pod.PurchaseOrderData) AS HashedData
FROM Purchasing.PurchaseOrders AS po
CROSS APPLY
(
    SELECT po.*
    FOR JSON PATH, ROOT('Purchase Order'), INCLUDE_NULL_VALUES
) AS pod(PurchaseOrderData);

The challenge with that code though, is that for the existing table data, it’s best calculated when the data is stored, rather than every time it’s queried. And, bonus points if you then create an index that holds just the key for matching plus an included column for the HashedData. With a bit of careful work, you can get an efficient join happening to find differences.

2023-10-09

I want to call attention to an upcoming change that I’m surprised I haven’t heard a lot more comments about. When you deploy an Azure VM, by default, they’ve always had the ability to connect outbound to the Internet.

That’s going to change.

Note the contents of this official notice: Default outbound access for VMs in Azure will be retired— transition to a new method of internet access.

The TLDR version of this, is that after the change date, when you deploy an Azure VM, it will not have outbound connectivity to the Internet.

2023-10-04

I took AI-102 yesterday. It was the first exam that I’ve taken where Learn content was available. There’s an icon at the bottom centre of the screen, beside the one that lets you take a break. (I haven’t used that break option yet but that was also a great recent additon).

Did it help?

There were a couple of places where I found it helped. It’s not going to teach you the content, even though the training parts of Learn are available. That would mostly take far too long. What it worked well for, was to confirm the purpose of a specific option, etc.

2023-09-30

I was recently sent a copy of a new book by Ahilan Ponnusamy and Andreas Spanner, called Technology Operating Models for Cloud and Edge: Create your purpose-built distributed operating model for public, hybrid, multicloud, and edge.

It was interesting to read this book.

I spend more of my time on the Azure side of the fence than the AWS / RedHat side of things, so I have some pretty different opinions to the authors on many of the topics. I’ve have worked with multiple clouds, so what they were describing was familiar anyway.

2023-09-28

Microsoft Learn has a community content pilot running right now. They are looking to see how content from community members might provide different value to the content provided by their team members.

So, I decided that I should submit an article to see how it goes, and to find out how it works.

At present, it’s all hosted in a GitHub repository, and no surprise, is all written in Markdown.

2023-09-26

This is part 4 of a series on working with change data capture (CDC) in Azure SQL Database. This part discusses how to enable it and how to use it.

When I was reading the documentation for CDC in Azure SQL Database, I kept coming across a mention that it required at least an S3 service level objective (SLO), if you were using a DTU-based database.

I really hoped that wasn’t the case.

2023-09-23

It’s scary how much of the world’s data lives in CSVs. Yet, as a standard, it’s problematic. So many implementations just don’t work as expected. Today, I had a client asking about why Azure Data Factory wouldn’t read a CSV that, while odd, was in a valid format.

The simplest equivalent of the file that wouldn’t load, would be one like this:

First Column,Second Column,Third Column,Fourth Column
12,Terry Johnson,Paul Johnson,031-23423
13,Mary Johnson,"Paul,Johnson",031-23423
14,Mia Johnson,"Paul ""the beast"", Johnson",031-23423
16,Cherry Johnson,Paul Johnson,031-23423

There are meant to be four columns. The source system wrapped strings in quotes only when the string contained a comma, as that was the delimiter for the file.

2023-09-12