AWS-RDS SQL Server limitations

29 July,2021 by Tom Collins

Some AWS  RDS SQL Server limitations

 

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

- Amazon RDS for SQL Server doesn't support importing data into the msdb database.

- You can't rename databases on a DB instance in a SQL Server Multi-AZ deployment.

-  AWS RDS doesn't support Data Quality Services and Master Data Services on the same RDS service, need to spin up an EC2 and run services form another server

-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

 

SQL Server security limitations

The following server-level roles aren't available in Amazon RDS for SQL Server:

  • bulkadmin

  • dbcreator

  • diskadmin

  • securityadmin

  • serveradmin

  • sysadmin

The following server-level permissions aren't available on RDS for SQL Server DB instances:

  • ALTER ANY CREDENTIAL

  • ALTER ANY DATABASE

  • ALTER ANY EVENT NOTIFICATION

  • ALTER RESOURCES

  • ALTER SETTINGS

  • AUTHENTICATE SERVER

  • CONTROL_SERVER

  • CREATE DDL EVENT NOTIFICATION

  • CREATE ENDPOINT

  • CREATE TRACE EVENT NOTIFICATION

  • DROP ANY DATABASE

  • 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 of databases on database instances for a Single-AZ- depends on instance class type

db.*.micro to db.*.medium   30 

db.*.large   30 

db.*.xlarge to db.*.16xlarge  100

db.*.24xlarge              100

 

Features not supported and features with limited support

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

  • Backing up to Microsoft Azure Blob Storage

  • Buffer pool extension

  • Custom password policies

  • Data Quality Services

  • Database Log Shipping

  • Database snapshots (Amazon RDS supports only DB instance snapshots)

  • Extended stored procedures, including xp_cmdshell

  • FILESTREAM support

  • File tables

  • Machine Learning and R Services (requires OS access to install it)

  • Maintenance plans

  • Performance Data Collector

  • Policy-Based Management

  • PolyBase

  • Replication

  • Resource Governor

  • Server-level triggers

  • Service Broker endpoints

  • Stretch database

  • 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,

 

AWS SQL Server Backup & Restore limitations

- You can't restore a snapshot from a DB instance in one time zone to a DB instance in 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

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

- You can't restore a database with the same name as an existing database. Database names are unique.

- Amazon S3 has a size limit of 5 TB per file. For native backups of larger databases, you can use multifile backup.

- You can't back up to or restore from more than 10 backup files at the same time.

 - A differential backup is based on the last full backup. For differential backups to work, you can't take a snapshot between the last full backup and the differential backup. If you want a differential backup, but a manual or automated snapshot exists, then do another full backup before proceeding with the differential backup.

 - You can't perform native log backups from SQL Server on Amazon RDS.

-You can create native backups of Transparent Data Encryption (TDE)-enabled databases, but you can't restore those backups to on-premises databases. You can't restore native backups of TDE-enabled, on-premises databases.

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


Author: Tom Collins (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on AWS-RDS SQL Server limitations


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer