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.

 

 

One thought on “Azure SQL Database – Scaling up

  1. Pingback: Scale-Up In Azure SQL Database – Curated SQL

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s