Lately I’ve been spending lot of time outside my natural habitat, Azure, and I’ve entered the AWS frontier. Because of this I decided to write down some of my experiences about how the SQL Server deployments between these two cloud platforms compare to each other. AWS has been around longer than Azure by few years and is the largest of all the public cloud platforms, and I believe, that even today it’s hosting greater number of Windows based VMs than Azure.
With Azure Microsoft had the opposite approach to hosting SQL Server databases, and rather than starting with VMs they first released Azure SQL Database and then later on Microsoft added support for SQL Servers in VMs to attract more of the existing workloads into Azure. Noting the different approaches, let’s then take a look at how they compare when it comes to SQL Server deployments.
What is available?
Both Azure and AWS provide two flavors of SQL Server services. One where you get the VM and the freedom to do everything you please with it, commonly known as the Infrastructure-as-a-Service (IaaS). In the other one the vendor is taking care of most of the mundate administrative tasks and this latter one is called Platform-as-a-Service (Paas).
| Cloud Platform | Service | Deployment Model |
|---|---|---|
| Azure | Azure Virtual Machine | IaaS |
| Azure | Azure SQL Server Managed Instance | PaaS |
| Azure | Azure SQL Server Database | PaaS |
| AWS | AWS EC2 | IaaS |
| AWS | AWS RDS | PaaS |
Infrastructure-as-a-Service
When it comes to virtual machines, options here are either Azure Virtual Machine and AWS EC2 (Elastic Compute Cloud). When it comes to VM’s, there are multiple different flavors in both cloud platforms, some are optimized for memory, others for compute, some for high storage throughput and some might even be purple (but not very likely). This multitude of options is also the one thing that makes the VMs sometimes a better option than fully managed database service, you can find a good option for pretty much any type of SQL Server workload you can imagine.
While both cloud platforms offer huge variety of VMs to pick for your SQL Server workloads, Microsoft does have a slight edge. First there is Azure Defender for SQL which also supports virtual machines, I am not aware if AWS has something similar, but if they do I’d appreciate it if you let me know. The second thing is the SQL Server Resource Provider which brings some “PaaS-lite” kind of goodness your way. Automated patching, backups and ability to change licensing on the fly? Yes please!
Platform-as-a-Service
When it comes to PaaS deployment model for SQL Server, both cloud vendors have an option to provide us with managed instance. In Azure this is called Azure SQL Managed Instance and in AWS this is called RDS for SQL Server. RDS is sometimes mistakenly called a database, but it’s not that, it’s a SQL Server instance in which you can host multiple databases (however with some caveats).
Besides Managed Instance Azure also has a database offering called Azure SQL Database, while the naming isn’t the most imaginative I give Microsoft some points for being consistent.
So how do these two compare? Let’s start with the Azure Managed Instance, which is providing nice balance of fully managed service and still giving enough access to instance level features to support older legacy applications. With the Gen5 hardware options Microsoft is now also providing more reasonably sized options for migrating smaller workloads there, however there’s still few things that are managed in really awkward manner. One example of this is that the storage throughput which is linked to size of the database file. Yeah, you read that right. Managed Instances also have limits on the size of databases they can support (1-4TB in Business Critical, 8 in General Purpose).
I would really like to like RDS, however AWS is not making that very easy. My pet peeve is, that for some reason there is no way to restore a single database in RDS using the built-in tools. You have to restore the whole instance and all the databases in it. The work around is to restore the instance alongside the existing one, then usual native SQL backup and restore to recover the database you want. Not really the most intuitive workflow. Also the native backup/restore performance sometimes seems to vary. Not too long ago I was restoring a small database where the restore took 40 minutes in the first run, and 7 minutes in the next.
In terms of monitoring, Azure SQL Managed Instances are one step ahead with built-in tooling such as Intelligent Insights and SQL Analytics. RDS has a decent monitoring too with the CloudWatch, but at least to me it doesn’t seem to go as deep as it’s Azure counterpart does, even with Performance Insights enabled. Managed Instances also have the slight advantage in the performance management as it’s able to make use of Machine Learning and AI powered Automatic Tuning.
Both cloud vendors also provide a way to deploy their managed instances outside their own clouds. AWS allows RDS to be deployed to on-premises with RDS On VMWare package. Here Microsoft goes a bit further with their Azure Arc-Enabled Data Services that allows Managed Instances to be deployed pretty much everywhere.
While Azure Managed Instances and RDS are mostly fully managed services, you do need to make sure that regular index maintenance and database consistency checks are being run.
Finally there is the Azure SQL Database, which sets Microsoft apart from the competition. It’s exactly what it claims to be, a database. With this option you do not have instance level access anymore, just a access to a database. Azure SQL Database has multiple different tiers, including Serverless and Hyperscale. Azure SQL Database has been growing in popularity in the past couple years, and it made the Top 3 list of “DBMS of the Year 2020” listing by DB-Engines, being the first cloud database to do so in the 9 years these rankings have been published.
Conclusion
I don’t think it’s a huge surprise that Microsoft does SQL Server very well, no matter how it is deployed. Being the owner of the product gives them an edge over any competition (just look at what they did to SQL Server architecture to make Hyperscale work!), but still I have to tip my hat to AWS for the effort. While RDS has shortcomings, to my experience it does it’s basic function rather well.
You may have noticed that I didn’t bother write anything about the cost effectiveness. If you Bing with the Google to figure out which cloud platform gives the best bang for your buck with the SQL Server workloads, you’ll find a bunch of whitepapers and studies saying that they both do. Who am I to argue with that?
Leave a Reply