SQL Azure Throttling & Error Codes (Year of Azure–Week 18)

Ok, last week was weak. Hopefully my grammar is correct. So I want to make it up too you by giving you some meat this week. I’ve been involved lately in several discussions regarding SQL Azure capacity limits (performance, not storage) and throttling. Admittedly, this little topic, often glanced over, has bitten the butts of many an Windows Azure project.

So lets look at it a little more closely. Shall we?

Types of Throttling

Now if you go read that link I posted last week on SQL Azure Error Messages, you’ll see that there are two types of throttling that can occur:

Soft Throttling – “kick in when machine resources such as, CPU, IO, storage, and worker threads exceed predefined thresholds”

Hard Throttling – “happens when the machine is out of a given resource”

Now SQL Azure is a multi-tenant system. This means that we have multiple tenants (databases) that will sit on the same physical server. There cold be several hundred databases that share the same hardware. Now SQL Azure will use soft throttling to try and make sure that all these tenants get a minimum number of resources. When a tenant starts pushing those limits, soft throttling will kick in and the SQL Azure fabric will try to move tenants around to rebalance the load.

Hard throttling means no new connections. You’ve maxed things out (storage space, worker process, cpu) and drastic steps should be taken to free those resources up.

SQL Azure Resources

Also in that article, we find the various resource types that we could get throttled on:

  • Physical Database Space
  • Physical Log Space
  • LogWriteIODelay
  • DataReadIODelay
  • CPU
  • Database Size
  • Internal
  • SQL Worker Threads
  • Internal
    Now when an error message is returned, you’ll have throttling types for one or more of these resources. The type could be “no throttling”, could be “soft”, or it could be “hard”.

Throttling Modes

Now if all this wasn’t confusing enough, we have three types of throttling. Well technically four if you count “no throttling”.

Update/Insert –  can’t insert/update, or create anything. But can still drop tables, delete rows, truncate tables, read rows.

All Writes – all you can do is read. You can’t even drop/truncate tables.

Reject All – you can’t do anything except contact Windows Azure Support for help.

Now unlike the throttling type, there is only one mode returned by the reason.

But what can we do to stop the errors?

Now honestly, I’d love to explain Andrew’s code to you for deciphering the codes. But I was never good with bitwise operations. So instead, I’ll just share the code and along with a sample usage. I’ll leave it to folks that are better equipped to explain exactly how it works.

The real question is can we help control throttling? Well if you spend enough time iterating through differing loads on your SQL Azure database, you’ll be able to really understand your limits and gain a certain degree of predictability. But the sad thing is that as long as SQL Azure remains a shared multi-tenant environment, there will always be situations where you get throttled. However, those instances should be a wee bit isolated and controllable via some re-try logic.

SQL Azure is a great solution, but you can’t assume the dedicated resources you have with on-premises SQL Server solution. You need to account for variations and make sure your application is robust enough to handle intermittent failures. But now we’re starting down a path of trying to design to exceed SLA’s. And that’s a topic for another day. Winking smile

SQL Azure Error Codes (Year of Azure–Week 17)

Ok, I’m no counting last week as a ‘Year of Azure’ post. I could,but I feel it was even too much of a softball for me to bare. Unfortunately, I was even less productive this week in getting a new post out. I started a new client and the first weeks, especially when travelling are horrible for me doing anything except going back to the hotel and sleeping.

However, I have spent time the last few week working over a most difficult question. The challenges of SQL Azure throttling behaviors and error reporting.

Reference Materials

Now, on the surface SQL Azure is a perfect wonderful relational database solution. However, when you begin subjecting it to a significant load, its limitations start becoming apparent. And when this happens, you’ll find you get back various error codes that you have to decode.

Now, I could dive into an hours long discussion regarding architectural approaches for creating scalable SQL Azure data stores. A discussion mind you which would be completely enjoyable, very thought provoking, and for which I’m less well equipped then many folks (databases just aren’t my key focus, I leave those to better…. er…. more interested people *grin*). For a nice video on this, be sure to check out the TechEd 2011 video on the subject

Anyways…

Deciphering the code

So if you read the link on error codes, you’ll find that there’s several steps that need to be decoded. Fortunately for me. While I have been fairly busy, I have access to a resource that wasn’t. One fairly brilliant Andrew Espenes. Now Andrew was kind enough to take on a task for me and look at deciphering the code. And in a show of skill that demonstrates to me I’m becoming far older then I would like to believe,

Anyways, pulled together some code that I wanted to share. Some code that leverages a technique I haven’t used since my college days of developing basic assembly (BAL) code. Yes, I am that old.

So lets fast forward down the extensive link I gave you earlier to the “Decoding Reason Codes” section. And our first stop will actually be adjust the reason code into something usable.  The MSDN article says to apply modulo 4 to the reason code:

ThrottlingMode = (AzureReasonDecoder.ThrottlingMode)(codeValue % 4);

Next determine the resource type (data space, CPU, Worker Threads, etc…):

int resourceCode = (codeValue / 256);

And finally, we’ll want to know the throttling type (hard vs. soft):

int adjustedResourceCode = resourceCode & 0x30000;
adjustedResourceCode = adjustedResourceCode >> 4;
adjustedResourceCode = adjustedResourceCode | resourceCode;
resourceCode = adjustedResourceCode & 0xFFFF;
ResourcesThrottled = (ResourceThrottled)resourceCode;

Next Time

Now I warned you that I was short on time, and while I have some items I’m working on for future updates I do want to spend some time this weekend with family. So I need to hold some of this until next week when I’ll post a class Andrew created for using these values and some samples for leveraging them.

Until next time!

Introduction to SQL Azure

Yet another simple attempt to document my continuing adventures with the Windows Azure Platform. In today’s edition, our hero attempts to host a simple on-premise database to SQL Azure and access it from an on-premise application.

