Disclaimer: I’ve a Microsoft MVP and shareholder, but neither of these things affected my opinions in this post.
Cloud vendors have built a rich array of platform as a service (PaaS) solutions on their platforms. They market these heavily, because they have higher degrees of stickyness compared to IaaS platforms (and in many cases they likely have higher profit margins), but they also have key benefits to the users of the platform. Because a PaaS solution is fully managed by the cloud provider, they tend have a set of common features:
- Easy to deploy–you are never running setup.exe
- Built-in high availability–no need to configure a cluster
- Easy disaster recovery/geo-replication–usually in most services it’s just a few clicks away
- Automated backups
- Automated and possibly zero downtime patching
While some services include other really useful features (for example the query data collected by the Azure SQL Database and Managed Instance platforms), I wanted to focus on the common value adds to PaaS systems across providers. I made the last two of these bold, because I feel like they are are the most important, especially in scenarios where the vendor doesn’t own the source to the applications. Like Amazon RDS for SQL Server.
Amazon RDS Backups
I’m writing this post this week, mainly because of what I just learned this week about the backups for SQL Server on RDS. I was in some client calls this week when I learned the default backup approach is volume snapshots (which I knew), but what I didn’t know what that you couldn’t restore an individual database with these default backups.
I feel like their docs are deliberately vague about this–it’s not clearly obvious that this is the case, but a few Stack Overflow threads and discussions with fellow MVPs confirmed that I was told on the call. Amazon does support you taking your own backups on RDS, and you can then restore individual databases to individual points in time, but where’s that fun (and more importantly the value proposition) in that. To me, this really eliminates one of the biggest benefits of using a PaaS service. AWS refers to normal backup/restore as “Native Backup/Restore” as you are reading docs.
Patching
Azure SQL Database and Managed Instance, both seamlessly patch your databases without your knowledge. For the most part those services utilize hot patching, which means in many cases there isn’t any downtime to install the Azure equivalent of a CU. (Owning the source code has it’s benefits) Amazon RDS can automatically install selected CUs, but you should be aware that is an option when you deploy your instance.
"ValidUpgradeTarget": [
{
"Engine": "sqlserver-se",
"EngineVersion": "14.00.3192.2.v1",
"Description": "SQL Server 2017 14.00.3192.2.v1",
"AutoUpgrade": true,
"IsMajorVersionUpgrade": false
}The other thing you should note, is that you may not always have the most current CU available to you. Currently, AWS supports CU12, which is three CUs behind current. However, our customer was only on CU8–so patching doesn’t seem to be as automatic or easy as it is on the Azure side.
Licensing
This one really isn’t AWS’ fault (it’s Microsoft’s fault), but there are a couple of issues with licensing RDS. The first is that you don’t have the option of running Developer Edition for non-production workloads. Which, especially if you are running Enterprise Edition represents an expensive choice–you either need to license Dev/Test for Enterprise, or run Standard Edition in dev to save some money, but not have adequate features in place (though the code platform is mostly the same, performance characteristics can be dramatically different). Additionally, you cannot bring your own SQL Server licenses to RDS, you have to lease them though AWS. Neither of these problems are the fault of AWS, but they still suck.
AWS is an excellent cloud platform, even thought I actively hate their console. For most part a lot of components are very similar or even better than Azure. However, when I comes to a service where Microsoft owns the source code and AWS doesn’t, you can see the clear superiority of Azure. So what is an AWS shop that runs SQL Server to do? IMO, the backup/restore thing is a deal breaker–I would just recommend running in an EC2 VM.
Pingback: Amazon RDS: Backups and Patching – Curated SQL
Not trying to shill for any companies, but Clumio offers a far more elegant solution than what AWS provides internally. I think its worth noting that companies relying on the volume snapshots in AWS are setting themselves up for failure. Whether its Clumio or any backup/restore vendor, businesses shouldn’t rely on those backups if they want to be able to restore individual DBs.
This was a great read, though, as I continue my research into moving our SQL Servers into RDS.
Joey,
If I have only one database per an “RDS for SQL Server” instance, then what is the problem with backups? In this case, I think it actually works better for software engineers, since it is fully managed in the cloud, and does not rely on a Database Administrator. The DBA can then be a Data Engineer.
I do agree that the RDS documentation is somewhat vague about these things, and it does create an annoying learning curve. However, the learning curve is now what can cost a business millions of dollars. Learning curves are one-time costs. The real cost is mean-time-to-recovery assumptions: You won’t know how long your recovery time takes unless you habitually practice it. Further, the default recovery mode is only 7 days, and many people ignore configuring that, thinking that if the retention policy on the underlying S3 bucket is 7 years, then they are fine. Finally, the one thing that drives me nuts about the backup documentation is that there is no option to take a copy-only backup. You can verify this by taking a native backup and checking the SQL Server Error Logs in CloudWatch. Native backups break the backup log chain.
Hope this helps your readers and you out.