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

Increasing and decreasing the performance level of your Azure SQL Database

One of the big benefits of Azure, and in fact any of the cloud offerings I’ve seen, is the ability to increase and decrease your capacity as needed. This is easy enough with Azure SQL Databases.

  1. In the Overview blade you can see current pricing tier.
  2. Under settings, just below the quick start link is the link for the pricing tier blade.
  3. Select the service tier (if it’s changing).
  4. Select the number of DTU using the slider bar.
  5. Select the max database size using the slider bar.
  6. You can now see the monthly cost
  7. Hit apply.
  8. Wait for a bit and the settings will change.

 
Simple enough right?

Well, there are two important things to remember. The first is somewhat obvious (at least in hindsight). If you try to decrease the capacity for a database and the database is too large for the new tier then the change will fail. This will happen even if you just increased the pricing tier minutes earlier.

Here is a concrete example:

  1. You have a SQL Database on a standard tier that is currently 230gb. You expect a surge of business because of a product release. Your application is going to need more database processing power.
  2. You increase it temporarily to the premium tier.
  3. While the database is under premium it grows by 30gb due to the new business. It’s now a 260gb database.
  4. At this point it is too big to be decreased back to a standard tier and you will either have to delete at least 10gb of data and shrink the DB or leave it at the premium tier.

 

Like I said, it makes sense when you think about it. Just make sure you know the size of your database before making any changes.

Here is the bit that you won’t see coming. In order to change the pricing tier the database is copied from one location to another and during the switch over the connections are disabled, causing any transactions in flight to be rolled back. This is mentioned in the documentation for SQL Database Service Tiers.

Changing the service tier and/or performance level of a database 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. This window varies but is on average under 4 seconds, and in more than 99% of cases is less than 30 seconds. If there are large numbers of transactions in flight at the moment connections are disabled, this window may be longer.

The important thing here is, if you plan on changing your service/pricing tier, make sure that your application can handle it.

In my opinion, this is a truly amazing feature of the cloud. The ability to scale your power needs up and down as needed could save a business vast amounts of money or keep a small business in, well, business.


Filed under: Cloud, Microsoft SQL Server, Performance, SQL Database, SQLServerPedia Syndication Tagged: Azure, microsoft sql server, Performance, SQL Database

SQLStudies

My name is Kenneth Fisher and I am Senior DBA for a large (multi-national) insurance company. I have been working with databases for over 20 years starting with Clarion and Foxpro. I’ve been working with SQL Server for 12 years but have only really started “studying” the subject for the last 3. I don’t have any real "specialities" but I enjoy trouble shooting and teaching. Thus far I’ve earned by MCITP Database Administrator 2008, MCTS Database Administrator 2005, and MCTS Database Developer 2008. I’m currently studying for my MCITP Database Developer 2008 and should start in on the 2012 exams next year. My blog is at www.sqlstudies.com.

Comments

Leave a comment on the original post [sqlstudies.com, opens in a new window]

Loading comments...