Conferences are back, baby!

I’m in London for SQLBits 2022, the biggest Microsoft data conference in Europe. This year’s theme is classic video games, and it’s been really cool to see the 8-bit logos and games all over the ExCel Center.

Today is the Microsoft keynote. It’s called Level Up with Azure Data, hosted by Bob Ward, Buck Woody, and a whole crew of Microsoft folks.

Bob Ward started by taking a moment to think about what’s happening to our colleagues in Ukraine.

In an effort to make performance tuning easier, SQL Server 2022 is expanding the Intelligent Query Processing features, and Query Store will be on by default. (These aren’t new changes – they’ve been discussed in the past – but Microsoft’s just reminding folks of what’s coming.)

Pedro Lopes took the stage to talk about parameter-sensitive plan optimization, aka PSP Optimization. He demoed it with SQL Server 2022 CTP 1.3. I’ve written about this feature before, and there wasn’t anything new here in the demos. My opinion on this feature remains the same: I think it sounds like a phenomenal down payment. It won’t fix parameter sniffing, but I don’t think it’s going to backfire.

Bob Ward and Buck Woody then demoed SQL Server Ledger based on blockchain something something. They demoed it by showing a Python notebook in Azure Data Studio – they didn’t run the queries live interactively, just walked through the existing results in the notebook. They kinda hustled through it really quickly, so I don’t think most employees caught it, but here’s what happened:

• Bob Ward ran update statements
• Ledger caught that and showed what he did
• Bob Ward changed pages in the database directly without using update statements
• Ledger caught that indirectly by showing that the hash blocks don’t match
• Bob Ward tried to change the ledger data itself
• Ledger caught that too because it’s stored securely

That demo really needs a solid 20-30 minutes to walk through and explain, and it would be really powerful. They just ran through it in a matter of seconds, so the audience didn’t really get what was happening. I actually really love the idea of this – it’s just tricky because it’ll require a hell of a lot of storage if you want to audit a lot of changes on a lot of tables across a long period of time.

Next up, Bob demoed replicating an on-premises SQL Server 2022 instance up to an Azure SQL DB Managed Instance up in the cloud. The wizard built into SSMS automatically created an Availability Group and a Distributed Availability Group up to the cloud – even if the database you’re trying to protect isn’t currently in an Availability Group. I love the idea of this, but I’m terrified of the execution. The diagnostics and troubleshooting of Distributed Availability Groups is a really sore point – it’s practically nonexistent – and it’s only going to get worse when you throw the cloud into the mix.

Anna Hoffman took the stage and announced that the SQL best practices assessment is now available in the Azure portal. It’s like sp_Blitz, but more powerful because it can be scheduled and centralized.

Managed Instances can now query flat files like parquet formats directly from T-SQL with OPENROWSET. It’s like linked servers, but hard coded to file paths. That’s great because file paths never change, and even if they did, it would be easy for you to go back and refactor your queries to point to the new paths. Okay, no, sarcasm over, I don’t have a huge problem with this because I can see analysts who only know T-SQL, but want to run queries over files. I know they exist – I’ve seen my customers doing that – but putting it into long term production gives me the shivers.

Also new in preview are the Link feature for SQL MI, and Hybrid Service Broker.

Anna demoed Azure SQL Hyperscale’s new named replica feature by adding secondary replicas on the fly, using purely T-SQL. Like the earlier Ledger demo, this just went too quickly, but it looks really compelling for workloads where we want the code itself to crank up our server capacity. That isn’t automatic – automatic scale/up down is compelling too, sure – but I actually love the ability to manually scale capacity up/down with T-SQL. For example, one of my clients does nightly warehouse data loads, and when the load is verified, they run an insanely huge amount of scheduled reports. They can’t predict exactly when they’re going to need the read capacity every time because the load runtimes are wildly variant, and sometimes the loads just outright fail. I can totally imagine them modifying their SSIS processes to fire off T-SQL commands to amp up the read-only capacity when they’ve verified that the loads are done.

Bob came back and demoed something with Synapse Analytics, and I missed it. Look, I drank a lot of coffee before the session, and I had to run to the bathroom. Don’t judge me. I don’t do anything with Synapse Analytics, so if there’s a demo I could skip, it’s that one.

Patrick LeBlanc demoed a live-updating dashboard in PowerBI. Amusingly, he put in a video of Adam Saxton supposedly playing Pac-Man, but Adam was hitting a lot of buttons for a game where you just move Pac-Man around in different directions. I can see why Adam’s having a tough time catching up on the leaderboard. 😉

Evangeline White came up to demo Azure Purview, a map and catalog of your organization’s data. Purview is something I think every big company needs. I’ve seen so many big companies where people don’t understand where data is stored, or what the data means, or whether it should be secured or visible everywhere. However, I bet most companies won’t see the ROI on a tool like Purview, so I think even if companies try to implement it, few will configure correctly, and even fewer will keep it up to date. That’s a bummer. I’m not making it my personal mission to evangelize that stuff, though.

Evangeline mentioned that they’d just released the ability to track lineage on the input and output of stored procedures. Oof, that’s gonna be rough. People hardly document anything, let alone document lineage of what a stored procedure modifies. Again – I love the idea of this, but I know how little documentation people write. We’re lucky when we get any documentation about a stored procedure – much less details on the input data, output data, and the changes that were made to it.

Bob Ward came back onstage to announce that the first public preview of SQL Server 2022 will drop by the end of the first half of 2022.