Office Hours: SO MANY Good SQL Server Questions

You posted some great questions at https://pollgab.com/room/brento. Here’s what we covered today:

00:00 Start
04:14 Groove_timer: Hey Brent,
Trying to convince my employer to not do In-Place upgrade across our SQL estate that is mostly 2012/14 instances. Rather they did side-by-side. But due to costs constraints they are not willing to do this.
Are SQL Server in-place upgrades still ill advised?
07:15 StuckInAQuagmire: Have you ever had a situation where a stored procedure took too long, so application reran it without stopping the original, and it took long, and it cascaded into zero resources available and the only way to stop it was to stop SQL Service?
09:39 Bomi: Hello! I watch your office hours constantly. Love your teaching pragmatic database usage.
But I’ve never used MS SQL server. Is there a Brent for SQlite or MariaDB or Postgres or something?
Or should I start using MS SQL server just because it has the best learning material?
11:32 Peter: I’ve JUST learned about Query Notifications. Normally I’d use event hooks in an app to raise notifications about data changes, but this would be useful for situations where the data can be changed outside of the app. What are the drawbacks / gotchas to using Query Notifications?
14:00 .Net Dev: Hi Brent, it seems like azure sql DB doesn’t allow me to update the setting for cost threshold for parallelism. Do you know it’s not configurable? Do they automatically modify the setting depending on the needs my DB?
16:50 Garðarshólmur: What is your opinion of the new snapshot backup support in SQL 2022? Is this the answer for multi tb database backup?
18:11 Kevin: Morning Brent, This may seem trivial. I’m a little leery about moving from SSMS 18.12 to 19.1. Any gotchas there that you know of, please?
19:29 Kris: Hi, In SQL Server 2019 is there a way of running dynamic SQL with EXEC where the SQL statement is over 8000 characters?
20:18 Slonik: What are your favorite PostgreSQL blogs?
21:55 Psycho (SPID) Killer: What is your opinion of the hybrid buffer pool / PMEM support in SQL Server?
24:34 CJ: Since SQL Agent alerts aren’t available on Azure SQL Managed Instance, is there a list of equivalent items we can/should monitor thru the Azure Portal (Azure Monitor). Any resources you can provide would be great.
25:40 Dom: What would be the most important “features” that you would see as “requirement” for a monitoring tool ?
We are looking at something that monitor everything so it won’t be “excellent” with SQL but I wonder what is the “must” that it should be monitoring (OS excluded)
27:32 Eduardo: Do you know of any good tools that parse SQL query plan XML to extract the params / param values for use in SSMS query testing?
28:06 Konya: What is your opinion of Azure Synapse?
30:57 Brandon: I’ve seen your article on “3 connection strings” for scaling transactional systems. What do you think are the best options now for providing read only replicas for the near-current and older connection strings?
31:27 Aditya: When should you use async auto update stats vs sync auto update stats?
34:40 Erum: What is your opinion of the Microsoft announcement for new support of .NET 5 C# Language Extension for SQL Server? https://cloudblogs.microsoft.com/sqlserver/2021/09/08/open-sourcing-the-net-5-c-language-extension-for-sql-server/