If you plan on using Amazon Web Services (AWS) to host your SQL Server based applications in the cloud, then you have a couple of options.
One is just to have an EC2 instance (a VM) and install the versions of the OS and SQL Server you want. There are also images you can use that will have these pre-installed. This is what’s known as the IaaS option (Infrastructure as a Service). If you take this option, then SQL Server is exactly the same as it would be if you had it on-prem.
Or you can go with Amazon RDS (Relational Database Service). This is more of a managed service where Amazon looks after some aspects of your database server for you. In return you give up some of the control you would have with your own server or VM. You can still pick the version of SQL Server you want installed, usually down to which cumulative update you want – though note that RDS normally lags behind the latest box version of SQL by 3 months or so. RDS is what’s known as a PaaS offering (Platform as a Service).
So, what do you give up and what do you gain? Here’s a quick summary of a few things I’ve noticed. This is not intended to be comprehensive and please bear in mind that AWS is a fast-moving beast – changes happen regularly.
What you can access
You can still connect to and manage your instance using SSMS, but you have no direct access to the server hosting it, such as configuration of the OS, or access to the disks. Through SSMS you have an access level slightly below Sa – which limits you to only what is allowed.
High Availability
In RDS you don’t use Availability Groups, Log Shipping or any of that stuff. Instead, HA is achieved using multiple Availability Zones (AZ). With this enabled, writes are performed synchronously to a replica in a second availability zone (usually a separate physical data centre in the same region). If the primary AZ goes down, then failover will happen automatically. You can also choose to replicate to other regions to be protected again failure of an entire AWS region – though often data protection laws mean that might not be an option for you if you hold personal data and there is only one AWS region in your country.
AWS RDS Multi-AZ promises a monthly up-time of 99.5% (which allows for 22 minutes of downtime per month). Full details of the SLA are here:
https://aws.amazon.com/rds/sla/
Disaster Recovery
RDS performs automated backups of your whole instance, including the equivalent of log backups every 5 minutes. That means that in case of a disaster, where Multi-AZ failover fails, or where someone deletes data they shouldn’t, then the maximum data loss (RPO) is 5 minutes. The built-in functionality allows you to restore your instance to a point in time, which is implemented by restoring to a new instance. It is not possible to recover individual databases. Backup retention can be set up to maximum of 35 days.
If you need more than that then you can still take native SQL backups, but this has to be enabled specifically and because you can’t access the underlying disks you have to use a stored procedure specific to RDS:
https://aws.amazon.com/premiumsupport/knowledge-center/native-backup-rds-sql-server/
There are also other options – such as taking extra snapshots and storing them in a separate region.
Patching
AWS handles patching of minor versions to your instance for you. You can choose for this to be done automatically or triggered by manual intervention through the AWS console.
Encryption
RDS has its own at-rest encryption similar to TDE. This is available for all instances hosted on RDS so unlike TDE you don’t need to be on an Enterprise Edition of SQL Server.
Collation
All SQL Server instances on RDS are set up with a server collation of SQL_Latin1_General_CP1_CI_AS and you can’t change this. You can specify a different collation for your databases but this might mean you run into collation issues if you use temporary tables and compare string based columns with those in your databases – as temp tables are created with the collation of the instance.
Server Memory
In an on-premise version of SQL Server we would carefully provision a proportion of the overall server memory for SQL Server. On RDS this is not an available setting, the amount of memory is fixed with our general instance sizing, however Amazon do make recommendations for tuning the amount of memory given to an RDS instance – which would mean resizing the instance if necessary.
The recommendation is that there should be enough memory that the “working set” of data is retained in memory. Memory should be tuned so that the ReadIOPS metric is “small and stable”.
Recovery Model
This is worth mentioning even though it is not entirely configurable in RDS. If you have backup retention set to longer than 0 days (i.e. take backups) then the recovery model will be set to FULL. If you set backup retention to zero (which disables backups) then recovery model will be set to SIMPLE. If you manually change the recovery model, RDS will automatically change it back within 5 minutes. This applies to all databases.
MAXDOP and parallelism
In RDS you cannot change the instance level MAXDOP, or the “Cost Threshold for Parallelism” through SQL. Instead these must be configured through a parameter group:
Optimize for Ad-hoc Workloads
Like the parallelism settings this cannot be modified through SQL. So, if you want this setting enabled you must use a parameter group:
Instant File Initialization
This setting means that when files grow the new space can be allocated immediately without taking time to fill the space with zeros.
This cannot be enabled in RDS, so it is of extra importance to size databases appropriately to avoid auto-growth where possible.
Extended Events vs Profiler
Unfortunately, Extended Events is not available in RDS, so if you wish to trace events you must use traditional traces/profiler.
Note that when setting up a trace against a busy instance you should create a server-side trace to minimize the impact on performance.
That’s the list of key differences I’ve noticed so far in working with RDS to provision new SQL instances.
What stays the same
There are a few other things you may be wondering if you still have control over (I know I was). So just to confirm, you can still do the following:
- Configure multiple files for TempDB
- Use either SQL or Windows authentication (or mixed)
- Schedule jobs with SQL Server Agent
- Run DBCC CHECKDB
- Rebuild indexes and statistics
Please comment on this post if you notice any other significant differences you think people should be aware of – or if you notice updates to AWS that make any of these points invalid.
Got a problem or embarking on a SQL Server project and want some help and advice? I’m available for consulting – please get in touch or check out my services page to find out what I can do for you.
Thanks for the Post. Really usefull.
We trying to setup RDS and one of the Index rebuild agent job is using path that we dont have access to. Is there any way we can manage Index rebuild for RDS?
Not clear what your issue might be. Index rebuilds definitely work (I’ve got them running on a number of environments). You shouldn’t need to access the file system though (if that is what you mean by path), if your job is trying to access the disk then you’ll need to rewrite your code so it doesn’t. Happy to share my thoughts if you can show a quick example of what you’re trying to do.