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.

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.

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.