I'd say one of the only options is a HPE Superdome Flex machine but as you said they might run into other bottlenecks at this scale.
I can't fathom what a database is doing on the CPU so much, usually I run out of I/O (both disk and network) on 128 core machines before maxing the CPUs. Also the post says they have 4 machines and 10 databases, very strange.
If the 10 databases are independent, that seems like the easy way out --- siphon them off into separate clusters, and you should get some headroom; but if one database is 99% of the load, it won't be much.
Otherwise, you've got to find better hardware or partition the database somehow. The good news is, I don't think Azure has a 416 core server, but they do have 416 vCPU servers[1], at 2vCPU per core, 208 cores is a lot, but a) these are Skylake cores and b) you can get a similar core count in a dual socket Epyc board these days, and have a core that's much newer. Not sure if you can get one of those in a cloud though.
Edit to add: there's also a lot of potential to move compute out of the database. Without knowing anything about their queries, my experience has been the most expensive queries are either unnecessary table scans (which can often be fixed) or joins. For joins, sometimes you can fix those to run better, and sometimes it's better to do a 'client assisted join', first do an indexed query to get the ids of things you want, then do a big union of queries to get the details. You can tell me how disgusting that is, but it can turn a thing that takes one round trip and hard processing on the server into a thing that takes two round trips and is pretty easy for the server. Maybe SQL server is better at joins that MySQL though? Sometimes it might not be ok for data integrity/transactional reasons, but usually it's ok. Joining might be hard on the clients, too, but it's usually easier to add more database clients than to scale the database server.
[1] https://learn.microsoft.com/en-us/azure/virtual-machines/mv2...
It might run on ARM. IIRC, Ampere has some large ones with lots of memory bandwidth. Maybe CXL memory can also help mitigating any disk IO.
HPE has single-image machines that can have up to 16 4th gen Xeons, which gives a top limit of 960 cores. IBM has POWER10 boxes that go up to 240 cores (but they are POWER 10 cores that can do, IIRC, up to 8 threads per core (increasing cache misses, but reducing unused execution units).