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?
Depends on your definition of "downtime". I have changed performance levels many times. Going from standard to premium we experienced many errors. Here are a few samples:
System.Data.SqlClient.SqlException (0x80131904): A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.) ---> System.ComponentModel.Win32Exception (0x80004005): An existing connection was forcibly closed by the remote host.
System.Data.SqlClient.SqlException (0x80131904): The ALTER DATABASE command is in process. Please wait at least five minutes before logging into database '...', in order for the command to complete. Some system catalogs may be out of date until the command completes. If you have altered the database name, use the NEW database name for future activity.
System.Data.SqlClient.SqlException (0x80131904): The service has encountered an error processing your request. Please try again. Error code 40174. A severe error occurred on the current command. The results, if any, should be discarded.
System.Data.DataException: Unable to commit the transaction. The underlying connection is not open or has not been initialized.
My advice is to change performance levels off hours or during maintenance periods if possible.
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.
There is no downtime when changing TIERS but there IS downtime when changing billing models. You literally have to backup your databases, spin up new databases in the new billing model servers, and restore them. You then have to change all your database references in apps or websites. If you want to change tiers FROM a billing tier that is no longer supported you WILL need to migrate to the new billing model first. We learned this the hard way. Microsoft doesn't make it easy either - it's not a pushbutton operation.
