Service Principal vs Service Principle -> not the same thing!

I can't tell you how many times lately, that I've seen people writing about service principles when they really mean service principals. These are entirely different concepts!

I did a certification exam the other day, and it was asking about service principles. I was left wondering how many people had reviewed that before it was released.

But what's worse, is when people bake the wrong one into code libraries that others need to use. I was reading some content from Microsoft Learn: Quickstart: Manage data with Azure Cosmos DB Spark 3 OLTP Connector for API for NoSQL and I saw this code example:

Note the authType. I just thought it was a typo and was going to make a pull request to fix it. But then I went looking at the library that it was using. That's Azure Cosmos DB OLTP Spark 3 connector, and what amazed me, is that the typo was right throughout the library.

I've passed about this back to the Cosmos DB team but it left me wondering what you should do when you see this sort of error in a library.

Do you just use the typo in your code and just forever propagate the mistake?

What should library authors do, to avoid breaking existing code? Perhaps allow both the correctly spelled option as well as the wrong one?

Service Principal

A service principal is a security identity used by applications, services, or automated processes to access specific resources or perform tasks within an Azure Active Directory (Azure AD) tenant. It's most commonly a type of service entity that can be assigned permissions to access resources in a secure manner. These are very common in Azure but they appear in many places across the IT industry.

Service Principle

A service principle really isn't something we talk about often but if we did, it would be perhaps a rule that we try to follow when providing service. Richard Koch wrote a famous book called The 80/20 Principle. That's often called The Pareto Principle. Either way, it's got nothing to do with an identity that performs actions in IT systems.

 

 

Fabric Down Under show 2 with guest Josh Caplan discussing OneLake

I had the great pleasure to get to spend time today, talking with Josh Caplan about OneLake.

Josh Caplan serves as a Principal Group Product Manager at Microsoft, where he's now leading product management for Microsoft OneLake. He has a strong background in managing products like Power BI, SQL Server Analysis Servers, and Azure Analysis Services. Before his current role, Josh contributed to enhancing developer tools for Power BI and worked with Bing to harness its vast data resources.

OneLake is a foundational aspect of all things Fabric.

In the show, Josh provides a solid introduction to OneLake, and then we delved into many aspects of how it works, and what it means for organizations.

You'll find it here: https://fabricdownunder.com

I hope you find it useful.

Book Review: Tripping Over Myself – Shaun Micallef

I've been a big fan of Shaun Micallef for many years. So I was very excited to listen to his new book Tripping Over Myself, A Memoir of a Life in Comedy both to learn more about his background, and to hear his thoughts on it.

I truly appreciate his sense of humour, and as someone who speaks publicly regularly, I appreciate watching his timing and delivery. That's the number one reason why I wanted to listen to this on Audible rather than reading the book. Shaun reads the book himself, and I knew that hearing him deliver the content would add to the value it brings.

And I was right. I thoroughly enjoyed the book.

Content

It's not for everyone. There are many, many Australian references that I think would be lost on anyone who hasn't lived here, and also, who hasn't lived here long enough to understand all the references. Fortunately, that suited me just fine.

Shaun does a wonderful job of seeing the funny side of life. He does poke fun at people, but even more so at himself. I love his self-effacing humour.

Hearing the background about TV shows that I've seen in the past was fascinating. The book does a good job of letting you see how his style has developed over the years.

Mad as Hell

Shaun's most recent TV series in Australia was Mad as Hell. The name of the show was a reference to a famous line delivered by Peter Finch in the movie Network, often described as a satirical black-comedy drama about a newsroom. I remember Peter Finch saying "I'm as mad as hell and I'm not going to take it any more" at a crucial part of the movie.

While Shaun was the clear star of the Mad as Hell show, he (and/or the producers no doubt) assembled such a talented team around him. Shaun always produced a masterclass in comedy timing and delivery.

The one exception I'd make to this is his desire to keep impersonating Kenneth Williams. That really, really needs to be removed from his performances.

Summary

I don't normally listen to biographies. As I mentioned, I loved it. Well done Shaun.

9 out of 10

 

Happy Birthday Windows Server (the artist previously known as Windows NT) !

On July 27th 1993, Microsoft released Windows NT, the forerunner to the versions of Windows we use today, particularly the server versions.

If you're old enough, one thing you'd remember about this event is how signficant it was. At the time, if you wanted a server-based operating system on a PC, you didn't have that many choices. We were predominantly using variants of Unix, most commonly Xenix.

