I am aware that Microsoft appears to be dropping Azure, renaming SQL Azure as SQL Database, but for the purposes of this post I will stick with the old name.

In the middle of 2011 we shipped a brand new system to a client. This system required a couple of databases, a web server and some Windows services hosting. The processing demand wasn’t high so it could easily fit on a small dedicated server.

Our client had no IT support staff and we did not want to support the infrastructure. The budgets were tight, but some downtime could be tolerated provided that no human intervention was required. As we were already using Amazon SQS (Simple Queue Service), we immediately thought of using an Amazon EC2 instance running SQL Express with scripts (for example using Amazon Cloud Formation to restart the instance if it were to fail). It quickly became obvious that to write the scripts to ensure that the database came back reliably and in a consistent state was neither going to be easy nor quick, and deadlines were pressing. The quotes for fully managed dedicated servers ran into several thousand dollars per year plus a set-up fee and they would have to sign up for between 1 and 3 years. That left us looking at Windows Azure with SQL Azure. SQL Azure gave us the zero support option we were looking for at a much cheaper price than the fully managed dedicated servers. It didn’t tie the client in so we could move away if things didn’t work out. Also data is copied to a replica in the transaction so we even got a redundancy thrown in which we hadn’t expected that we could get at the rates the client was prepared to pay.

We ended up with a couple of databases, a web role and a couple of worker roles, all working fine on extra small instances. Moving from a single server solution did require changes to some parts of the application, and an effort was required to reduce the chattiness of communications with the database (as with any hosted database). At this point we were using the best choice for the client.

Competition is generally a good thing so I was very pleased to hear that Amazon has extended its RDS (Relational Database Service) to include SQL Server in addition to MySQL and Oracle. We have used Amazon RDS for MySQL, so I was very keen to see what it could offer with SQL Server. Naturally I wanted to see if we are still in the right place for our Azure-based client.

It doesn’t take long to realise that Amazon RDS SQL Server is a very different beast from SQL Azure.

Database Features

Amazon RDS SQL Server gives you a full SQL Server installation, and it supports all the core database engine features. Using SSMS you would be hard-pushed to spot that you are connected to a hosted database. You have access to all the data management views and functions, and if you really want to, you can even run SQL Profiler remotely (be very tight on your filtering!). The full list of features is here.

If you look closely, there are a few features that aren’t supported that may send you running for the hills. A big one for us is encryption. With SQL Azure we are able to connect using SSMS using an encrypted connection and administer the database without any worries about prying eyes. We also configured the web site and the worker roles to use encrypted connections. The recommended solution for connecting to databases for administration purposes in the AWS world is to use a bastion host (a machine that is only booted if access is required and the only machine from which administrative access is allowed). While this is a very sensible way of going about your production access, in a project of this size that is an overhead which I would prefer to do without and I still like the added comfort of knowing that all the traffic is encrypted.

With SQL Azure you aren’t running with a full SQL Server installation. You can’t get under the covers to access data management views and functions, and even old faithful stored procedures like sp_spaceused don’t work. You have to resort to querying the system tables. READ_COMMITTED_SNAPSHOT and ALLOW_SNAPSHOT_ISOLATION are set ON and you can’t change them. Also, you must have clustered indexes on all tables. The full list of the limitations is here.

Processing power and database size

With Amazon RDS SQL Server you can pick the storage size you want, from 20GB up to 1TB. This is data and log space combined. You can also pick the amount of memory in your server from 630MB (Micro) all the way up to 68GB (High-Memory Quadruple Extra Large), with similar choices in processing capacity.

SQL Azure you don’t get the option of picking the hardware; it is what is it. The database size is worked out differently. You only pay for the data you use, starting at just 100MB but only going up to 150GB. Log file space is not charged for, but you are billed for each database.

Backup and availability

With Amazon RDS SQL Server you are not able to do multiple availability zone deployments, so no real-time replication is built-in. Transactions logs do get backed up every 15 minutes, but you control when backups and maintenance can happen and you are able to create a snapshot of the whole database instance. Backups can be kept for up to 31 days and snapshots are kept until you explicitly delete them.

In SQL Azure each database is replicated and there are multiple redundant copies, hence the need for all tables to have a primary key. In theory you don’t need to backup your databases. Not being conformable without the option of a point in time restore we have been using the Redgate tools to take copies for us.

Administration

With the AWS management console, you are confronted with 20+ products that you may or may not be using. Finding the status of your environment takes quite a few clicks, and you need to know where you are going. The interface is fine for techies, but our client glazed over when we tried to show them the SQS management tab. At this point we didn’t bother showing them about the EC2 instances. In addition you can create alarms with email alerts based on very many criteria to help you in monitoring your database without having to log in to the console.

On the other hand the Windows Azure Platform management console is much simpler. At the moment you have a choice between a Silverlight-based interface, or, you can use the preview version of a new HTML5 Metro-style console announced as part of the Azure update announced on June 7th. However even though these are simpler than Amazon's offering you still need to know where you are going; but when you get there the status information is easy to understand. Our non-technical client can easily see what is happening and reboot the web and worker roles if they see an issue. As part of the June 7th update the monitoring of web and worker roles was expanded to a comprehensive set of metrics. Unfortunately the SQL Azure monitoring was not updated to the same level. You can see how much space you have used, and via the Query Performance tab see a list of the latest queries against the database (with some basic statistics) and the ability to display the estimated query plan for the query. This is useful - especially as it’s all displayed in your web browser, but is still limited for serious use. Unlike with Amazon, you can not set alerts to inform you of important events.

Conclusion

Comparing these two offerings.

Amazon RDS SQL Server

Pros

• Full database support
• You get an instance so you can have up to 30 databases
• Database backups can be controlled and kept for up to 31 days or indefinitely with DB Snapshots
• Good monitoring framework built-in
• Database sizes up to 1TB
• Can select the instance size according to workload

Cons

• Multiple Availability Zone deployments not supported
• No SSL connection
• Alphabet soup administration interface
• Smallest disk size 20GB

SQL Azure

Pros

• Small, cheap databases
• Simple administration interface
• Multiple redundant copies of the database as standard
• Encrypted connections

Cons

• You can’t choose the hardware you are running on
• Only supports databases up to 150GB
• Limited SQL Server features
• Limited database information and monitoring

For the time being we are happy to stay on the Azure platform for this application. But the ability to choose what level of hardware and the much larger database sizes make Amazon RDS SQL Server a very interesting option for the future. Which one should you choose? It depends...

Over the next few weeks I’ll be posting more to help you decide, looking at costs and performance.