Change service levels or database performance and downtime

Question

I determined that we may need to scale to the next level of service as soon as (Standard to Premium).

For other interested parties, this article provides excellent guidelines for analyzing your SQL database.

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

Answer 1

There is no downtime when changing levels, I did this several times. The change is not immediate, although it takes at least 5 minutes, but during this time it will work as usual.

Answer 2

Depends on your definition of downtime. I have changed performance levels many times. Switching from standard to premium, we experienced many mistakes. Here are some examples:

System.Data.SqlClient.SqlException (0x80131904): The transport layer encountered an error while receiving results from the server. (Supplier: 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 host remote control.

System.Data.SqlClient.SqlException (0x80131904): DATABASE ALTER command is in process. Wait at least five minutes, enter the database "..." to complete the command. Some system directories may become outdated until the command completes. If you changed the database name, use the new database name for future activities.

System.Data.SqlClient.SqlException (0x80131904): the service has encountered an error processing your request. Please try again. error code 40174. A serious error occurred in the current command. results, if any, should be discarded.

System.Data.DataException: Unable to complete transaction. The underlying connection is not open or has not been initialized.

My advice is to change performance levels whenever possible after hours or during maintenance periods.

Answer 3

As stated above, it depends on your definition of downtime. There is a short period when the tier switches, when transactions can be thrown back.

From the "Scaling or Scaling ..." section of this page: https://docs.microsoft.com/en-us/azure/sql-database/sql-database-service-tiers

Note that changing the service level and / or database performance level creates a replica of the source database at a new performance level, and then switches the connections to the replica. During this process, data is not lost, but for a short moment when we switch to a replica, the database connections are disconnected, so some in-flight transactions may be thrown back. This window changes, but on average it is less than 4 seconds, and in more than 99% of cases - less than 30 seconds. Very rarely, especially if there are a large number of transactions in flight, the connections are currently disconnected, this window may be longer.

Since a “transaction in flight" usually refers to a transaction that occurs when the connection fails, it seems that either the connections can be split into an average transaction, or transactions running on multiple connections may fail and be discarded if one of them refuses to switching time. If the latter, then when switching simple transactions may not often occur. If the former, then loaded databases will almost certainly notice some impact.

Answer 4

There is no downtime when changing TIERS, but there is downtime when changing billing models. You literally have to create database backups, create new databases on new invoicing model servers and restore them. Then you need to change all the database links in the applications or on the websites. If you want to change levels from a pay level that is no longer supported, you must first transfer to a new billing model. We learned it with difficulty. Microsoft doesn't make it easy either - it's not a keypad operation.