Understanding SQL Manged Instance behaviour in Planned Maintenance events

Question

Understanding SQL Manged Instance behaviour in Planned Maintenance events

Jim Swainston 20

Hi,
I have been reading this article (https://learn.microsoft.com/en-us/azure/azure-sql/database/planned-maintenance?view=azuresql) and would like to get some clarification on this paragraph....

During planned maintenance, members of the database quorum will go offline one at a time, with the intent that there is one responding primary replica. For Business Critical and Premium databases, at least one secondary replica will also be online to ensure no client downtime. When the primary replica needs to be brought offline, a reconfiguration process will occur. For Business Critical and Premium databases one of the secondary replicas will become the new primary replica. For General Purpose, Standard, and Basic databases the primary replica will move to another stateless compute node with sufficient free capacity.

Does this mean that for a SQL Managed Instance, I could choose a Service Tier of General Purpose with a Hardware Generation of Premium and experience no downtime in a planned maintenance window? See image below...

User's image

Ultimately I want the high availability, but don't need the added features (and cost) of Business Critical if possible.

thanks

GeethaThatipatri-MSFT 29,587 Reputation points Microsoft Employee Moderator

2023-08-22T14:53:41.1633333+00:00

Hi @Jim Swainston Welcome to Microsoft Q&A thanks for posting your question.

I Understand you are asking for clarification on the behavior of SQL Managed Instance during planned maintenance events. you want to know if you can choose a Service Tier of General Purpose with a Hardware Generation of Premium and experience no downtime in a planned maintenance window.

No, that is how general purpose works, the reality is there will still be a brief outage even with BC tier - if a transaction is in flight when you reboot the underlying server it will break the connection there is no way that takes an uncommitted transaction in memory and transfers it to the other node...

however, the document is a little confusing and will check with the internal team and provide you with more details.

Regards

Geetha
Jim Swainston 20 Reputation points

2023-08-23T10:13:58.15+00:00

Thanks @GeethaThatipatri-MSFT I am ok with a dropped connection, but I would want to be able to establish a new connection immediately. What I want to avoid is the average 8 seconds max 30 second outage mentioned below. Is Business Critical the only way to avoid this?

On average, 1.7 reconfigurations occur per planned maintenance event. Reconfigurations generally finish within 30 seconds. The average is eight seconds. If already connected, your application must reconnect to the new primary replica of your database. If a new connection is attempted while the database is undergoing a reconfiguration before the new primary replica is online, you get error 40613 (Database Unavailable): "Database '{databasename}' on server '{servername}' is not currently available. Please retry the connection later."

Answer accepted by question author

1 additional answer

Your answer

GeethaThatipatri-MSFT 29,587 Reputation points Microsoft Employee Moderator

2023-08-22T14:53:41.1633333+00:00

Hi @Jim Swainston Welcome to Microsoft Q&A thanks for posting your question.

I Understand you are asking for clarification on the behavior of SQL Managed Instance during planned maintenance events. you want to know if you can choose a Service Tier of General Purpose with a Hardware Generation of Premium and experience no downtime in a planned maintenance window.

No, that is how general purpose works, the reality is there will still be a brief outage even with BC tier - if a transaction is in flight when you reboot the underlying server it will break the connection there is no way that takes an uncommitted transaction in memory and transfers it to the other node...

however, the document is a little confusing and will check with the internal team and provide you with more details.

Regards

Geetha
Jim Swainston 20 Reputation points

2023-08-23T10:13:58.15+00:00

Thanks @GeethaThatipatri-MSFT I am ok with a dropped connection, but I would want to be able to establish a new connection immediately. What I want to avoid is the average 8 seconds max 30 second outage mentioned below. Is Business Critical the only way to avoid this?

On average, 1.7 reconfigurations occur per planned maintenance event. Reconfigurations generally finish within 30 seconds. The average is eight seconds. If already connected, your application must reconnect to the new primary replica of your database. If a new connection is attempted while the database is undergoing a reconfiguration before the new primary replica is online, you get error 40613 (Database Unavailable): "Database '{databasename}' on server '{servername}' is not currently available. Please retry the connection later."

Answer 1

GeethaThatipatri-MSFT 29,587 Microsoft Employee Moderator

@Jim Swainston

High availability is built-in capability in both service tiers – General Purpose and Business Critical. No matter for which service tier you decide to go, in both cases you will get almost no-downtime experience.

The impact of planned maintenance is failover that happens during patching. It typically takes a bit shorter in case of BC due to the always-on architecture, but in both cases it is measured in seconds.

Regards

Geetha

GeethaThatipatri-MSFT 29,587 Reputation points Microsoft Employee Moderator

2023-08-29T16:32:02.48+00:00

@Jim Swainston I am checking to see if you got a chance to look into the above response. Please let me know if you have any further questions.

Regards

Geetha

Answer 2

Hello Jim Swainston!

You're seeking clarity on SQL Managed Instance behavior during planned maintenance events, especially when using the General Purpose Service Tier with Premium Hardware Generation.

Here's what you need to know:

Even with Premium Hardware Generation, the General Purpose tier will experience a brief outage during maintenance. Transactions in progress during a server reboot will experience a dropped connection. Business Critical tier offers higher availability but can still have short disruptions. Reconfigurations typically take 8 seconds but can extend up to 30 seconds. If minimizing downtime is crucial, the Business Critical tier might be your best choice.

I hope this clears things up! If this information is helpful, please consider marking my response as such. Best of luck with your SQL Managed Instance decisions! 😊

Share via

Understanding SQL Manged Instance behaviour in Planned Maintenance events

1 additional answer

Your answer