Azure SQL Database and Error Handling

For some reason I have friends / colleagues telling me that when scaling (up and down for this example) that no downtime occurs. Well, not only does Microsoft documentation say differently, I will show it. So let’s test it out. Before the practical test, this is the official stance. “There is a switch over period where connectivity is lost to the database for a short amount of time, which can be mitigated using retry logic”.

The key part here? Re-try logic. If not, then expect disconnects so you really want to do this during a quiet window. Or at the very minimum something to handle the transaction.
I initiate a scaled request from S0 to S1. I do this whilst some code is running.


This is the code.



DECLARE @first AS INT = 1
DECLARE @last AS INT = 100000000

WHILE(@first <= @last)
SET @first += 1

This is the result –

Msg 40197, Level 20, State 4, Line 16 The service has encountered an error processing your request. Please try again. Error code 9001.


It did commit some inserts just until the disconnect occurred.


Ideally, I would want an all or nothing strategy. So, let’s be more defensive. I run the below whilst another scale up occurs. So if a disconnect occurs I want NOTHING to be inserted.

Note: The code is below with the error handling. It is a screen shot, for some weird annoying reason this editor did not like the block of code.



About a minute later the same error.


BUT what has happened to my inserts?


Nothing, it did not commit anything.


1 thought on “Azure SQL Database and Error Handling

  1. Pingback: Dew Drop – February 12, 2020 (#3132) | Morning Dew

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s