Office Hours: Live on Black Friday

I took a break from answering Black Friday email support questions to go through your top-voted questions from pollgab.com/room/brento. Here's what we covered:
00:00 Start
00:41 MatthewsSQLServer: How AlwaysON AG failovers cause can be analyzed, is it DB/Cluster/Infrastructure/AD issue. Can you please suggest any videos or blogs that can help?
02:19 NotADeveloper: We have a 40 TB DB. There are discussions to move the data to MongoDB from MSSQL because it provides sharding and its caching is better than SQL. There is no partition today. Mongo DB's sharding and caching VS SQL's Partitioning scheme, what route is better and why for a VLDB.
04:25 Alex Threepwood: Hi Brent, When a client calls (calls? mail?) for an emergency job, do you charge per hour, or to analyze the problem at hand? And do they know your rate then, or do you send a quote before you get out of bed?
06:04 ImAfraidOfBI: After restoring a database (to test backups, be it manually or automatically), what do you suggest is done to test and make sure that the DB restore is good? Random selects? DBCC checkdb?
07:39 Mobius: What is your opinion of Copilot SQL query optimization?
09:43 Eduardo: Are there any gotchas when upgrading from an older version of Ola H's maint solution to latest version? SQL 2019 Enterprise
10:26 Renzi: What is your opinion of constrained core VM's for Azure SQL VM?
11:33 Iceman-OG: Hi Brent, I'm getting back into SQL Server on prem. I see that PolyBase seems to be a cool feature that's been around, but I never heard of it, seems a like an excellent feature
12:36 MyTeaGotCold: What is the DBA equivalent of automated unit testing?
13:24 thatkerolearlier: Hey Brent! I am currently studying Intelligence systems for my major such as machine learning, ann, cnn etc. can you give me an idea for my final year project ?
14:30 Froid: Is it ever ok to lead a non-clustered index with an inequality search column followed by an equality search column? If so, when?
15:41 Kang: For boxed SQL, should we clear SQL wait stats after raising the DB compat level to the latest level?
16:17 Piotr: Have you ever had to disable TSQL_SCALAR_UDF_INLINING for a database? If so, why?
16:56 Diana: Do you know of any gotcha's when running cross DB queries where DB1 and DB2 are in different compat levels?
17:30 Bill: Does the first responder kit follow the same end of life schedule as Microsoft for SQL Server versions?
18:34 Aditya: Are there any troubleshooting benefits to naming TSQL transactions as opposed to not naming them?
19:18 RushingSQL: Did anyone let you know that Bob Ward gave a class on Always On Availability Groups at Pass Data Community Summit 2023 and you were listed at the top of his reference slide?
20:28 Tom: Hello! I have a question before pruchasiong a prodcut. "SQL Server 2019 Standard - 15 clients" Whats does 15 clients means? I am not sure if this is related with CALS or the amount of database that I can conect it to.
21:18 BoboDBA: Hi B. Would partitioning a 300 million records (800gb) table monthly on a datetime column (with an aligned index on the column too) provide better query performance than a nonclustered index on that column? Stakeholder is demanding partitioning but he doesn’t have to maintain it.
22:28 thevibrantDBA: long winded question
23:20 Sigríður: Does the MemoryGrant property for a query plan include the memory used to read pages from disk into the buffer pool needed to service the query?
23:57 Here-I-Am: What's your opinion on CDC and its use in products like Goldengate for replication?
25:12 RenegadeLarsen: Starting to see in Europe that many customers are focusing on security. Do you see the same trend in the US?
27:24 Vinícius Lourenço: Hi from Brazil, as a non-database person, what is the basic maintenance tasks I should look/do on my Azure SQL Server DB? A few thousand inserts per month into 2 tables
28:05 Slow Cheetah: Query performance is good for a given query when forcing parallelism with trace flag 8649. It's bad without this query hint.
28:50 Ingeborg : Have the Iceland lava flows affected any friends / places you visited?
30:34 Chakra: What is your opinion of the KEEP PLAN query hint? Do you ever like to use it?
31:01 Håkan A: Hi Brent, We get a .bak file every night we have to import new data from. Do you know any common reasons for running restore database from disk with replace (in single user) getting stuck in (restoring...) very often?
31:52 Hera Syndulla: What is the best and worst SQL VM naming conventions you have seen?
33:25 Raghav: Do you like any third party software for SQL A.G. backup over native backup?
33:54 Q-Ent: Hi Brent, Can you suggest any article related to CPU Mathematics 101?
34:54 Richard Wilmuth: What is the best way to import databases from a Google Cloud VM SQL Server to an AZURE SQL Server (not on a VM) ?
36:04 BullRed: How did you enjoy the F1 weekend in Vegas?

[@malcomreynolds4103]

The 40Tb DB question was hilariously succinct for a topic that large. I like Brent's answer about the design of their database but a few things on the writer's statement :
1. Mongo does sharding. If your application is implemented with sharding on SQL server, good luck implementing that on mongo without an entire rewrite of the models - and then logic layer...at least if you want anything recent inserted, updated or deleted to ever query back accurately.
2. Mongo and most other noSQL databases DO NOT CACHE; instead the OS performs the caching and OS caching is entirely different from the access patterns of the database; The database will ask for more memory, if the server has it, will give it, if it doesn't have it, it will page it, then if it doesn't have swap space, it will arbitrarily discard pages. It still suffers from Java's extremely poor memory management. -> This point alone likely disqualifies Mongo as contender for a database of that scale; if a nosql database could benefit this implementation, the java memory problems will be a problem with mongo. If the application only pulls regularly tiny percentage of data, at best it is a wash between a relational DB and NoSQL DB - but probably slower on NoSQL.
3. Sharding vs partitioning is a nonsequitur. They only trivially overlap. If the writer thinks they need sharding, and they implement sharding via partitioning in SQL Server, I am skeptical they need either.
4. If its a key - pair database as Brent alluded to, then NoSQL makes sense - but do you require data integrity? If so you it is still a relational DB for you. If its not key pair and you don't require data integrity, your foot print could easily grow to petabytes - which may or may not be a problem in its raw cost, but it could be.
I agree on fictious characters for bad naming. It sounds cute on the first batch but then over time you start running out of deities. Had a client that had to switch from greek mythology to roman mythology, then celestials because obviously some of them are used in mythology, eventually all of those ran out and things really fell apart going into characters from beowulf. The best ones were when the server builder misunderstood what a deity did or had an odd interpretation. I have no idea how many times hermes, iris or apollo got reboot intending to be the file server, but was actually the exchange server. (Apollo pulls a cart so obviously maybe files or maybe emails are in the cart - I think he was an exchange server and hermes was a file server because he has a bag of documents)
Had one client who name all the servers normal things, but with metadata that had nothing to do with the server in question for 'security.' Its great when you reboot xx-HQ-FS01 because it is one of many redundant client access servers...but it was actually the CRM database server running a 2-day long batch for end of month billing, huge rollback lasting days and you actually meant to reboot xx-HQ-FS02

[@mickgarner5259]

30 years ago we spent a 7 figure sum £ on hardware to run SQL Server on NT, the SAs named the servers MrBurns and Maggie, cue embarrassment in management meetings.

[@BrentOzarUnlimited]

HAHAHA, that's awesome.

[@Kane0123]

Wow that first question…

Restart the server :) Ok at least I know it is not a brilliant way of shipping data which i suspected ... we will se if we can get a better way or a separate stage server. Thanks for bringing it up.