Importantly, most other server systems were largely text-based. With Windows NT, Microsoft brought the power of a graphical interface to mass market server operating systems.

But that wasn't all that Windows NT offered. The team that Dave Cutler led produced a processor-independent,  multi-user and multiprocessing operating system.

The NT moniker was a challenge over time. At release, people said it stood for New Technology.  We presumed that was to separate it from OS/2 that their partnership with IBM had offered. The project that become Windows NT was initially going to release OS/2 version 3.0.

HAL

Windows NT included a hardware abstraction layer (HAL) that allowed it to run on processors from different processor families. As well as the Intel x86 architecture that we all know today, it could run on DEC Alpha, and on MIPS processors. It was also later ported to other operating systems. Curiously, the project initially targeted the i860 (aka N10) processor but no commercial release for that processor ended up shipping. Even the DEC Alpha version wasn't quite ready at release.

Environment Subsystems

One interesting aspect of the operating system was that it supported multiple execution environments. The intent was to make the kernel separate from the APIs used for programming. At release, Windows NT supported not only the Windows APIs (Win16 and Win32), it supported OS/2 apps and POSIX apps. I'm sure the last option was there so that it couldn't be automatically rejected in tender processes for people like the US government, because POSIX-compliance had become part of many procurement rules.

Microsoft wasn't the only company to produce these subsystems. As an example, Softway Systems (who Microsoft later acquired) created a more complete Unix variant called OpenNT that was built on top of the POSIX layer.

I was involved in a project that was creating a more secure execution subsystem that would only execute code produced by a specific secure compiler, and signed by that compiler. Not everyone wanted a general purpose operating system that would run anything (including viruses) that you threw at it.

Happy Birthday Windows Server !

From the server direction, we currently have variants of Windows Server that have a fairly direct lineage to Windows NT. Given it's release was 30 years ago today, it's time to say Happy Birthday !

SQL Question: ROUND() didn't go to the same school I did?

I had an email from a friend today where he asked what was wrong with SQL Server, and wondered if ROUND() didn't go to the same school that he did.

The example was:

The output was:

What was puzzling him is why SQL Server's ROUND was converting 94.55 to 94.5 and not 94.6. Now writing numbers as strings is problematic to start with, but that aside, he was puzzled by the output.

Rounding

There isn't just one "correct" way to do rounding. Here is info on it: https://en.wikipedia.org/wiki/Rounding

The T-SQL ROUND() function does apply "standard" mathematical rounding. Values from 5 and up in the next digit go to the next value, less than 5 goes down. So you'd expect 94.55 to be 94.6 not 94.5.

It's worth noting that not all languages do this. Some languages implement Bankers' Rounding. This was designed to avoid the skew that you can get if you have a whole lot of .5 values. For example, 2.5 + 3.5 + 4.5 would be 3 + 4 + 5 = 12 if you rounded them all to integers first. With Bankers' rounding, it alternates. 0.5 goes to 0, 1.5 goes to 2, 2.5 also goes to 2, 3.5 goes to 4, etc. And so the 2.5 + 3.5 + 4.5 would be 2 + 4 + 4 which is 10, and closer to the underlying total of 10.5.

Older versions of VB, etc. used Banker's Rounding, but that's not what the T-SQL ROUND() function does.

So what's up with ROUND() ?

In that case, what's the problem with his original query?

The issue is data types. He didn't provide decimal values to round, he provided strings. That means they have to go to another data type before they are rounded. Which one?

If I execute this code, you might see the problem:

as it returns this:

Note that the value is a float. And as I've talked about in this blog many times, floats are evil for business applications, and a common mistake.

To see the difference, instead of using the implicit conversion, try this:

And that returns this output:

Note that the rounded value is the expected value, and the output data type is the expected decimal type.

Finally, also keep in mind what when SQL Server Management Studio shows you a float value, it also rounds it before it shows it to you. So a value like 9.9999999999999999 might appear as 10.0 but it's not 10.0.

Hope this helps someone.

Book Review: Leap First by Seth Godin

I'm a fan of Seth Godin, and have always enjoyed listening to him. Recently I finished listening to Leap First on Audible. 
 
When I started listing to this book, I didn't quite know what to make of it. I always enjoy Seth's anecdotes. At first, the book seemed more like a series of anecdotes than an in-depth treatment of the topic. It seemed to lack a continuous train of thought. I started feeling like I was listening to a collection of anecdotes from Seth, rather than a "real" book.
 
