Tuesday, August 12, 2014

Why I will be ditching SQL Azure in April 2015

UPDATE:  See the end of this post for more information  

As some may be aware, Microsoft is switching its SQL Database pricing model over to a new set of tiers in April 2015.  Currently the tiers are 'Web' and 'Business'.  The new tiers are 'Basic', 'Standard', and 'Premium'.

Recently, I decided to go ahead and switch over to the new pricing tiers, and get ahead of the game.  I switched the 1.5GB database behind my website from 'Web' to 'Basic' over the weekend when there was a pretty light load.  My thought was that if that was enough for my website, I would be fine, and if it had problems, I could quickly switch it over to Standard S1.  Both tiers seem to be reasonably priced at $5/month and $20/month respectively for my size of database.  I pay $20+/month for the Web tier right now anyway. (That's not what the pricing calculators tell you, but that doesn't take into account daily backups. Don't get me started.)

A Little Background
My website is fairly small, it has about 300 logins per day.  Peak usage is probably around 20 people simultaneously logged in.  A small website by any measure. A few months ago, I ran IIS, SQL Server Express, and MS Reporting Services all on a single virtual machine with a single core allocated to it and 1.7GB of RAM.  Even on that small of a machine the website screamed!!

Back to the Present Day
I switched over to the Basic pricing tier of SQL Azure, and the website seemed to run OK over the weekend. But Monday morning hit, and after a handful of users got on, the website went to its knees!  Users couldn't log in, others were reporting multiple errors.   So I immediately switched over to the Standard S1 tier.   The website came back online, but reports from multiple users during the day made it clear that performance had dropped significantly!   The website limped along for the rest of the day, but this was no solution.

That left me with a couple options. Switch to the Standard S2 tier, and hope that the $100/month I was going to pay was enough to run my website.  Or go back to the 'Web' version and hope that Microsoft gets their act together before April 2015.

The Bottom Line
SQL Azure Standard S1 is way underpowered.  Much less than what a single core machine will give you in performance.  Standard S2 is way overpriced, and may still not be enough horsepower to run a small website.   I can't pass on another $100/month in hosting costs to my client with the explanation that "Microsoft is forcing us to pay it to continue the same level of service."  And I'm not going to eat that extra cost.    So in April, when the 'Web' version expires, I will be moving my database back onto a VM with SQL Server Express.  It sucks that I am going to have to pick up all this extra maintenance, but it looks like I won't have many other options.

UPDATE - 9/15/2014: 
I have decided, I can't wait until the April deadline, and have moved my data out of SQL Azure.  I contacted some people on the SQL Azure team (via Scott Gu), and to their credit, they are very responsive. But in the end, I can't say I'm satisfied with their answers.  The first thing they pointed out to me was that the Web database tier doesn't have a guaranteed level of service like their new offerings do.  That means that if you happen to be sharing a CPU with some other apps, and they suddenly hit their database hard, you are going to pay the price. Moving to guaranteed performance levels is definitely what you want for a mission-critical application.

But my original gripe still remains, that SQL Azure is WAY underpowered for what they are charging, even for the lower cost levels they announced in September 2014.  I had a chance to do some comparisons before I unplugged SQL Azure completely.   I have a fairly intensive stored proc that I run at night.  This is what I found when running that proc:

SQL Azure Standard S2:  Stored proc took 2 minutes 49 seconds to execute

SQL Server Running on A1 VM (1.75GB RAM, 1 core CPU):   32 seconds to execute

That's right,  the S2 tier is over 5 times slower than a VM with a single core.  I realize that my results are not scientific, but I tried to level the playing field between the two as much as I could.  Doing the 'DTU math' between the tiers, it looks like you will need the P2 level of performance just to get the equivalent of a single-core processor devoted to your database.  That's almost $1000/month just in SQL Server charges.  Does that sound unreasonable to anyone else?    The downside is that if your database takes more than 10GB, you will have to pony up for a SQL Server license (move off the Express Edition), but even then it would only take a month or two to break even.

The New Bottom Line
If you have a small application, assume you will need the P2 or P3 level of performance if you are going to use SQL Azure (everything below that, will throttle your performance under even the smallest loads).   If you have a medium size application (100+ simultaneous users), forget about SQL Azure! You won't ever get enough horsepower out of it.