Menu
Amazon Relational Database Service
User Guide (API Version 2014-10-31)

Microsoft SQL Server on Amazon RDS

Amazon RDS supports DB instances running several versions and editions of Microsoft SQL Server. You can use the following versions and editions:

  • SQL Server 2016

    • Version 13.0.2164.0, CU2, for all editions, and all regions except South America (São Paulo)

  • SQL Server 2014

    • Version 12.0.5000.0, SP2, for all editions and all regions

    • Version 12.0.4422.0, SP1 CU2, for all editions except Enterprise Edition, and all regions

  • SQL Server 2012

    • Version 11.0.6020.0, SP3, for all editions and all regions

    • Version 11.0.5058.0, SP2, for all editions, and all regions except US East (Ohio)

    • Version 11.0.2100.60, RTM, for all editions, and all regions except US East (Ohio)

  • SQL Server 2008 R2

    • Version 10.50.6529.00, SP3 QFE, for all editions, and all regions except US East (Ohio)

    • Version 10.50.6000.34, SP3, for all editions, and all regions except US East (Ohio)

    • Version 10.50.2789.00, SP1, for all editions, and all regions except US East (Ohio)

For information about licensing options for the different SQL Server versions and editions, see Licensing Microsoft SQL Server on Amazon RDS.

With Amazon RDS, you can create DB instances and DB snapshots, point-in-time restores, and automated or manual backups. DB instances running SQL Server can be used inside a VPC. You can also use SSL to connect to a DB instance running SQL Server, and you can use TDE to encrypt data at rest. Amazon RDS currently supports Multi-AZ deployments for SQL Server using SQL Server Mirroring as a high-availability, failover solution.

In order to deliver a managed service experience, Amazon RDS does not provide shell access to DB instances, and it restricts access to certain system procedures and tables that require advanced privileges. Amazon RDS supports access to databases on a DB instance using any standard SQL client application such as Microsoft SQL Server Management Studio. Amazon RDS does not allow direct host access to a DB instance via Telnet, Secure Shell (SSH), or Windows Remote Desktop Connection. When you create a DB instance, you are assigned to the db_owner role for all databases on that instance, and you will have all database-level permissions except for those that are used for backups (Amazon RDS manages backups for you).

Before creating your first DB instance, you should complete the steps in the setting up section of this guide. For more information, see Setting Up for Amazon RDS.

Common Management Tasks for Microsoft SQL Server on Amazon RDS

The following are the common management tasks you perform with an Amazon RDS SQL Server DB instance, with links to relevant documentation for each task.

Task AreaRelevant Documentation

Instance Classes, Storage, and PIOPS

If you are creating a DB instance for production purposes, you should understand how instance classes, storage types, and Provisioned IOPS work in Amazon RDS.

DB Instance Class

Amazon RDS Storage Types

Multi-AZ Deployments

A production DB instance should use Multi-AZ deployments. Multi-AZ deployments provide increased availability, data durability, and fault tolerance for DB instances. Multi-AZ deployments for SQL Server are implemented using SQL Server’s native Mirroring technology.

High Availability (Multi-AZ)

Multi-AZ Deployments Using Microsoft SQL Server Mirroring

Virtual Private Cloud (VPC)

If your AWS account has a default VPC, then your DB instance is automatically created inside the default VPC. If your account does not have a default VPC, and you want the DB instance in a VPC, you must create the VPC and subnet groups before you create the DB instance.

Determining Whether You Are Using the EC2-VPC or EC2-Classic Platform

Working with an Amazon RDS DB Instance in a VPC

Security Groups

By default, DB instances are created with a firewall that prevents access to them. You therefore must create a security group with the correct IP addresses and network configuration to access the DB instance. The security group you create depends on what EC2 platform your DB instance is on, and whether you will be accessing your DB instance from an EC2 instance.

In general, if your DB instance is on the EC2-Classic platform, you will need to create a DB security group; if your DB instance is on the EC2-VPC platform, you will need to create a VPC security group.

Determining Whether You Are Using the EC2-VPC or EC2-Classic Platform

Amazon RDS Security Groups

Parameter Groups

If your DB instance is going to require specific database parameters, you should create a parameter group before you create the DB instance.

Working with DB Parameter Groups

Option Groups

If your DB instance is going to require specific database options, you should create an option group before you create the DB instance.

Options for the Microsoft SQL Server Database Engine

Connecting to Your DB Instance

