AWS -RDS limitations for Microsoft SQL Server DB Instances

Amazon RDS doesn't support running SQL Server Analysis Services, SQL Server Integration Services, SQL Server Reporting Services, Data Quality Services, or Master Data Services on the same server as your Amazon RDS DB instance

Amazon RDS does not provide shell access to DB instances, and it restricts access to certain system procedures and tables that require advanced privileges.

The following server-level roles are not currently available in Amazon RDS:

• • bulkadmin

  • dbcreator

  • diskadmin

  • securityadmin

  • serveradmin

  • sysadmin

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

  • CONTROL_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)

Limits for Microsoft SQL Server DB Instances

You can create up to 30 databases on each of your DB instances running Microsoft SQL Server

• • 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 deployment.

  • The maximum storage size for SQL Server DB instances is the following:

    • • • General Purpose (SSD) storage: 16 TiB for all editions

        • Provisioned IOPS storage: 16 TiB for all editions

        • Magnetic storage: 1 TiB for all editions

Features Not Supported and Features with Limited Support

The following Microsoft SQL Server features are not supported on Amazon RDS:

• • Stretch database

  • Backing up to Microsoft Azure Blob Storage

  • Buffer pool extension

  • BULK INSERT and OPENROWSET(BULK...) features

  • Data Quality Services

  • Database Log Shipping

  • Database Mail

  • Distribution Transaction Coordinator (MSDTC)

  • File tables

  • FILESTREAM support

  • Maintenance Plans

  • Performance Data Collector

  • Policy-Based Management

  • PolyBase

  • R

  • Replication

  • Resource Governor

  • SQL Server Audit

  • Server-level triggers

  • Service Broker endpoints

  • T-SQL endpoints (all operations using CREATE ENDPOINT are unavailable)

  • WCF Data Services

The following Microsoft SQL Server features have limited support on Amazon RDS:

• • Distributed Queries / Linked Servers. For more information, see: Implementing Linked Servers with Amazon RDS for Microsoft SQL Server.

The following are limitations to setting the local time zone on your DB instance:

• • You can't modify the time zone of an existing SQL Server DB instance.

  • You can't restore a snapshot from a DB instance in one time zone to a DB instance in a different time zone.

  • We strongly recommend that you don't restore a backup file from one time zone to a different time zone. If you restore a backup file from one time zone to a different time zone, you must audit your queries and applications for the effects of the time zone change. For more information

Limitations and Recommendations Backup and Restore:

The following are some limitations to using native backup and restore:

• • You can't back up to, or restore from, an Amazon S3 bucket in a different AWS Region than your Amazon RDS DB instance.

  • We strongly recommend that you don't restore a backup file from one time zone to a different time zone. If you restore a backup file from one time zone to a different time zone, you must audit your queries and applications for the effects of the time zone change.

  • Backing up databases larger than 1 TB in size is not supported.

  • You can't restore databases larger than 4 TB in size. Restoring databases on SQL-Server Express is limited by the MSSQL edition to 10GB or less.

  • You can't back up a database during the maintenance window, or any time Amazon RDS is in the process of taking a snapshot of the database.

  • On Multi-AZ DB instances, you can only restore databases backed up in full recovery model.

  • Calling the RDS procedures for backup/restore within a transaction is not supported.

  • Backup files are encrypted with the specified KMS key using the "Encryption-Only" crypto mode. When you are restoring encrypted backup files, you should be aware they were encrypted with the "Encryption-Only" crypto mode.

Few important points about Multi-AZ deployment for AWS RDS SQL Server

• • AWS allows a single secondary copy of the RDS instance in a similar region

  • We cannot configure Multi-AZ deployment for standby RDS instance in a different region

  • We cannot access the stand by replica databases

  • Databases should be in full recovery model for Multi-AZ deployment

  • AWS automatically replicates users, logins and permissions on standby replica. No user intervention required

  • RDS maintains a synchronized copy of databases in another availability zone. In synchronized mode, it waits the transaction commit acknowledgment from the standby database and then commits on primary or principal database copy. We might face latency due to synchronous commit in comparison with stand-alone database copy

  • We need to replicate SQL Server agent jobs manually on a standby instance. AWS RDS does not take care of it

  • Usually, we have a 1-2 minute of RDS instance failover including database recovery on a new primary replica. It might take longer depending upon database size, the number of active transactions, recovery process (undo, redo) efforts required