Amazon Relational Database Service (Amazon RDS) is a web service that makes it easier to set up, operate, and scale a relational database in the cloud. It provides cost-efficient, resizeable capacity for an industry-standard relational database and manages common database administration tasks. Although Amazon RDS is great for many applications to store its data, but has many limitations where many features cannot be used due to restricted permissions at file system level and on registry and OS level. Some of the important restricted features are mentioned below.
– Only SQL Authentication Logins are allowed. Windows authentication is not supported, so applications are to be changed to only use SQL Server authentication.
– Amazon RDS only provides support for core database engine of SQL Server, but does not include SSRS, SSAS, SSIS, etc. If application requires these services, then they can be implemented on on premise server and can access data from Amazon RDS.
– Maintenance plans are not supported, so we would need to use SQL Scripts to implement the maintenance tasks like backups, Index and statistics maintenance, Integrity checks, etc. There are already many of these scripts available like Ola Hellengren scripts. Using scripts also provides flexibility of dealing with these maintenance tasks.
– Linked Server access is limited and only can be configure and used from Amazon RDS to outside SQL Server instances over internet or other Amazon RDS instances.
– SQL Agent role is limited, which causes issues where one user with SQL Agent role cannot see jobs created by another user with SQL Agent role. So workaround is to use only one account for job management.
– In Amazon RDS, during the initial creation of instance, we choose the storage and this storage cannot be changed later. There are no workarounds and will need to create new instance with required storage design and then have to migrate data from old instance to new instance.
– Replication can be setup, but is is limited. We can only configure Amazon RDS instance as a subscriber. We cannot use Amazon RDS instance as publisher/distributor. We cannot use pull subscription too.
Some of the other features which are not supported by Amazon RDS SQL Server 2012 or lower include below.
Database Log Shipping
Change Data Capture (CDC) – Consider using Change Tracking as an alternative to CDC.
Additional T-SQL endpoints
Performance Data Collector
Distribution Transaction Coordinator (MSDTC)
WCF Data Services
SQL Server Audit
BULK INSERT and OPENROWSET(BULK…) features. These must be run from client-based server storage.
Data Quality Services
Instant file initialization
Always On (2012 Enterprise Edition)
Server level triggers
Hope this was helpful.
This is applicable for below versions of SQL Server
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, Frequently asked questions, SQL Server Trainings