With a standard SQL Server platform (with no replication or redundancy), some downtime is to be expected for weekly/monthly patching and reboots.
Is this still the case for Azure DBs (elastic pools, serverless, managed instances, etc) ?
Or can I expect them to be operational almost 100% of the time except for unexpected disasters?
Microsoft publishes SLA's for all services, the Azure SQL ones can be found here. In summary, the SLA ranges between 99.99% and 99.995% depending on which tier of database you are using.