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.

yourscales

This is the code.

CREATE TABLE dbo.duck
(
ID  INT
)

GO

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

WHILE(@first <= @last)
BEGIN
INSERT INTO dbo.duck VALUES(@first)
SET @first += 1
END

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.

rederror

It did commit some inserts just until the disconnect occurred.

4159

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.

thecode

 

About a minute later the same error.

samerror

BUT what has happened to my inserts?

oinserts

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:

WordPress.com Logo

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

Google photo

You are commenting using your Google 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