January 28, 2020
The Advantages of Running SQL Server on AWS
So the database. It’s a pain, right? They’re hard to keep running and require constant patching. Huge licensing costs. Endless data optimization. Full-time DBAs required.
Moving that database to the cloud eliminates one set of issues right away: you don’t have to worry about maintaining your own hardware. But that really is just the beginning, if you’re willing to try something new. Amazon’s Relational Database Service (called, fittingly, Amazon RDS) is a cloud-native database platform that is truly awesome—and a real opportunity to save time, eliminate hassle and improve performance.
Not convinced? Here are nine reasons you really need to be using RDS for your cloud database needs.
RDS works with the all five of the most popular database platforms in the market: MySQL, PostgreSQL, Microsoft SQL, MariaDB, and Oracle, in addition to Amazon’s own platform. That means that you have a pretty good chance of being able to migrate your existing database without too many hoops. It won’t be seamless (Pro tip: If anyone ever tells you your database migration is going to be seamless, don’t believe them!), but it won’t be awful either.
While RDS was architected to work well with these big 5 incumbents, it works even better with Aurora, Amazon’s homegrown database platform. Out of the box, Aurora provides continuous backups, automatic self-healing and built-in redundancy across three availability zones. It also supports up to 15 read replicas (you only get 5 with MySQL, PostgreSQL et al). All that means that you get a pretty nearly indestructible and scalable database—without your having to do a lot of extra work to build all that functionality. Amazon engineers have done it for you!
A DBA’s worst nightmare is data loss, crash, or both.
With RDS, you can rest easy that (a) things are less likely to go wrong and (b) you’ll be in good shape for recovery. Why? To ensure you end up with a highly available database, Amazon is taking care of an awful lot behind the scenes. First, Amazon RDS automatically backs up your databases every 24 hours by default. This ensures that, worst case, your RPO is 24 hours. And that really is a worst case.
Routine patching is automated as well, with set maintenance windows for AWS to automatically patch your instances to keep them secure. You can also do minor version updates or database changes during these maintenance windows, with easy rollback settings in case something goes wrong with your update. Which means that if you want to resize an instance, you don’t have to wake up at 3 A.M. to do it. Schedule it in the console, and sleep well.
If you’re in a multi-AZ deployment, Amazon will automatically patch the secondary database first before failing over and patching the primary without your having to manually schedule or manage the sequencing. Once again, Amazon’s engineers have done the hard work for you.
Read replicas are an easy way to tick a lot of boxes. Locating read replicas in zones closer to your users will offer performance enhancements for read-only sessions, and you can also keep your production server running at peak performance by sending your complicated data queries (for reports, for instance) to read replicas.
If that 24-hour RPO was too big for you, those read replicas also give you a simple DR solution with an even lower RPO. And if something goes wrong and you need to promote a Read Replica to a primary server, it’s just button click in the AWS console.
Later this year, AWS is planning on releasing Multi-Region Multi Master support for Aurora. This will allow for DNS health checks to automatically fail over to a different instance in a alternate region without manually promoting the cutover.
Over time, you’re going to find yourself segregating your databases naturally into functional groups (like dev/test and production/staging), and you’re going to need to manage them differently. Fortunately, RDS makes that easy too, with Parameter and Option Groups.
Parameter Groups allow you to apply a set of configs to multiple RDS instances without repeating any actual work and without having to manage it all manually. For instance, if you need to enable SSL-only connections to your production and staging instances but not your dev and test, you can easily do that by making one parameter group for production/staging and another for dev/test. If you activate SSL for the production/staging group, every instance in that group will get the same parameter update simultaneously, while your dev/test group will maintain their settings. Parameter Groups go a long way to simplifying management of the databases across your environment. (Option Groups operate similarly to Parameter Groups, but cover different features and settings, such as memcaching.)
Amazon RDS integrates seamlessly (yes, really) with Amazon’s scaling tools, for both horizontal and vertical scaling. If you need to scale vertically to a larger or more powerful instance, it’s just a few clicks away. And if you need to scale horizontally, spinning up additional read replicas can be automated so your system responds to increasing usage demands for your read-only workloads, without missing a beat.
You can also scale down just as easily, which is critical if you want to avoid runaway costs. Let’s say you have a big promotion coming up and know you’re going to get hit with a gargantuan number of reads. You spin up some extra read replicas of your application and configure rules so it will continue to scale automatically as usage rises, without human intervention (aka, bottlenecks). But to keep your costs down, you need to scale that all back down when the promotion is over so you don’t have to keep paying for peak-use resources. Trying doing that with a server rack!
RDS monitors itself and can detect anomalies and automatically make adjustments to restore itself to normal without you actually doing anything—recovery without human intervention, without delay and without waking you up in the middle of the night (or pulling you away from other work).
Recently a Mission client messaged me after they did a routine review of their monitoring logs, which included a sudden and unexpected drop in CPU usage on a single RDS instance the previous night. We reached out to AWS to see what had caused the dip and learned Amazon had experienced a hardware failure that had caused our client’s instances to drop below the expected speed. RDS detected the slow-down, and the instance failed over to different hardware. Performance returned to spec, and neither we nor our client had to do anything (except build the instance at the outset with multi-AZ to support this failover, of course). No service interruption, no work interruption, no hassle. Pretty awesome, eh?
One of the things we love most about RDS is its flexibility—and how easily you can unleash all that power from a single console. You can start a snapshot, spin up a new instance, test schema changes, test your application against non-live data and then tear it all down.
Even better? You can now just stop an instance instead of tearing it down. This is truly awesome for a dev database that you want to save and come back to but aren’t using all the time and so don’t want to pay for all the time. RDS lets you stop the instance to save money and then turn it back on when you need it again. You do still have to pay for the storage, but you’re able to save the instance cost until you need to spin it back up.
Amazon RDS is really an enormous time-saver when you’re looking to build a cloud-based database. Yeah, there are other ways to get it done, but with RDS, you’re getting the benefit of a whole team of Amazon engineers whose full-time job is to continue to develop the performance and functionality of the tool—and to integrate it with the rest of the AWS ecosystem. And leaning on them for the hardcore DB development means you’ll have lots more time available to work on developing your application.
You’ll probably be able to sleep better too.
Get started on your RDS migration by scheduling a 30-minute SA On-Demand where you can talk to me or one of our other engineers about what steps you need to take to get ready to migrate – just let them know Kiril sent you!