It's brief: only 2 hours 6 minutes long. I started to wonder if Seth had felt the need to push out another book, when one wasn't needed.

But Later

I was wrong.

As the book continued, a real train of thought did emerge to tie the thoughts together. I ended up quite enjoying it. The book is a recording of parts of a session that he was delivering. The audience was aspiring entrepreneurs and others. And Seth is a great public speaker.

 
I loved when he called us all out on procrastination, and our failure to "leap".  Other people's reviews were strong. "Highly recommended for anyone who might be stuck or trying to find meaningful work."  This is where the brevity of the book might well be a good thing. I can imagine people listening to it many times, particularly when they feel the need for a "pick me up" in their work.
 
I loved the stories of authors reading the one-star reviews of their work, and the analysis of why they do that. I've seen that many times and he's dead right.

Summary

A brief but interesting book. Don't let the feeling that you are listening to a collection of anecdotes put you off. An message does shine though as the book continues.

7 out of 10.

 

Book Review: The Incredible Unlikeliness of Being

Ever since I watched Professor Alice Roberts' series on travelling Egypt by Train, I've been quite a fan of her work. (It also means I probably came to knowing about her later than I should have). Since then, I've been working through a number of her books. On Audible, I just finished listening to The Incredible Unlikeliness of Being.

Amongst many other things, Alice is an English biological anthropologist. She also worked as a doctor in the National Heath Service in Wales for a while, but she left clinical medicine to focus on anatomy.

This is an amazingly powerful book.

I can't describe just how many things I learned when listening to it. Alice shows a mastery of so many disciplines (like anatomy, genetics, biology, evolution, and more) and combines them to tell a detailed story of the evolution of the human body. She fills in so many gaps that I didn't understand about why things in the body are the way they are, and importantly, how they came to be that way.

Alice works through each of the major systems of the body, one by one, and describes so much about how they function in humans, and how they function in other animals, particularly those that share a common ancestor with us in the tree of life.

When I was at high school, biology was considered a softer science than chemistry, psychics, mathematics, etc. In fact, in an indication of the appalling gender-divide back then, at our school biology was taught over at the girls' high school and males who wanted to learn, needed to go there. Worse still, because it didn't rate as well as other sciences, it put students in a less competitive situation for university entrance. That meant that if you wanted to be a medical doctor, the worst thing you could do was to study biology at school, as it would reduce your chances of getting into medical school. How ridiculous!

As I've aged, I've now built a complete fascination with biology. I wish I'd spent more time on it when younger. This book hit the mark for me nicely.

Audio

I've seen a few comments from people (one or two from the USA) who have some difficulty with Alice's accent and found the book harder because she read it herself. I couldn't disagree with those comments more. I'm so very glad that Alice read the book herself. She has a steady pace, a clear accent, and is obviously used to teaching. Her accent is a pretty straightforward English accent.

The Verdict?

