Scaling up and down your SQL Database is something that is quite common to do. I want to discuss the impact of moving up and down tiers, in terms of your transactions and connections.

We should all be aware that when changing the service tier and/or performance level of a database it creates a replica of the original database at the new performance level and then switches connections over to the replica. No data is lost during this process but during the brief moment when we switch over to the replica, connections to the database are disabled, so some transactions in flight may be rolled back. So it probably best that you do this during a period of low activity.

Now, I will show you this via an example. First, I create a database that is S1 tier.

With that now created I logon via SSMS (SQL Server Management Studio)

I created this database from a sample backup (renamed SheepDB ).

What I am going to do is simulate some queries (obviously trivialised) and leave it “running” then upgrade the edition to see what happens.

While I have a basic (but a high duration) query running, I then upgrade the tier to S2.

After about 60 seconds I get the following message (Ignore the local join warning hint – that’s me messing about, focus on the red):

It disconnected my connection. As soon as the scale request finishes you will be able to resume the query. Something to be aware of.

Filed under: Azure, Azure SQL DB Tagged: Azure, Azure Portal, Scaling, SQL database, SSMS, Technology