Scaling SQL Server on Amazon Web Services

Question

I currently have a web application that I would like to scale out using Amazon Web Services.

My web application specs:

C# ASP.NET SQL Server

I have a file storage for my users and have a SQL Server database for all my data. I want to scale on Amazon Web Services, using S3. I am not sure how to scale with SQL Server on Amazon Web Services? The Amazon Relational Database Service doesn't look like its for SQL Server. Would I just run it on a windows instance? How would I scale.

Answer 1

There are two different elements in your application. There is the application server, which is the one holding the ASP .Net application and there is the database server.

Web application servers are stateless in nature so scaling out the application server is going to be very easy. Simply deploy the application on multiple instances and then put a load balancer in front of them.

For the database server things are quite more complicated. Relational databases, because of their transactional and relational nature, are very hard to scale out and that will usually involve writing custom code at application level just to support the scale out scheme. This is actually an important aspect, because scalability won't simply be achieved transparently by adding more hardware (more machines) but you will also have to invest in coding. Probably two of the most popular ways of scaling out with Sql Server is by mirroring and by partitioning. Scaling out when Sql Server is deployed inside a cloud is not helped in any way by the cloud itself, it will still be base on the built in options.

Besides the complexity it involves, up to a certain level, on non-free DB solutions, horizontal scaling might turn out to be much more expensive than the vertical one. Consider the following scenarios for example:

1 x 10000$ machine + 1 x 8000$ Sql Server professional license = 18000$

10 x 1000$ machines + 10 x 8000$ Sql Server professional licenses = 90000$

Because it requires only one Sql Server license, an expensive machine will be much cheaper in the end than using 10 cheap machines with 10 licenses.

And now that I've come to the point of scaling up in cloud things complicate a bit. What cloud providers give you are essentially virtual machines. Virtual machines are quite good on partitioning CPU and RAM memory and if necessary you can add more power to your machine by adding more CPU power and more memory. That is fine, but databases are highly dependent on the I/O (hard disk) speed and that is one area where virtual machines are not great.

Related to the Amazon cloud offer and Sql Server I can recommend this very good article (I actually got to it by following the link supplied by @marc_s). In order to achieve good I/O speed on scaling up scenarios, the author is using EBS volumes. The problem with this approach is that the EBS volumes are not hard disks inside the server where you have deployed the DB, but rather storage volumes accessible over the network and that reduces their efficiency by quite a lot.

If you are not tied to Amazon, then you might try cloud providers that also offer physical machines (dedicated servers) in their infrastructure. As far as I know gogrid are doing that, but there might be others as well. The idea would be to deploy the database on a dedicated server (good I/O performance) and the application servers on virtual machines (which will be horizontally scalable).

Microsoft also has a cloud offer, which is called Windows Azure and it might be one of the most interesting at the moment, but in my opinion it is being let down by their relational database offering. They have a dedicated service for that, called Sql Azure. One of its main advantages is that it is easier to administer than a normal database, but on the downside it is quite limited to scaling. The only scaling option is in the database size and that only goes up to 50Gb. They don't mention anything about the performance that DB is delivering, nor can you do any configurations related to that. Chances are that the performance are lower than those of a cheap dedicated server (in fact there are posts on StackOverflow with people complaining on the Sql Azure speed compared to physical machines). You can scale out using sharding, but as mentioned above, this does impact and limit the DB structure and the way it used in code.

In conclusion:

• If you are building a business application with a very big demand on relational data, then cloud providers might not be your best option. You might consider using your own hardware or maybe providers that give you the option of using dedicated servers.
• You might consider building your application using a nosql database solution. These are much more scalable but on the downside they have some feature limitations (no transactions, no joins, limited indexes). Sql Table Storage might be a place to start if you are interested on this approach. You might also consider a hybrid approach, which will involve that part of your data will be held in a relational db and part of it in a nosql one.
• Before scaling the database check if there aren't any other options that you might try first. For example check if there are inefficient queries or build a caching layer, which depending on the application, can take quite a lot of stress out of the database.