Thoughts from Microsoft Data Platform MVP and RD – Dr Greg Low
I had the great pleasure to get to also record another new Cosmos Down Under podcast last night. It's now edited and released.
Show 9 features Microsoft MVP Hugo Barona discussing his experiences in migrating clients to Azure Cosmos DB, and the lessons he's learned along the way.
I hope you enjoy it.
You'll find it here, along with previous episodes: https://cosmosdownunder.com
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.
In the show, we discuss command line utilities for SQL Server, including the latest go-sqlcmd offering. We also include a shout out to the upcoming SQL Bits conference.
I hope you enjoy it. You'll find this show (and previous shows) here: https://podcast.sqldownunder.com/
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
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.
You'll find the show here: https://fabricdownunder.com
I hope you find it useful.
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:
|
1 2 3 4 5 6 7 8 |
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.
The challenge with this, is that you might have a column in the data that should NOT be checked. Perhaps you've stored a column called HashedData in the table and it should not be included in the hash calculations. Or perhaps there's an identity column that you really need to excluded.
The problem with the code above is that there wasn't an easy way to exclude the column.
What I really wish that SQL Server had is a way to exclude a specific column or columns from a SELECT list.
When you are writing utility code, it would be incredibly useful to be able to say just:
|
1 2 |
SELECT * EXCLUDE (ColumnIDontWant, AnotherColumnIDontWant) FROM dbo.SomeTable; |
I'm not a big Snowflake fan but it's an example of something that it does better than SQL Server in T-SQL.
If I had that, I could have just excluded it in the CROSS APPLY above.
But there is a way around this. Since SQL Server 2016, we've had JSON_MODIFY.
And I can use that to modify the data before hashing it. So to exclude a column called HashValue, I can write:
|
1 2 3 4 5 6 7 8 9 10 11 |
SELECT po.PurchaseOrderID, HASHBYTES('SHA2_256', JSON_MODIFY(pod.PurchaseOrderData, '$."Purchase Order"[0].HashValue', NULL)) AS HashedData FROM Purchasing.PurchaseOrders AS po CROSS APPLY ( SELECT po.* FOR JSON PATH, ROOT('Purchase Order'), INCLUDE_NULL_VALUES ) AS pod(PurchaseOrderData); |
With JSON_MODIFY, we can choose a JSON property to remove by setting it to NULL.
This makes it far easier to write generic code, without the need to list all the columns (as you would with CONCAT or CONCAT_WS), and without the need for dynamic SQL.
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.
I'm not sure how I feel about this. I understand that Microsoft want to raise the bar on secure by default thinking but I can see this one having signficant impacts.
It's not just another switch that you'll need to enable. You will need to use explicit outbound connectivity methods such as:
Note also that every one of those options involves additional costs.
The article makes it clear that it won't affect VMs that are already deployed. While that seems simple enough, it's not. It's not just a case that something already deployed will continue to work.
If someone has scripted their reinstalls, that they'll work one day, but at a day in the future, the scripts they have depended upon (or haven't tested for some time) won't work as expected. Or if they have used scripts to install hosts, when they go to add another one, there will be a day where that won't work as expected any more. And quite a bit of CI/CD code will need to change. Processes that involve exporting ARM templates and deploying VMs will break, etc. etc.
I can see people having lots of scripts, CD processes, etc. that will need revisiting because of this change.
Even for myself, I know that I have PS scripts that I use to roll out VMs if I'm teaching an online class. They'll have to change and will have to deploy additional items and set up different configuration.
I really just wanted to call this out, as I think it's a huge change, and seems to have been flying under most peoples' radars.
I am hoping that at the very least, Microsoft make it obvious to anyone using the GUI, about what they need for outbound connectivity, perhaps even with an option to deploy it automatically for them.
If this change will affect you though, it's time to plan to migrate to an explicit connectivity option.
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).
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.
Questions are meant to be designed around the application of knowledge, not around pure knowledge. (Yes, I know some of these still exist)
The way it works is that a browser appears on the right-hand side of your screen, and your question stays on the left. For most multiple choice questions that's easy enough, but a little more challenging (screen real estate wise), for case studies.
What was hard to work with, was moving the exam monitoring/counter popup thing around, to keep it out of the way of the browser tabs. It was never a problem with exam pages, but that popup thing was designed to be as small as possible, to stay out of the way. I found it hard to move, as you needed to click like a normal window, but there is almost no blank space on it that you can click without doing something, instead of just selecting it.
The team could improve that experience by making sure that popup thing had a blank space that would work well as a handle for dragging.
I was recently asked if there's a limit to the number of tabs you can have open. Someone mentioned that he'd had a large number open. I found it refused to open more after about 5 (didn't note the exact number). But any more than that would be awkward.
What I do find hard with it, is the searching for content. The search bar in Learn is not a good way to search Learn. Let's face it, almost no-one searches Learn content by going to the home page, and using the search bar. I'd guess that 99% of traffic to Learn comes from search engines, and they do a far better job of indexing the content.
We're not going to get a Google search in there (i.e., what most people would actually use), but it would be interesting to have the option to use Bing search and enforce a site restriction for Learn. I didn't try to find out, but I'm guessing they have a limit to any external links that you can connect to from within Learn.
Another important note: in the search, there are options to restrict the scope of the search. Make sure you use them !
I was doing an AI exam, and at first, I'd go looking for a class name, and getting results from Dynamics, etc. that I didn't want. Remember that it's all of Learn, with all products, all documentation, training materials, tutorials, etc. Using the scope options, for this exam, I could say just .NET and Azure, and the results were far better.
After getting into the exam, I knew I had plenty of time as I was pretty confident with the material. I'd spent weeks prepping, and doing the labs, etc. and a lot of it was familiar before I started. (Note for any certification people reading: the labs for AI-102 really need updating).
Because of that, I searched as I went, but I'd strongly recommend that if you aren't really confident on the content, you don't do that.
I did find that when I tried to research topics, that I really burned time. In fact, I burned way, way more time than I was expecting. Most of that was because the searching option isn't good. But you could easily go down long rabbit holes with this, and simply lose track of time.
I'd recommend that as you go through, you choose your best answer even if you have doubts, but flag the question for later review. You'd be better to make sure you've answered everything, then go back to research what you need to check, based on your available time.
Thanks to the certification team for a great new option! Two areas I'd love to see some work on:
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.
I particularly liked their discussion near the beginning of the book that cover what often goes wrong in cloud moves. They talked about issues with:
I regularly write articles on making real cloud transformations not just migrations. Too many people talk transformation, but end up doing migrations, and then enter the well of disappointment. And for their users, things are even worse.
So I liked to see their discussion warning against focusing on technology instead of transitioning people, processes, and culture to enable cloud ROI. And I had a chuckle when I read their recommendation to not start with logos that we want on our CVs. I can't tell you how often I've seen teams makign that mistake.
I'm not 100% convinced by the Robust is out. Antifragile is in. discussion. I get why they're arguing that, but I see it differently. Similarly, I view application lifecycles now as very different to the:
options discussed.
Given my work focus, it's not entirely surprising that I differ a great deal with them in regards to how data should be handled today.
The authors must be seeing far more edge-based services than I commonly see in the market. They had an interesting discussion on that. Quite a large part of the book is dedicated to tackling edge services.
As for hybrid cloud arrangements, I'm quite pleased to see the lead Microsoft has taken in their Arc-enabled offerings.
Finally, I see their arguments around avoiding vendor lock-in. This is a topical one for me. I see people worried by this, and ending up with lousy outcomes.
It reminds me of application code that's written to avoid being locked into specific databases. Invariably, you end up with code that's not great with any database. And eventually, code gets added that's specific to one database. So then you have the worst of all possible worlds: poor code that's database agnostic, yet lock-in anyway because eventually, work just had to be done. And the kicker: I've never seen anyone who does this ever change databases anyway.
The same applies to multi-cloud thinking. What the authors don't really discuss, is that it's easy to end up with outcomes that work equally poorly on multiple platforms.
And it's a similar discussion in relation to committing to only use open source software. I understand the sentiment, and I've used many of the services that they mention in the book. I just haven't loved those services the way I do many of the options that aren't open source.
In today's world, your company can get run over by competitors who are quicker to market by using the best aspects offered by a single platform.
Regardless, I'm glad they've written on these topics. Even if you don't agree, it gives you a list of topics to consider.
7 out of 10
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.
To contribute, you fork the respository, choose a template, and create your content from the template, following the rules contained in the template. You also need to add your content to the Table of Contents. Finally, you raise a pull request and a build agent checks that your content follows the rules. If the team are then happy, you get author access and can merge your content in. Eventually, your new article should appear at Microsoft Learn.
Here's the first article that I contributed:
https://learn.microsoft.com/en-us/community/content/remembering-the-users-in-cloud-transformation
As another example, here's another one from fellow MVP Luke Murray:
https://learn.microsoft.com/en-us/community/content/securing-backups-with-azure
I'd love to hear your thoughts on whether this style of content is useful, and if so, how it should be structured.
If you are interested in contributing, here's a link to get you started:
https://github.com/MicrosoftDocs/community-content/blob/main/CONTRIBUTING.md
You can also find more information in the Microsoft Learn Contributor Guide. It covers style, voice, etc.
https://learn.microsoft.com/en-us/contribute/content/
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.
I was left saying "please say it ain't so!".
I thought it was time to find out what actually happens when you try it.
I started by creating a database with the lowest SLO (Basic). Now I know these don't hold enough data to really even be very meaningful, but I wanted to know what would happen.
I didn't get far.
I created the table from the scripts in part 3 of this series, and then tried to do the first enable of CDC.
To say that's disappointing is an understatement. But i tmakes it clear that Basic, S0, S1, and S2 aren't going to let you enable it.
Then I wondered what would happen if I enabled it on an S3, and then tried to scale down.
So I scaled the DB to S3 ok, and enabled CDC as in part 3. All worked as expected.
I then scaled the DB back to S2 using the Azure Portal. And alas, that failed too:
I suppose I'm not surprised, but I had hoped it might at least have automatically disabled CDC if it really had to.
I think these licensing restrictions for CDC in Azure SQL DB are a mistake.
Back in SQL Server 2016 SP1, I was one of the main instigators to get the product team to make sure that the programming surface of the database worked across all the tiers (Enterprise, Standard, Developer, Express) wherever possible.
This restriction on CDC goes directly against that ethos. It harks back again to large single database thinking, not cloudy thinking.
I understand that it's probably needed for performance in production scenarios, but what I'm always trying to tell the team is that production scenarios aren't the only scenarios.
Developers also need to write code. We should let them write and test functionality. It can be clear to them that it won't work the same as on a production instance, but that's no different to many other aspects of the system. A Basic SLO database won't run a complex query over a lot of data either, but we don't expect it to.
But it's important to let them write their code and make sure it basically functions, no matter what level of SLO they're working with. If you require them all to have an expensive database, just to test code or concepts, you greatly limit how many developers will use the features.Â
Having those types of restrictions also restricts the cloudiness of the solution, in that you couldn't scale up/down across a range of SLOs. We often take S7 or S8 databases and temporarily "park" them at S2 while not in use. This type of restriction kills those options as well, and makes the overall solution less cloudy.
That's service level objectives for the use of CDC in Azure SQL DB. We'll move to accessing CDC data from another Azure SQL DB next.