After creating a security group and associating it to a DB instance, you can connect to the DB instance using any standard SQL client application such as Microsoft SQL Server Management Studio.

Connecting to a DB Instance Running the Microsoft SQL Server Database Engine

Backup and Restore

When you create your DB instance, you can configure it to take automated backups. You can also back up and restore your databases manually by using full backup files (.bak files).

Working With Automated Backups

Importing and Exporting SQL Server Databases

Monitoring

You can monitor your SQL Server DB instance by using CloudWatch Amazon RDS metrics, events, and enhanced monitoring.

Viewing DB Instance Metrics

Viewing Amazon RDS Events

Log Files

You can access the log files for your SQL Server DB instance.

Amazon RDS Database Log Files

Microsoft SQL Server Database Log Files


There are also advanced administrative tasks for working with SQL Server DB instances. For more information, see the following documentation:

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:

  • The maximum number of databases on a single Microsoft SQL Server DB instance is 30.

  • 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 db.t1.micro DB instance class has limited resources and is best used for testing. For example, the db.t1.micro DB instance class doesn't have enough resources for a full implementation of SQL Server 2012 or SQL Server 2014.

  • SQL Server 2016 Standard Edition only supports up to 128 GB of memory, and can be used with the db.m4.4xlarge DB instance class.

  • SQL Server 2014 Standard Edition only supports up to 128 GB of memory, and can be used with the db.m4.4xlarge, and db.r3.4xlarge, and db.r3.8xlarge DB instance classes.

  • SQL Server 2012 Standard Edition only supports up to 64 GB of memory, and can be used with the db.m4.4xlarge, and db.r3.2xlarge DB instance classes.

  • 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 Roles and Permissions

The Microsoft SQL Server database engine uses role-based security. The master user name you use when you create a DB instance is a SQL Server Authentication login that is a member of the processadmin, public, and setupadmin fixed server roles.

Any user who creates a database will be assigned to the db_owner role for that database and will have all database-level permissions except for those that are used for backups. Amazon RDS manages backups for you.

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)

Version and Feature Support on Amazon RDS

Microsoft SQL Server 2016 Support on Amazon RDS

Amazon RDS supports the following versions of SQL Server 2016:

  • Version 13.0.2164.0, CU2, for all editions, and all regions except South America (São Paulo)

SQL Server 2016 includes many new features, such as the following:

  • Query store

  • Operational Analytics

  • Temporal tables

  • Always encrypted

  • JSON support

For the full list of SQL Server 2016 features, see What's New in SQL Server 2016 and Features Supported by the Editions of SQL Server 2016 in the Microsoft documentation.

In addition to the unsupported features of previous versions, the following Server 2016 features are not supported:

  • Stretch database

  • PolyBase

  • Backing up to Microsoft Azure Blob Storage

Microsoft SQL Server 2014 Support on Amazon RDS

Amazon RDS supports the following versions of SQL Server 2014:

  • Version 12.0.5000.0, SP2, for all editions and all regions

  • Version 12.0.4422.0, SP1 CU2, for all editions except Enterprise Edition, and all regions

In addition to supported features of SQL Server 2012, Amazon RDS supports the new query optimizer available in SQL Server 2014, and also the delayed durability feature.

In addition to the unsupported features of previous versions, Amazon RDS does not support the buffer pool extension feature of SQL Server 2014.

SQL Server 2014 supports all the parameters from SQL Server 2012 and uses the same default values. SQL Server 2014 includes one new parameter, backup checksum default. For more information, see How to enable the CHECKSUM option if backup utilities do not expose the option in the Microsoft documentation.

Microsoft SQL Server 2012 Support on Amazon RDS

Amazon RDS supports the following versions of SQL Server 2012:

  • Version 11.0.6020.0, SP3, for all editions and all regions

  • Version 11.0.5058.0, SP2, for all editions, and all regions except US East (Ohio)

  • Version 11.0.2100.60, RTM, for all editions, and all regions except US East (Ohio)

For more information about SQL Server 2012, see Features Supported by the Editions of SQL Server 2012 in the Microsoft documentation.

In addition to supported features of SQL Server 2008 R2, Amazon RDS supports the following SQL Server 2012 features:

  • Columnstore indexes (Enterprise Edition)

  • Online Index Create, Rebuild and Drop for XML, varchar(max), nvarchar(max), and varbinary(max) data types (Enterprise Edition)

  • Flexible Server Roles

  • Service Broker (note that Service Broker Endpoints are notsupported)

  • Partially Contained Databases

  • Sequences

  • Transparent Data Encryption (Enterprise Edition only)

  • THROW statement

  • New and enhanced spatial types

  • UTF-16 Support

  • ALTER ANY SERVER ROLE server-level permission

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.

  • Replication

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