Create our SQL Azure Server/Database

I’ve covered getting Windows Azure platform benefits in another post. So we’ll presume you have already either purchased a subscription for SQL Azure or claimed any benefits you’re entitled too. Now my MSDN Premium subscription benefits include 3, 1gb instances of SQL Server. Today I’m going to setup my SQL Azure server and create one of those instances.

I start by pointing my favorite browser to https://sql.azure.com and signing in with the Live-ID that is associated with my subscription. Once signed in, you should be at the SQL Azure Developer Portal’s landing page (as seen below) with a list of “projects” (aka subscriptions) we have associated with my Live-ID displayed.

clip_image002

You’ll click on a project project and if prompted, accept the terms of service. Since this is my first time logging into my SQL Azure subscription, I need to start by creating a SA (system admin) account and specifying a region for my database. The region selection here is important for reasons I’ll get to later in this article.

clip_image004

So fill in the form, select a location (aka datacenter), and click on “Create Server”. After a few seconds, our server has been partitioned and we have a master database already created within it. The only thing that was lacking here is that I really would have liked to been able to try and provide a unique name for my server. Oh well. :)

clip_image006

At this point we need to go to the “Firewall Settings” tab and adjust the security settings for our database. If we don’t, we won’t be able to connect to it. These settings will apply to all database instances within our SQL Azure Server. For now, I’m going to set it to allow for any IP to connect. This is not something I would generally recommend. It would be a good practice to add settings into this to only allow connections from our application, network submask, or maybe even only from Microsoft hosted services. But I’m feeling a touch lazy today.

Once we’ve given our firewall settings a few minutes to be applied, select the “master” database instance and test connectivity. Once verified, we’re ready to get connected.

Connecting SQL Azure

There are already many blog posts available that explain how to get SQL Server 2008 Management Studio to connect to SQL Azure. However, if you tag the SQL Server Management Studio 2008 R2 CTP you’ll find this less problematic. Using this version, it’s as simple as putting in your server name (available from the portal and in the form of <somevalue>.database.windows.net) into the login box along with your Administrator username and password.

If you can’t use the R2 CTP, you can find alternatives to get SQL Server 2008 Management Studio working in various blog articles.

Once that connection is established, I can run a couple exported SQL Scripts I already have to create my database. I generated these scripts from a local SQL database. In my case, the scripts required only two minor changes before I got them to run successfully.

Connecting to it from the Visual Studio 2010 RC’s Server Explorer is just as simple. Same goes for having your applications connect, you just use a connection string and you’re in. The only thing all these methods have in common is that you need to have port 1433 open for outbound traffic and the IP address you are connecting from has to be allowed via the SQL Azure firewall settings.

I started by creating a new windows forms project and adding a data source to it. I could have set this using a connection string I generated from the portal or via any preconfigured data connections. I then added that data source to my form and launched the project. Simple as pie! Without writing a single line of code I could connect to and update my SQL Azure database from an on-premise application.

Why was that region selection important?

I said I’d get back to this and I have.

The reason that the region selection is so important is because you don’t get charged for bandwidth within a datacenter (region). Additionally, by locating your database within the same center as your application, you reduce any connection latency. Yeah, we could host the DB in Asia and run the app in the US, but that’s only going to slow down performance AND cost us more. So there’s no reason we should do it. This is also why you may want to run a local copy of the database when doing development. No sense paying bandwidth costs for accessing a hosted server when a local copy of SQL Express will do the job nicely.

But wait, what about the infamous “smoking hole” disaster recover scenario? Does picking another region affect my disaster recovery plan? The short answer is “no”. People with a greater insight into how SQL Azure is built tell us that there are 3 copies of your database are automatically created and maintained and that at least one of those should be geographically diversified. If your database crashed, or the data center itself is destroyed by a giant radiation mutated lizard, a backup copy will be activated and the logs applied.

In fact, you may not even notice if a simple database crash takes place. We’ll leave the question of “but don’t I need to know” for another day.

Some closing thoughts

In going through this exercise, there are a couple things that become apparent to me. First off, there are going to be some that will detract SQL Azure as being too “dumbed down”. I’m admittedly not a DB guy. I know enough to stay away from bad practices, create the stored procedures/functions I need to do my job, and can tell the difference between a physical and logical database schema. Hell, I’ve even been known to help debug a query performance issue on rare occasion. However, I am by no means someone that enjoys spending their time optimizing databases and monitoring their performance. As such, SQL Azure does a good job of meeting my basic needs. In its current state, it may not be an enterprise level solution. But you, I think it makes a pretty decent operational data store that would serve a simple application pretty well.

For those that are really into RDBMS systems and love tweaking and tuning them like a hot-dog prepping for the quarter mile, they are likely to be disappointed by SQL Azure. However, I’m confident that the SQL Azure team is committed to this product. We’ve already seen the 1.1 version released with changes based directly on feedback they’re received from the community. I also believe that the current size limits are based more on ensuring they have a stable service offering then on any real limitation. It wouldn’t surprise me if we see SSRS and +25gb instances available before the end of the year. And I don’t think its too far fetched to predict that +1tg instances will happen eventually.

I’m also getting more of an idea of what MSFT may be thinking when it comes to the online subscriptions. This is admittedly a fairly new area for them (compared to license based software distribution) and as such is subject to change, but it really strikes me that they are trying to encourage folks to tie given applications to a specific subscription rather than have multiple projects within a single subscriber account. It could go either way, but for enterprises that will be interested in billing charges back, it makes sense to have a subscription for each “project” for simplicity.

I guess only the future will really tell the tale.

Follow

Get every new post delivered to your Inbox.

Join 1,147 other followers