SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Azure SQL Database – Scaling up

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.

s1db

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

AzureSSMS

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

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.

UPGRADE

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

LOCALERROR

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

All About SQL

I am a Senior DBA with interest in MS technology especially SQL Server and Azure. During 2015 I was mentored by Paul Randal – Data Platform (SQL Server) MVP and during 2016 I completed my SQLskills Immersion training on Internals and Performance Tuning. When I am not working I am in the gym burning calories.

Comments

Leave a comment on the original post [blobeater.blog, opens in a new window]

Loading comments...