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