What To Know: SQL Server Database on AWS RDS

In many of our migration and application refactoring projects involving Microsoft workloads, one of the most common areas of customer confusion is whether or not to use the Amazon SQL Server Relational Database Service (RDS). Amazon RDS for SQL Server is a relational database management system provided as-a-service, which makes it easy to set up, operate, and scale SQL Server deployments. However, only the SQL instance service endpoint is exposed and you will not gain access to the underlying infrastructure (server or operating system level). In short, this is not the same as running SQL Server on a virtual machine or physical hardware, and there are many caveats and limitations to consider. As a general rule, if you are running a proprietary application that relies on SQL Server databases (SharePoint, Dynamics GP, RMS, etc.), it is not likely to be compatible with Amazon RDS. We recommend that you consult with the application vendor to determine if their SQL databases are compatible with Amazon SQL Server RDS prior to making any architectural commitments.

As of this writing, Amazon RDS for SQL Server currently supports Microsoft SQL Server 2008 R2, 2012, 2014, and 2016. Information about the supported minor versions is available in the Amazon RDS User Guide. Below are some general limits for all Amazon RDS for Microsoft SQL Server editions:

• You can only create a maximum of 30 databases on your RDS DB instance.

• The minimum allowable storage size for a SQL Server DB instance is 20 GB for the Express and Web Editions, and 200 GB for the Standard and Enterprise Editions.

• The maximum allocable storage size is 4 TB for the Enterprise, Standard, and Web editions, and 300 GB for the Express edition.

• Amazon RDS doesn't support some SQL Server components including SQL Server Analysis Services, SQL Server Integration Services, SQL Server Reporting Services, Data Quality Services, and Master Data Services. To use these features, you would need to run SQL Server components on a virtual machine (such as an Amazon EC2 instance with Amazon EBS storage, pursuant to Microsoft licensing policies).

• Amazon RDS doesn't currently support increasing storage on a SQL Server DB instance. We recommend that you provision storage according to anticipated future storage growth (Note that there are ways around this limitation, but it requires downtime).

• In terms of Microsoft SQL Server security, the following server-level roles are not available: bulkadmin, dbcreator, diskadmin, securityadmin, serveradmin, sysadmin. The following server-level permissions are not available: ADMINISTER BULK OPERATIONS, ALTER ANY CREDENTIAL, ALTER ANY EVENT NOTIFICATION, ALTER ANY EVENT SESSION, ALTER ANY SERVER AUDIT, ALTER RESOURCES, ALTER SETTINGS, AUTHENTICATE SERVER, CONTROL_SERVER, CREATE DDL EVENT NOTIFICATION, CREATE ENDPOINT, CREATE TRACE EVENT NOTIFICATION, EXTERNAL ACCESS ASSEMBLY, SHUTDOWN, UNSAFE ASSEMBLY, ALTER ANY AVAILABILITY GROUP (SQL Server 2012 only), CREATE ANY AVAILABILITY GROUP (SQL Server 2012 only). For the full list, refer to this link.

• Amazon RDS for SQL Server does not support importing data into the msdb database.

• You can't rename databases on a DB instance in a SQL Server Multi-AZ with Mirroring deployment (Note that there are ways around this limitation, but some downtime is required).

• Some ports are reserved for Amazon RDS use and you can't use them when you create a DB instance.

• In terms of local time zones, this can only be set during instance creation. You can't modify the time zone of an existing SQL Server DB instance. You also can't restore a snapshot from a DB instance in one time zone to a DB instance in a different time zone.

For the full list of published general limits, refer to this link.

In addition, not all Microsoft SQL Server features are available in AWS RDS. For example - database mail, maintenance plans, and FILESTREAM are not supported. We encourage you to thoroughly review the available features for your chosen version of SQL Server to determine compatibility. The full list of supported versions and features can be found here: Version and Feature Support on Amazon RDS.