Some SQL Server parameters have changed in SQL Server 2012.

  • The following parameters have been removed from SQL Server 2012: awe enabled, precompute rank, and sql mail xps. These parameters were not modifiable in SQL Server DB Instances and their removal should have no impact on your SQL Server use.

  • A new contained database authentication parameter in SQL Server 2012 supports partially contained databases. When you enable this parameter and then create a partially contained database, an authorized user's user name and password is stored within the partially contained database instead of in the master database. For more information about partially contained databases, see Contained Databases in the Microsoft documentation.

Microsoft SQL Server 2008 R2 Support on Amazon RDS

Amazon RDS supports the following versions of SQL Server 2008 R2:

  • Version 10.50.6529.00, SP3 QFE, for all editions, and all regions except US East (Ohio)

  • Version 10.50.6000.34, SP3, for all editions, and all regions except US East (Ohio)

  • Version 10.50.2789.00, SP1, for all editions, and all regions except US East (Ohio)

For more information about SQL Server 2008 R2, see Features Supported by the Editions of SQL Server 2008 R2 in the Microsoft documentation.

Amazon RDS supports the following SQL Server 2008 R2 features:

  • Core database engine features

  • SQL Server development tools:

    • Visual Studio integration

    • IntelliSense

  • SQL Server management tools:

    • SQL Server Management Studio (SMS)

    • sqlcmd

    • SQL Server Profiler (client side traces; workaround available for server side)

    • SQL Server Migration Assistant (SSMA)

    • Database Engine Tuning Advisor

    • SQL Server Agent

  • Safe CLR

  • Full-text search (except semantic search)

  • SSL

  • Transparent Data Encryption (Enterprise Edition only)

  • Spatial and location features

  • Service Broker (note that Service Broker Endpoints are not supported)

  • Change Tracking

  • Database Mirroring

  • The ability to use an Amazon RDS SQL DB instance as a data source for Reporting, Analysis, and Integration Services

Version Management

With Amazon RDS, you control when to upgrade your SQL Server instance to new versions supported by Amazon RDS. You can maintain compatibility with specific SQL Server versions, test new versions with your application before deploying in production, and perform version upgrades on your own terms and timelines.

Currently, you perform all SQL Server database upgrades manually. For more information about upgrading a SQL Server DB instance, see Upgrading the Microsoft SQL Server DB Engine.

Multi-AZ Deployments Using Microsoft SQL Server Mirroring

Amazon RDS supports Multi-AZ deployments for DB instances running Microsoft SQL Server by using SQL Server Database Mirroring. Multi-AZ deployments provide increased availability, data durability, and fault tolerance for DB instances. In the event of planned database maintenance or unplanned service disruption, Amazon RDS automatically fails over to the up-to-date standby so database operations can resume quickly without manual intervention. The primary and standby instances use the same endpoint, whose physical network address transitions to the mirror as part of the failover process. You don't have to reconfigure your application when a failover occurs.

Amazon RDS manages failover by actively monitoring your Multi-AZ deployment and initiating a failover when a problem with your primary occurs. Failover doesn't occur unless the standby and primary are fully in sync. Amazon RDS actively maintains your Multi-AZ deployment by automatically repairing unhealthy DB instances and reestablishing synchronous replication. You don't have to manage anything; Amazon RDS handles the primary, the Mirroring witness, and the standby instance for you. When you set up SQL Server Multi-AZ, all databases on the instance are mirrored automatically.

For more information, see Multi-AZ Deployments for Microsoft SQL Server with Database Mirroring.

Microsoft SQL Server SSL Support

You can use SSL to encrypt connections between your applications and your Amazon RDS DB instances running Microsoft SQL Server. SSL support is available in all AWS regions and for all supported SQL Server editions. For more information, see Using SSL with a DB Instance Runnning the Microsoft SQL Server Database Engine.

Using Transparent Data Encryption to Encrypt Data at Rest

Amazon RDS supports Microsoft SQL Server Transparent Data Encryption (TDE), which transparently encrypts stored data. Amazon RDS uses option groups to enable and configure these features. For more information about the TDE option, see Microsoft SQL Server Transparent Data Encryption Support.

Local Time Zone for Microsoft SQL Server DB Instances

