SQL Server on the AWS Cloud
AWS provides the option to run Microsoft SQL Server in a cloud environment. For developers and database
administrators, running SQL Server in the AWS Cloud is similar to running SQL Server databases in a
data center. There are three primary options to run SQL Server on AWS:
-
Microsoft SQL Server on Amazon Elastic Compute Cloud (Amazon EC2)
-
Amazon RDS for Microsoft SQL Server
-
Amazon RDS Custom for Microsoft SQL Server
Your application requirements, database features, functionality, growth capacity, and
overall architecture complexity will determine which option to choose. If you are migrating
multiple SQL Server databases to AWS, some of them might be a great fit for Amazon RDS, whereas others
might be better suited to run directly on Amazon EC2. You might have databases that are running on
SQL Server Enterprise edition but are a good fit for SQL Server Standard edition. You may also want to
modernize your SQL Server database running on Windows to run on a Linux operating system to save on
cost and licenses. Many AWS customers run multiple SQL Server database workloads across Amazon RDS and
Amazon EC2.
Microsoft SQL Server on Amazon EC2
When to choose Microsoft SQL Server on Amazon Elastic Compute Cloud (Amazon EC2):
-
You want full control over the database and access to its underlying operating system,
database installation, and configuration.
-
You want to administer your database, including backups and recovery, patching the
operating system and the database, tuning the operating system and database parameters,
managing security, and configuring high availability or replication.
-
You want to use features and options that aren’t currently supported by Amazon RDS. For more
information, see Features not supported and features with limited support in the Amazon RDS
documentation.
-
You require a specific SQL Server version that isn’t supported by Amazon RDS. For a list of
supported versions and editions, see SQL Server versions on Amazon RDS in the RDS for Microsoft SQL Server User Guide.
-
Your database size and performance requirements exceed the current RDS for Microsoft SQL Server offerings.
For more information, see Amazon RDS DB instance storage in the
Amazon RDS User Guide.
-
You want to avoid automatic software patches that might not be compliant with your
applications.
-
You want to bring your own license instead of using the RDS for Microsoft SQL Server license-included
model.
-
You want to achieve higher IOPS and storage capacity than the current limits. For more
information, see Amazon RDS DB instance storage in the Amazon RDS User Guide.
Amazon RDS for Microsoft SQL Server
RDS for Microsoft SQL Server is a managed database service that simplifies the provisioning and management
of SQL Server on AWS. With Amazon RDS, you can quickly deploy multiple versions and editions of SQL Server ,
with cost-efficient and resizeable compute capacity. You can provision Amazon RDS for SQL Server DB
instances with either General Purpose SSD or Provisioned IOPS SSD storage. Provisioned IOPS SSD
is optimized for I/O-intensive, transactional (OLTP) database workloads.
Amazon RDS manages database administration tasks, including provisioning, backups, software
patching, monitoring, and hardware scaling. Amazon RDS also offers Multi-AZ deployments and read
replicas (for SQL Server Enterprise edition) to provide high availability, performance,
scalability, and reliability for production workloads. For more information, see Amazon RDS for Microsoft SQL Server.
When to choose RDS for Microsoft SQL Server:
-
You want to focus on your business and applications, and you want AWS to take care of
undifferentiated heavy lifting tasks, such as the provisioning of the database, management of
backup and recovery tasks, management of security patches, minor SQL Server version upgrades, and
storage management.
-
You want a highly available database solution, and you want to take advantage of the
push-button, synchronous Multi-AZ replication offered by Amazon RDS, without having to manually set
up and maintain database mirroring, failover clusters, or Always On availability
groups.
-
You want to pay for the SQL Server license as part of the instance cost on an hourly basis,
instead of making a large, up front investment.
-
Your database size and IOPS requirements are supported by Amazon RDS for SQL Server. See Amazon RDS DB Instance
Storage in the AWS documentation for the current maximum limits.
-
You don’t want to manage backups or point-in-time recoveries of your database.
-
You want to focus on high-level tasks, such as performance tuning and schema
optimization, instead of the daily administration of the database.
-
You want to scale the instance type up or down based on your workload patterns without
being concerned about licensing complexities.
Amazon RDS Custom for SQL Server
Amazon RDS Custom for SQL Server is a managed database service for legacy, custom, and packaged
applications that require access to the underlying operating system and database
environment. Amazon RDS Custom for SQL Server automates setup, operation, and scaling of databases in
the AWS Cloud while granting you access to the database and underlying operating system on
Amazon EC2 to configure settings, install patches, and enable native features to meet the
dependent application's requirements. For more information, see Working with RDS
Custom for SQL Server in the Amazon Relational Database Service User
Guide.
When to choose Amazon RDS Custom for SQL Server:
-
You want the benefits of Amazon RDS, but your requirements include the need to customize
the underlying operating system and database environment for legacy, custom, and
packaged applications.
-
You need administrative rights to the database and underlying operating
system.
-
You need to install custom database and OS patches and packages.
-
You need to configure file systems to share files directly with their
applications.
Decision matrix
The following table provides a side-by-side comparison of SQL Server features supported on
Amazon RDS, Amazon RDS Custom, and Amazon EC2. Use this information to understand the differences between
these services, and to choose the best approach for your use case. For the most current
information for Amazon RDS, see Microsoft SQL Server on Amazon RDS and Working with RDS Custom for SQL Server in the Amazon RDS User
Guide.
- Development
-
Development feature |
Amazon RDS |
Amazon RDS Custom |
Amazon EC2 |
Notes |
Buffer pool extensions |
No |
Yes
|
Yes
|
|
BULK INSERT
|
Yes
|
Yes
|
Yes
|
See Integrating an Amazon RDS for SQL Server DB instance with Amazon S3 in the
Amazon RDS documentation. |
Change data capture (CDC) |
Yes (Enterprise Edition: all versions;
Standard Edition: 2016 SP1 and later) |
Yes
|
Yes
|
See Using change data capture in the Amazon RDS
documentation. |
Change tracking |
Yes
|
Yes
|
Yes
|
|
Columnstore indexes |
Yes
|
Yes (Enterprise Edition: 2019) |
Yes (Enterprise Edition) |
|
Data Quality Services |
No |
Yes
|
Yes |
|
Database Mail |
Yes
|
Yes
|
Yes |
See the blog post Using
Database Mail on Amazon RDS for SQL Server.
We encourage you to use the Amazon Simple Email Service
(Amazon SES) to send outbound email originating from AWS resources, to
ensure a high degree of deliverability. |
Database Engine Tuning Advisor |
Yes |
Yes
|
Yes |
|
DB event notifications |
Yes
|
Yes
|
No (manually track and manage DB
events) |
See Using Amazon RDS event
notification in the Amazon RDS documentation. |
DDL event notifications |
No |
Yes
|
Yes |
|
Delayed transaction durability (lazy commit) |
Yes |
Yes (SQL Server 2019)
|
Yes
|
|
Distributed queries |
Yes (SQL Server targets) |
Yes (SQL Server targets)
|
Yes (SQL Server targets) |
See the Implementing linked servers with Amazon RDS for SQL Server blog
post. |
Extended stored procedures, including
xp_cmdshell |
No |
Yes
|
Yes |
|
File tables |
No |
Yes
|
Yes |
|
FILESTREAM
|
No |
Yes
|
Yes |
FILESTREAM isn't compatible with Amazon RDS. However, you can
configure the in-memory database.
|
Full-text search |
Yes (except semantic search) |
Yes
|
Yes |
|
In-memory database |
Yes |
Yes (SQL Server 2019)
|
Yes
|
|
Linked servers |
Yes (SQL Server targets) |
Yes
|
Yes |
See the Implementing linked servers with Amazon RDS for SQL Server blog
post. |
Machine Learning Services (with R scripts) |
No |
Yes
|
Yes |
Machine Learning Services must be installed separately on a Windows
or Linux machine. It's supported on an Always On Failover Cluster Instance (FCI) only in SQL Server 2019
and later.
Although R isn't supported on Amazon RDS, you can use it on AWS (see the blog
post Getting
started with R on AWS). |
Maintenance plans |
No |
Yes
|
Yes |
Amazon RDS provides a separate set of features to facilitate backup and
recovery of databases. For backup, you can configure automated
backup. |
Master Data Services |
No |
Yes
|
Yes |
|
Microsoft Distributed Transaction Coordinator (MSDTC) |
Yes |
Yes
|
Yes |
See the blog post Enabling distributed transaction support for domain-joined Amazon RDS for SQL
Server instances. |
OPENROWSET
|
Yes |
Yes
|
Yes |
|
Partially contained databases |
Yes |
Yes (SQL Server 2019)
|
Yes
|
|
Performance Data Collector |
No |
Yes
|
Yes |
On Amazon RDS, you can use Amazon CloudWatch, AWS CloudTrail, and Performance Insights to
monitor your SQL Server performance (see Overview of
monitoring Amazon RDS in the Amazon RDS documentation). |
Policy-Based Management |
No |
Yes
|
Yes |
|
PolyBase |
No |
Yes
|
Yes |
|
Resource Governor |
No |
Yes |
Yes |
|
Safe CLR |
Yes |
Yes |
Yes |
|
Server-level triggers |
No |
Yes |
Yes |
|
Service Broker |
Yes (except endpoints) |
Yes |
Yes |
|
Spatial and location features |
Yes |
Yes |
Yes |
|
SQL Server Agent |
Yes |
Yes |
Yes |
|
SQL Server Analysis Services (SSAS) |
Yes (SQL Server 2016 and later) |
Yes |
Yes |
See Support for SSAS in Amazon RDS for SQL Server in the Amazon RDS
documentation. |
SQL Server Integration Services (SSIS) |
Yes (SQL Server 2016 and later) |
Yes |
Yes |
See Support for SSIS in Amazon RDS for SQL Server in the Amazon RDS
documentation. |
SQL Server Management Studio (SSMS) |
Yes |
Yes |
Yes |
|
SQL Server Profiler |
Yes (server-side and client-side
traces) |
Yes |
Yes |
|
SQL Server Reporting Services (SSRS) |
Yes (SQL Server 2016 and later) |
Yes |
Yes (Server 2022 requires a manual
installation of SSRS) |
See Support for SSRS in Amazon RDS for SQL Server in the Amazon RDS
documentation. |
sqlcmd
|
Yes |
Yes |
Yes |
|
Stretch Database |
No |
Yes |
Yes |
|
THROW statement
|
Yes
|
Yes (SQL Server 2019) |
Yes
|
|
Transact-SQL endpoints |
No |
Yes |
Yes |
All operations that use CREATE ENDPOINT are unavailable
on Amazon RDS. We recommend that you install SQL Server on an EC2 instance for
these operations. |
UTF-16 support |
Yes
|
Yes |
Yes
|
|
WCF Data Service |
No |
Yes |
Yes |
|
- HA/DR
-
Development feature |
Amazon RDS |
Amazon RDS Custom |
Amazon EC2 |
Notes |
Always On availability groups |
Yes |
Yes (for migration purposes in a single
Availability Zone only) |
Yes |
If you need a self-managed Always On availability group, we recommend
that you use AWS Launch Wizard to simplify SQL Server HA deployment on an EC2 instance.
See AWS Launch Wizard for SQL
Server in the AWS documentation.
|
Always On Failover Cluster Instances (FCIs) |
No |
Yes |
Yes |
You can use AWS Launch Wizard to simplify your SQL Server FCI deployment on Amazon EC2. See AWS Launch Wizard for SQL Server in the AWS documentation.
|
Backing up to Amazon S3 |
Yes |
Yes |
Yes |
Amazon RDS supports native backup and restore for SQL Server databases by
using full backup files (.bak files) and Amazon S3 as a repository. See Importing and Exporting SQL Server databases in the Amazon RDS
documentation. |
BACKUP command
|
No |
Yes |
Yes |
See How do I
perform native backups of an Amazon RDS DB instance that's running SQL
Server? in AWS Knowledge Center. |
Database mirroring |
Yes (Multi-AZ) |
Yes |
Yes |
|
Database replication |
No (limited push subscription) |
Yes |
Yes |
If you want to replicate a single table on Amazon RDS, you can also use
AWS DMS or set up read
replicas. |
Distributed availability groups |
No |
Yes |
Yes |
|
Log shipping |
No |
Yes |
Yes |
For disaster recovery purposes, you can use read replicas or AWS DMS. |
Managed automated backups |
Yes |
Yes |
No (requires configuring and managing
maintenance plans, or using third-party solutions) |
See Working with backups in the Amazon RDS documentation. |
Multi-AZ with automated failover |
Yes |
Yes (with manual configuration of Always On
availability groups) |
Yes (Enterprise Edition only, with manual
configuration of Always On availability groups) |
See Multi-AZ
deployments for Amazon RDS for SQL Server in the Amazon RDS
documentation. |
Read replicas |
Yes (SQL Server 2016 and later) |
Yes (with manual configuration of Always On
availability groups) |
Yes (with manual configuration of Always On
availability groups) |
|
RESTORE command
|
Yes |
Yes |
Yes |
See AWS
Knowledge Center. |
- Scalability
-
Development feature |
Amazon RDS |
Amazon RDS Custom |
Amazon EC2 |
Notes |
Built-in instance and database monitoring and metrics |
Yes |
No |
No (export your own metrics to CloudWatch or use a
third-party solution) |
See the blog post Monitor your SQL Server database by using custom metrics with Amazon CloudWatch and
AWS Systems Manager. |
Configurable storage size |
Yes |
No |
Yes |
|
Maximum number of databases per instance |
Depends on the instance size and Multi-AZ
configuration |
5,000 |
32,767
|
See Maximum capacity specifications for SQL Server in the Microsoft SQL
Server documentation. |
Maximum storage size of a DB instance |
16 TiB |
16 TiB |
No limitation |
Amazon RDS also supports tempdb databases on local disks by using
Non-Volatile Memory Express (NVMe) instance storage. See Instance store
support for the tempdb database on Amazon RDS for SQL Server in the Amazon RDS
documentation. |
Minimum storage size of a DB instance |
20 GiB (Enterprise, Standard, Web, and Express
Editions) |
20 GiB (Enterprise, Standard, Web, and Express
Editions) |
No limitation |
|
New Query Optimizer |
Yes
|
Yes
|
Yes
|
|
Read replicas |
Yes (SQL Server 2016 and later) |
Yes (with manual configuration of Always On
availability groups) |
Yes (with manual configuration of Always On
availability groups) |
|
- Security
-
- Other features
-
Development feature |
Amazon RDS |
Amazon RDS Custom |
Amazon EC2 |
Notes |
Ability to install a third-party agent |
No |
Yes |
Yes |
|
Ability to rename existing databases |
Yes (Single-AZ only) |
Yes (not available for databases in
availability groups or enabled for mirroring) |
Yes (not available for databases in
availability groups or enabled for mirroring) |
For Multi-AZ deployments on Amazon RDS, see Renaming a Microsoft SQL Server database in a Multi-AZ deployment
in the Amazon RDS documentation. |
Control over DB instance and operating system |
No |
Yes |
Yes |
|
Custom set time zones |
Yes |
No |
Yes |
|
Distributed Replay |
No |
Yes |
Yes |
The SQL Server Distributed Replay client service
requires
sysadmin permissions, which is why it isn't supported in Amazon RDS. |
Import data into the msdb database |
No |
Yes |
Yes |
|
Installation methods |
N/A |
N/A |
Amazon Machine Image (AMI) or manual installation |
|
SQL Server editions |
Enterprise, Standard, Web, Express |
Enterprise, Standard, Web |
Enterprise, Standard, Web, Developer, Express |
|
SQL Server versions |
2014, 2016, 2017, 2019 |
2019 |
2014, 2016, 2017, 2019, 2022 |
|