I was giving the task to investigate if RDS is a good option for us to migrate our SQL server database from premise to the cloud. In this blog post I will only talk about the limitations of RDS. However RDS can be used in other cases, however after careful analysis RDS was not the best option. We decided to go with EC2.
Below I will list all the limitations of RDS.
Limits for Microsoft SQL Server DB Instances
The Amazon RDS implementation of Microsoft SQL Server on a DB instance have some limitations you should be aware of:
- You can create up to 30 databases on each of your DB instances running Microsoft SQL Server. The Microsoft system databases, such as
model, don’t count toward this limit.
- Some ports are reserved for Amazon RDS use and you can’t use them when you create a DB instance.
- 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.
- The minimum 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 storage size for a SQL Server DB instance is 4 TB for the Enterprise, Standard, and Web editions, and 300 GB for the Express edition.
If you have a scenario that requires a larger amount of storage, it is possible to use sharding across multiple DB instances to get around this limit. This approach requires data-dependent routing logic in applications that connect to the sharded system, so that data gets queried from and written to the appropriate shard. You can either use an existing framework like Hibernate Shards or write custom code to enable this. If you do choose to use an existing framework, it must not require any components to be installed on the same server as the DB instance. For an example of a sharding solution using an existing framework, see Using an Example of Sharding with Hibernate.
- Because of the extensibility limitations of striped storage attached to Windows Server, 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. If you need to increase the storage of a SQL Server DB Instance, you can backup your databases, create a new DB instance with increased storage, and then restore the databases into the new DB instance. For more information, see Importing and Exporting SQL Server Databases.
- Amazon RDS doesn’t support some features of SQL Server. This includes components such as SQL Server Analysis Services, SQL Server Integration Services, SQL Server Reporting Services, Data Quality Services, and Master Data Services. To use these features, you can run SQL Server components in an Amazon EC2 instance with Amazon EBS storage, pursuant to Microsoft licensing policies.
- Because of limitations in Microsoft SQL Server, restoring to a point in time before successful execution of a DROP DATABASE might not reflect the state of that database at that point in time. For example, the dropped database is typically restored to its state up to 5 minutes before the DROP DATABASE command was issued, which means that you can’t restore the transactions made during those few minutes on your dropped database. To work around this, you can reissue the DROP DATABASE command after the restore operation is completed. Dropping a database removes the transaction logs for that database.
Microsoft SQL Server Security
The following server-level roles are not currently available in Amazon RDS:
The following server-level permissions are not available on SQL Server DB instances:
- ADMINISTER BULK OPERATIONS
- ALTER ANY CREDENTIAL
- ALTER ANY EVENT NOTIFICATION
- ALTER ANY EVENT SESSION
- ALTER ANY SERVER AUDIT
- ALTER RESOURCES
- ALTER SETTINGS (You can use the DB Parameter Group APIs to modify parameters. For more information, see Working with DB Parameter Groups.
- AUTHENTICATE SERVER
- CREATE DDL EVENT NOTIFICATION
- CREATE ENDPOINT
- CREATE TRACE EVENT NOTIFICATION
- EXTERNAL ACCESS ASSEMBLY
- SHUTDOWN (You can use the RDS reboot option instead)
- UNSAFE ASSEMBLY
- ALTER ANY AVAILABILITY GROUP (SQL Server 2012 only)
- CREATE ANY AVAILABILITY GROUP (SQL Server 2012 only)
Amazon RDS currently does not support the following SQL Server features:
- Maintenance Plans
- Database Mail
- Distributed Queries (i.e., Linked Servers)
- Database Log Shipping
- Change Data Capture (CDC) – Consider using Change Tracking as an alternative to CDC.
- The ability to run Reporting, Analysis, Integration, or Master Data Services on the same server as the DB instance. If you need to do this, we recommend that you either install SQL Server on an Amazon EC2 instance or use an on-premise SQL Server instance to act as the Reporting, Analysis, Integration, or Master Data Services server.
- Performance Data Collector
- Service Broker or additional T-SQL endpoints (all operations using CREATE ENDPOINT are unavailable)
- Distribution Transaction Coordinator (MSDTC)
- WCF Data Services
- FILESTREAM support
- Policy-Based Management
- SQL Server Audit
- BULK INSERT and OPENROWSET(BULK…) features
- Data Quality Services
- Instant file initialization
- Always On (2012 Enterprise Edition)
- File tables
- Server level triggers
Microsoft SQL Server 2016 Support on Amazon RDS
In addition to the unsupported features of previous versions, the following Server 2016 features are not supported:
- Stretch database
- Backing up to Microsoft Azure Blob Storage