Published on June 25th, 2015 | by Warner Chaves20
DBA Guide to running SQL Server on Amazon RDS
UPDATE: I have published a course on Pluralsight on implementing SQL Server on RDS, including how to build, configure, setup HA and replication to RDS. The course can be found HERE.
In case you didn’t notice, the cloud wars are here. I feel like we’re back in the Netscape vs Internet Explorer days of the 90’s here except the battle is not for the desktop but for the development and infrastructure platform. Just like any other major database (SQL or NoSQL), SQL Server now has many options and variations to run on the different clouds:
- Amazon Infrastructure as a Service: run SQL on a Windows VM.
- Azure Infrastructure as a Service: run SQL on a Windows VM.
- Amazon SQL Server RDS: run SQL Server as a DaaS (Database as a Service) platform.
- Azure SQL Database: run a very-close-to-SQL Server variation of your database on Microsoft’s DaaS platform.
Now each one of those comes with it’s own set of caveats, pros and cons. We already covered Azure SQL Database here. And I want to eventually cover my recommendations and best practices for the other ones on different blog posts here at SQLTurbo. For today though, we’re going to focus on SQL Server on Amazon RDS and run through what the service offers and what our recommendations are.
First things first, RDS is a Database as a Service offering, so you give up fine grained control and some features of the product in exchange for a managed service where Amazon takes care of the infrastructure, high availability and backups. You can connect to your SQL instance but you have no access to the underlying host, nor will you get sysadmin access. We’ll go into detail for each feature on the different sections below.
Versions and licensing
Currently Amazon RDS supports SQL 2008 R2, SQL 2012 and SQL 2014 (this last one up to Standard Edition). The editions can go from Express to Enterprise, however, if you want to run Enterprise then you’ll need to provide your own license. Amazon does NOT offer pay-as-you-go billing for SQL Enterprise edition.
There are several main activities that Amazon will take off your hands as part of providing the database service:
- Patching: as part of your configuration you can specify a maintenance window and sign up to get patches applied. Note however that you have no control over what patch level Amazon wants to certify and bring RDS up to. For example, SQL 2008 R2 is currently offered on the SP1 + CU3 version, dating back to October 2011! SQL Server 2012 does NOT do much better, the current version offered is the very first RTM launch version, dating back to March 2012!
- Upgrade: you can, if you want, have the service do a major version upgrade from 2008 R2 to 2012.
- Backups: you can specify a retention period of up to 35 days. Amazon’s documentation offers an RPO of 5 minutes, however, I’m not sure how (or if) this is enforced on an official SLA. Another thing worth noting is that RDS does NOT support changing a databases recovery model. If you change from FULL to SIMPLE then Amazon will change it back. If you remove backup retention then Amazon will just set it to SIMPLE. Database snapshots can also be taken and up to 50 can be kept by Amazon.
- High Availability: to provide high availability, RDS can automatically setup a database mirroring secondary on another Availability Zone (another Amazon data center in the same region). As part of their service, Amazon will also keep logins and their permissions in sync between the primary and secondary. Unfortunately, jobs are not kept in sync, this will be your responsibility.
Performance and Scalability
Performance for SQL Server on Amazon RDS is very similar to specifying your hardware requirements on Infrastructure as a Service virtual machines.
You have a choice of different numbers of vCpus and RAM as well as storage from General SSD (middle ground in terms of performance), Provisioned IOPS SSD (highest performance, highest cost) and magnetic (lowest cost, lowest performance). Some of the instance classes will also say if they are EBS optimized. This means that Amazon will have a dedicated connection and bandwidth just for storage.
Of all the instance types, the T2 one is of special interest. This type is Burst Capable. This means that the instance has lower cost than the higher fixed performance ones but also has the capability of bursting CPU to meet periodic high demands. This is ideal for workloads where there are significant ups and downs on load during the day.
As part of being a cloud service, you can scale from one instance type to another to modify CPU and RAM capacity at any time. This however, cannot be said for storage!
A big big caveat when dealing with SQL Server on RDS is that unlike the other RDS services, SQL Server does not support changing storage after deployment. This basically means that whatever size and characteristics you chose at the beginning of your deployment must account for future growth. Any change in storage will require to create a new instance with the new storage config and migrate to that.
Other Major Gotchas
Due to the nature of being a cloud service, Amazon has to put some limitations on the product that will allow them to automate and easily manage the service while at the same time preventing users from killing their platform. These are some possible show stoppers that we’ve run into when exploring an RDS deployment with clients:
- Each RDS instance can have a max of 30 Dbs.
- Max size of the instance is 4TB.
- You CANNOT do a backup/restore from a full SQL Server to SQL on RDS. You will have to do logical data movement through scripting, SSIS or BCP.
- Distributed queries (linked servers and MSDTC) are NOT officially supported. We’ve had success LINKING TO RDS but NOT LINKING OUT from RDS.
- SQL Server Replication is also NOT officially supported. Forum posts talk about different user experiences regarding this one, some got it working, others not. In the end it’s not officially supported, so even if it works today, if it stops working tomorrow you have no one to complain to. Given this situation, we can’t recommend anyone using replication on RDS.
- Even though SQL 2012 introduced AlwaysOn Availability Groups, Amazon is NOT using it, the HA setup is still going to be database mirroring.
- Instant file initialization is NOT enabled on RDS and you can’t enable it. This means autogrowth events need to be avoided AT ALL COST.
- Amazon RDS instances get created with the SQL_Latin1_General_CP1_CI_AS collation. You can specify the individual database collation to be different but this can lead to issues when joining data with tempdb for example, since tempdb will inherit the default instance collation.
Previously Windows Authentication was NOT supported and it was part of our list of gotchas. This has changed recently though and it is now supported in RDS! The details can be found HERE.
Monitoring and tracing
You can monitor your databases through a regular SQL connection as long as the script doesn’t access anything blocked by RDS. For example, to monitor the SQL errorlog and the Agent log, you will need to use the SP provided by Amazon for this specific purpose (rdsadmin.dbo.rds_read_error_log). You can also access these logs from the AWS RDS console.
Amazon’s own monitoring Cloudwatch service also provides a set of metrics that can be monitored for baselining and setup for alerting. More details on Cloudwatch for RDS are here.
For tracing, you can connect SQL Server Profiler from another server or create a Profiler server-side trace. Extended events unfortunately are NOT yet supported.
Amazon supports running the SQL Server Agent. Some limitations apply since you don’t have access to the underlying host, so for example, command line steps won’t work. Email notifications are also NOT available.
Both SSL connection encryption and TDE are supported on RDS. You’ll need Enterprise licensing for TDE. Amazon also provides their own encryption at rest as an instance option.
- If you’re going to run production you definitely need to go with the automated Multi-Availability zone deployment. In the cloud, you never know when things might go down and you have no control of the infrastructure, you have to use this redundancy.
- Predetermine your necessary creation config options like: will the RDS instance have a public-facing endpoint, port, VPC, etc.
- Predetermine your configuration for the instance itself. This is done through what Amazon calls a Parameter Group. The Parameter group includes options that you would normally find under sp_configure and also some popular trace flags.
- If you’re migrating from an existing install, make sure you’re not moving back on patch level to what RDS is offering. You could end up with a situation where you had an issue prevented by a fix that reappears after a minor version downgrade.
- Pre-grow all your files. On-premises you can get away with having instant file initialization and autogrowth, however, without instant file initialization, autogrowth on RDS will make sessions wait and it could be enough to throw timeouts.
- Measure the throughput and latency you get with the Multi-AZ deployment. RDS only offers synchronous replication so you will need to take this into account as part of your planning.
- Just like on-premises SQL Server, the mirroring failover will depend on the speed of transaction replay and recovery. Make sure to test this properly.
- For high performance production workloads we recommend an EBS optimized instance with Provisioned IOPS.
- For medium performance workloads with lower concurrency and more in tune with a “business hours” schedule, the T2 type instances are great from a cost/benefit perspective.
- For cheap archival type databases, a small instance type with magnetic storage is enough to provide access to the data at a very low cost. It will be slow but at least it will be available.
I hope this is a handy guide for everyone considering deploying SQL Server on RDS,.If there are any questions you want us to cover and add to the guide or any tips you want to share please leave it on the comments below!