How do I run SQL Server on AWS?
Running SQL Server on AWS can be done in 2 ways.
- Relation Database Service (RDS): AWS’s managed solution where some of the administration (maintenance, backups and patching) is handled for you.
- EC2: Your very own virtual machine in the cloud. With EC2, you manage SQL Server, just like you would do on-premises. This gives you full control over your SQL instance.
Why not just use Azure?
Azure may be the right choice for you. This purpose of this post is to show that you do have options and that Azure is not the only choice. Really, you should weigh up what you need from your cloud provider and make a choice. Managing a multi-cloud environment can be challenging. If you are working for a company already heavily invested it AWS, it may make more sense to stay with AWS. Even if not, AWS’s SQL Server offering is mature and RDS’s features are improving all the time so you should consider what you can achieve with AWS.
Differences/Similarities/Pros and Cons
| Feature | EC2 | RDS |
|---|---|---|
| Use SSMS | ✔ | ✔ |
| Use database as source or target for SSIS | ✔ | ✔ |
| Full control over the instance | ✔ | |
| Automated backups | ✔ | |
| Automated patching | ✔ | |
| Always On Availability groups | ✔ | ✔ |
| AWS-managed Multi AZ deployment | ✔ | |
| Log Shipping | ✔ | |
| Replication | ✔ | |
| Database mail | ✔ | |
| Linked Servers | ✔ |
A full list of included and excluded features can be viewed at https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_SQLServer.html
RDS key facts
- Limited control
- The master user name you use when you create a DB instance is a SQL Server Authentication login that is a member of the processadmin, public, and setupadmin fixed server roles. It is not a member of the sysadmin fixed server role.
- Anyone who creates a db is assigned to db_owner role on that DB.
- More administration tasks are handled automatically e.g. backups, OS patching and SQL patching
- Bring Your Own licence is no longer available on RDS
- License included (covers multi AZ)
- Standard
- Web (Must only be used for public and internet-accessible webpages, websites, web applications, and web services.)
- Enterprise
- Backups
- Can still be pushed to S3
- Native backup and restore using .bak files to/from S3
- Versions: 2008 R2 – 2017
- Limited to 30 databases per instance
- Window/Mixed authentication
- Multi AZ is very simple to set up
- TDE at rest
- SSL for data in transit
- No database Mail
EC2 approach
- Full control of instance
- Bring your own licence (BYOL) options
- Licence included (AMI)
- Standard
- Enterprise
- High Availability Disaster Recovery HADR
- You can use Always On Availability Groups
- Automatic failover between AZs
- Synchronous commit
- Latency about 1ms to 2 ms
- Manual failover between Regions option for increase resiliency
- You can use Always On Availability Groups
Backups
EC2, you are responsible for all of your own backups. Backups can be pushed to S3 to save reduce costs. I have a full post dedicated to SQL Server backups on EC2. You could also store backups on EBS volumes attached to your instance however this is much more expensive than storing on S3.
RDS will handle backups for you. You can restore to a point in time in RDS withing the backup window you configure. Transaction logs are taken every 5 minutes so you may experience data loss up to 5 minutes. All databases are restored with 1 second of each other. The downside to this is that transactions spanning multiple databases may not be recovered consistently.
Summary
Features are being added to RDS all the time however if you need features in the excluded list, you would need to go with an EC2 installation. However if your business size doesn’t allow for DBAs or database specialists, RDS may be the best option as AWS take care of all the installation and maintenance for you.
Resources
[…] John McCormack identifies some differences between running SQL Server in EC2 versus RDS on Amazon We…: […]