The time zone of an Amazon RDS DB instance running Microsoft SQL Server is set by default. The current default is Universal Coordinated Time (UTC). You can set the time zone of your DB instance to a local time zone instead, to match the time zone of your applications.

You set the time zone when you first create your DB instance. You can create your DB instance by using the AWS Management Console, the Amazon RDS API CreateDBInstance action, or the AWS CLI create-db-instance command.

If your DB instance is part of a Multi-AZ deployment (using SQL Server Mirroring), then when you fail over, your time zone remains the local time zone that you set. For more information, see Multi-AZ Deployments Using Microsoft SQL Server Mirroring.

When you request a point-in-time restore, you specify the time to restore to in UTC. During the restore process, the time is translated to the time zone of the DB instance. For more information, see Restoring a DB Instance to a Specified Time.

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, see Importing and Exporting SQL Server Databases.

Supported Time Zones

You can set your local time zone to one of the values listed in the following table.

Time Zone

Standard Time Offset

Notes

Alaskan Standard Time

(UTC–09:00) Alaska

 

Atlantic Standard Time

(UTC–04:00) Atlantic Time (Canada)

 

AUS Central Standard Time

(UTC+09:30) Darwin

 

AUS Eastern Standard Time

(UTC+10:00) Canberra, Melbourne, Sydney

 

Belarus Standard Time

(UTC+3:00) Minsk

This time zone does not observe daylight savings time.

Canada Central Standard Time

(UTC–06:00) Saskatchewan

 

Cen. Australia Standard Time

(UTC+09:30) Adelaide

 

Central America Standard Time

(UTC–06:00) Central America

 

Central Asia Standard Time

(UTC+06:00) Astana

 

Central Brazilian Standard Time

(UTC–04:00) Manaus

 

Central Europe Standard Time

(UTC+01:00) Belgrade, Bratislava, Budapest, Ljubljana, Prague

 

Central European Standard Time

(UTC+01:00) Sarajevo, Skopje, Warsaw, Zagreb

 

Central Pacific Standard Time

(UTC+11:00) Solomon Islands, New Caledonia

 

Central Standard Time

(UTC–06:00) Central Time (US and Canada)

 

Central Standard Time (Mexico)

(UTC–06:00) Guadalajara, Mexico City, Monterrey

 

China Standard Time

(UTC+08:00) Beijing, Chongqing, Hong Kong SAR, Urumqi

 

E. Africa Standard Time

(UTC+3:00) Nairobi

This time zone does not observe daylight savings time.

E. Australia Standard Time

(UTC+10:00) Brisbane

 

E. Europe Standard Time

(UTC+02:00) Minsk

 

Eastern Standard Time

(UTC–05:00) Eastern Time (US and Canada)

 

Georgian Standard Time

(UTC+04:00) Tblisi

 

GMT Standard Time

(UTC) Dublin, Edinburgh, Lisbon, London

This time zone is not the same as Greenwich Mean Time. This time zone does observe daylight savings time.

Greenland Standard Time

(UTC–03:00) Greenland

 

Greenwich Standard Time

(UTC) Monrovia, Reykjavik

This time zone does not observe daylight savings time.

Korea Standard Time

(UTC+09:00) Seoul

 

Mountain Standard Time

(UTC–07:00) Mountain Time (US and Canada)

 

Mountain Standard Time (Mexico)

(UTC–07:00) Chihuahua, La Paz, Mazatlan

 

New Zealand Standard Time

(UTC+12:00) Auckland, Wellington

 

Pacific Standard Time

(UTC–08:00) Pacific Time (US and Canada)

 

Pacific Standard Time (Mexico)

(UTC–08:00) Baja California

 

Russian Standard Time

(UTC+3:00) Moscow, St. Petersburg, Volgograd

This time zone does not observe daylight savings time.

Singapore Standard Time

(UTC+08:00) Kuala Lumpur, Singapore

 

Tokyo Standard Time

(UTC+09:00) Osaka, Sapporo, Tokyo

 

Hawaiian Standard Time

(UTC–10:00) Hawaii

 

US Eastern Standard Time

(UTC–05:00) Indiana (East)

 

UTC

UTC

This time zone does not observe daylight savings time.

W. Australia Standard Time

(UTC+08:00) Perth

 

W. Central Africa Standard Time

(UTC+01:00) West Central Africa

 

W. Europe Standard Time

(UTC+01:00) Amsterdam, Berlin, Bern, Rome, Stockholm, Vienna