After learning so many things from the book (and it's a long book), I got a hint of just how much more I don't know about this.  At some point, if I have another 11 hours 15 minutes to spare, this is one book that I'll likely listen to again, to try to catch some of what I'm sure I missed.

10 out of 10

 

 

SDU Tools: List use of Deprecated Data Types in a SQL Server Database -> Updated

I've previously posted about the procedure ListUseOfDeprecatedDataTypes in our free SDU Tools for developers and DBAs. I mentioned that I'm often reviewing existing databases and one of the first things I go looking for is the way they've used data types, and that in particular, I'm keen to know if they've used any deprecated data types (i.e. ones that will/might be removed at some point).

The procedure was updated in version 21 to now include a ChangeScript column. (Thanks to Michael Miller for the suggestion).

It still has the same parameters:

@DatabaseName sysname – This is the database to process
@SchemasToList nvarchar(max) – a comma-delimited list of schemas to include (ie: 'Sales,Purchasing') or the word 'ALL'
@TablesToList nvarchar(max)- a comma-delimited list of tables to include (ie: 'Customers,Orders') or the word 'ALL'
@ColumnsToList nvarchar(max) – a comma-delimited list of columns to include (ie: 'CustomerName,StreetAddress') or the word 'ALL'

And still returns one row for each use of each deprecated data type.

The columns returned are SchemaName, TableName, ColumnName, DataType, SuggestedReplacementType, and now also ChangeScript.

You can see the previous version in action here:

In our own internal version, we also flag money and smallmoney types as we don't want them used, but the tool doesn't flag those.

But let's look at an example of using the update, in this case against msdb. (I wish msdb wasn't still using types that were deprecated in 2005 and it's now 2022):

Deprecated types in msdb

Note the new ChangeScript column that is output.

To become an SDU Insider and to get our free tools and eBooks, please just visit here:

http://sdutools.sqldownunder.com

SQL: Understanding Change Data Capture for Azure SQL Database – Part 3 – Enabling and Using CDC

This is part 3 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.

To show how this works, I have created an Azure SQL Database called CDCTest. I created it as a DTU-based database with a service level objective (SLO) of S3. I'll discuss more about the licensing implications of CDC in part 4.

Connecting and Setup

I've connected to the database using SQL Server Management Studio (SSMS) and opened a query window to the new database. I've then executed the following to create the objects:

If I check the entry in sys.databases by executing this command:

The output of the column is as shown:

CDC not enabled

Enabling CDC at the DB Level

So let's start by enabling CDC for the database, by executing:

Checking sys.databases the same way again now shows it enabled:

CDC is enabled

That's how we can check if it's enabled at the DB level. But there are other changes that have occurred. First, there's a new schema that's been added:

New CDC schema visible

And it contains a number of objects:

CDC schema objects

Enabling CDC at the Table Level

Next, let's enable it at the table level and see what's changed.

We did several things here. We said that the source table is called NewEmployees and that it lives in the dbo schema. We then said that this capture instance will support net changes. That means that as well as providing each individual change, we can ask CDC for the net affect of a set of changes. We'll see that later. And finally, we told it that for now, only admins can access the data. Otherwise, we'd have had to provide a name for the role that contains the users who can query this.

We can then see that the table is enabled for CDC by querying sys.tables:

sys.tables

But wait a minute. We only created one table right? What are these others? We can find them if we expand the tables in the database:

List of tables - more than expected

Note all these extra ones are system tables.

One other thing we did, even though we didn't specify it, is to create a capture instance called dbo_NewEmployees. That's the default name but we could have named it by using a parameter. Each table can have two capture instances to allow for handling schema changes and/or other requirements.

We can see the list of capture instances for a table by using this query:

capture instance details

This shows the configuration of the capture instance, the start and end points of the log that it relates to, and more, like the name of the index in the table, etc.

Using CDC for the Table

Now let's make use of CDC for this table. I'll start by inserting three rows, then updating one of them.

Now let's check out what happened by calling the CDC functions:

When calling these functions, I needed to specify a range of log sequence numbers (LSNs). I called sys.fn_cdc_get_min_lsn to get the minimum value available to the capture instance (note: not to the table), and used sys.fn_cdc_get_max_len to get the maximum value that's available from the log right now.

Normally, I'd want to record where I'm up to and get values past that point.

The output of the first SELECT using cdc.fn_cdc_get_all_changes_dbo_NewEmployees was:

Get all changes

Note that it shows every operation i.e. the three inserts (with __$operation as 2) and an update (with __$operation as 4).  The primary key value is also shown, and the LSNs where it all occurred.

Also note that the name of the capture instance is part of the name of the function that was created when we enabled CDC for the table.

Often I do want every operation, but sometimes I don't care about all the individual operations. I just want the net effect. That's what's shown in the second SELECT using cdc.fn_cdc_get_net_changes_dbo_NewEmployees:

Net effect

This one is interesting. Inserting 3 rows, then updating 1 of those rows, is the same as inserting 3 rows but one of them with the updated value. And that's what you get from this function.

And that's the basic enabling and use of CDC in Azure SQL DB. We'll move to licensing implications in the next part.

  1. Why use Change Data Capture for Azure SQL Database?
  2. How Change Data Capture works in Azure SQL Database
  3. Enabling and using Change Data Capture in Azure SQL Database
  4. Change Data Capture and Azure SQL Database Service Level Objectives
  5. Accessing Change Data Capture Data from Another Azure SQL Database

 

Announcing the Fabric Down Under podcast – Show 1 available now

I've mentioned in a previous post how important I think Microsoft Fabric is going to be.

So, in addition to our existing SQL Down Under, and Cosmos Down Under podcasts, we have a brand new podcast called Fabric Down Under.

In the preview for Microsoft Fabric, one of the key participants from the product group has been Pawel Potasinski. I've known Pawel for many years and he's been the driving force behind many important things. It was great to see him driving community around Microsoft Fabric in preview.

So he was the obvious choice for the first podcast in the series.

I hope you enjoy it. You'll find all the shows and details on subscribing at our site: https://fabricdownunder.com