RDS SQL Server upgrade downtime

Yes. Amazon RDS for SQL Server does rolling upgrades. You have an outage only for the duration of a failover.

For RDS SQL Server,

When upgrading a Multi-AZ instance, RDS performs the upgrade on the standby instance first. It then performs a failover and then upgrades the old primary.

• The downtime here is only during the failover process which would typically last for 60 to 120 seconds under ideal conditions. Please make sure that you have no active connections or transactions or any other database activity; this would increase database recovery time eventually causing a longer downtime.
• The failover mechanism automatically changes the DNS record of the DB instance to point to the upgraded standby DB instance. Kindly note, during the whole process, your RDS instance status will be in 'Modifying' but that doesn't mean that database is not accessible, because it will remain accessible by end user and downtime would only be during failover period.

Above process is applicable to both RDS SQL Server version and OS (windows) upgrades.

https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_UpgradeDBInstance.SQLServer.html
https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Concepts.MultiAZ.html#Concepts.MultiAZ.Failover