Changing service tiers or performance level and database downtime

Question

I have identified that we may need to scale into the next service tier as some point soon (Standard to Premium).

For others interested, this article provides great guidelines for analysing your SQL Database.

My question: Is there any downtime while scaling to a different service tier or performance level?

Answer 1

As above, it depends on your definition of downtime. There is a brief period as the tier switches when transactions may be rolled back.

From 'Scaling up or scaling down...' section of this page: https://docs.microsoft.com/en-us/azure/sql-database/sql-database-service-tiers

Note that changing the service tier and/or performance level of a database creates a replica of the original database at the new performance level, and then switches connections over to the replica. No data is lost during this process but during the brief moment when we switch over to the replica, connections to the database are disabled, so some transactions in flight may be rolled back. This window varies, but is on average under 4 seconds, and in more than 99% of cases is less than 30 seconds. Very infrequently, especially if there are large numbers of transactions in flight at the moment connections are disabled, this window may be longer.

Since "in-flight transaction" usually refers to a transaction that is running when a connection is broken, it seems that either connections may be broken mid-transaction, or, transactions operating across multiple connections might fail and be rolled back if one the connections is denied during the switch. If the latter, then simple transactions may not often be affected during the switch. If the former, then busy databases will almost certain see some impact.