SQL Server on Amazon Web Services: 2014 in review

I have a particular interest in the combination of Microsoft SQL Server and the cloud services of Amazon Web Services, for the following reasons:

  • For almost all web applications (and for all SaaS applications in particular) the database system is by far the most business-critical component, where absolutely nothing should go wrong, both in performance and reliability.
  • Microsoft SQL Server has always been an incredibly robust and user-friendly relational database system, and of course a no-brainer if you work within the Microsoft ecosystem. Its (unavoidable) move to core-based licensing has driven up licensing costs (and lost it some friends), but especially the licensing costs of SQL Server Web Edition are still very low (the Enterprise edition is far too expensive, though, keeping it and its features out of reach of many companies).
  • Amazon Web Services (AWS) dwarves all other cloud-service providers, and is in most cases the default choice, even when using Microsoft products. 2014 has seen a very rapid pace of innovation from Amazon. The only time that AWS hasn’t been the leader was when in march 2014 Google drastically reduced the prices for its cloud storage (to $0,026/GB/month). Only a day later AWS followed with a similar price reduction of around 60% for cloud storage.

So, in review, some of the important features and innovations of AWS in 2014, relating to SQL Server:

  1. Cloud storage price reduction (march): The prices of Amazon S3 have been reduced by around 60% (to $0,03/GB/month). This makes it even easier to store a very generous amount of SQL Server backups and snapshots in S3. It will still be a negligible amount on your monthly bill from AWS. My backup strategy is now to to make EBS snapshots of the primary SQL Server data disks every 10 minutes, make transaction-log backups every 15 minutes and snapshot the disks with the log-backups, and make nightly full SQL Server backups, and store the full backups in S3 separately.
  2. New memory-optimized instances (april): The R3 instance types use new Intel Ivy Bridge processors, and go up to 32 cores and 244 GB RAM. These instances should now be the default choice when setting up your own SQL Server instance. Some important issues, though, with these instances:
    • The R3 instances have no direct competition from Google, so they have not been included in any price reductions and are not very cheap. r3.2xlarge, with 8 cores and 61GB RAM and SQL Server Web Edition is $1.555 per hour.
    • SQL Server Web Edition (the best choice for starting web companies) can only use 64GB of RAM, so any instance type bigger than r3.2xlarge is wasted.
  3. Multi-Availability zone (mirroring) support for SQL Server in Amazon’s Relational Database Service (RDS) (may): Although I’m personally moving away from using Amazon RDS  (Amazon’s RDMS PaaS solution), mainly because its lack of backup options on the database level, and its lack of storage scalability, RDS is still a great way to start with Amazon’s cloud services. This Multi-AZ support is based on SQL Server database mirroring, which brings a few issues:
    • SQL Server mirroring is not supported for the SQL Server Web Edition, so no Multi-AZ if you use this version of SQL Server.
    • Microsoft seems to be moving away from mirroring as a disaster recovery method, towards AlwasOn availability groups.
  4. SSD-backed Elastic Block Storage (june): Previously only the instance storage (that is lost on stopping and starting an instance) could be an SSD. Now all virtual drives can (and should be) SSD storage. This is a huge gain for SQL Server, which benefits tremendously from the increased seek time of SSD. When using SSD drives on Amazon I don’t even bother anymore with reserved IOPS, because a SSD of 1 TB (currently the maximum size) already gives you 3000 IO per second.
  5. New Amazon region in Frankfurt (october)Until now, Ireland was the only region (a set of datacenters) of Amazon within the EU. With this second region, it has become an option to spread out your SQL Server instances, backups, and snapshots over multiple regions, while still keeping all data within the EU.
  6. Private DNS (november). You can now give your SQL Server instance easy-to-use network names.
  7. New payment options for reserved instances (december)In most cloud solutions, the database servers are by far the most expensive instances. This also makes the upfront costs for reserving these instances a big hurdle, even if the costs over the full term are lower. There are now new payment options for reserved instances without upfront payments, where you still get a 30% price reduction if you reserve the instance for a year.

Announced, but no yet available:

  • EBS volumes larger than 1TB. I have always been skeptical of software-based RAID-ing of virtual drives in a cloud environment, so it’s great to see that EBS volumes of up to 16TB have been announced, making life a lot easier for administrators of large SQL Server databases.

Curious about:

  • Amazon Aurora – new “cloud-optimized” relational database engine. I’m always a bit skeptical when cloud-companies feel the need to build their own “cloud-optimized” relational database engine. As we have seen with Azure SQL, companies are reluctant or unable to re-architect their applications to effectively use these database engines, and also really need the full feature set of the full database product (such as database backups, point-in-time restores, etc.). But we’ll see how this turns out.

Wishlist for 2015:

  • SQL Server Enterprise as “license-included”. Currently, Amazon does not offer server instances that includes SQL Server Enterprise. You have to buy your own licenses, that need to include Microsoft Software Assurance, and somehow certify these licenses to use them in the cloud. The upfront cost and hassle of this is simply too great. Microsoft Azure offers instances with SQL Server Enterprise included, so is there a reason why Amazon can’t or is not allowed to?
  • Faster EBS snapshots. I really like the snapshot mechanism of EBS, but at the moment they often take such a long time, that in practice you can take them (at best) only every 15 minutes (I would prefer every 5 minutes), making them less than ideal as a disaster recovery solution.
  • Make RDS SQL Server more useful. Currently, RDS SQL Server has some limitations that make it impractical for quite a few use cases:
    1. Make storage scalable. Currently the storage used by a SQL Server RDS instance can not be increased.
    2. Make database-level backups available.
    3. Lift the limit of 30 databases.
  • Improve the AWS console UI. This is a common complaint from almost all AWS users: The web-based console UI is still very primitive (especially for such crucial cases as IAM security), and it’s always a guess which feature works in which browser.

All in all, it’s been a pretty good year for all of us running SQL Server in AWS, with the general availability of SSD-drives as the highlight. Happy 2015 everyone!

SQL Server Blog

Thoughts about SaaS software architecture

Brent Ozar Unlimited®

Thoughts about SaaS software architecture

Visual Studio Blog

Thoughts about SaaS software architecture

Microsoft Azure Blog

Thoughts about SaaS software architecture

AWS News Blog

Thoughts about SaaS software architecture

%d bloggers like this: