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 editions of Microsoft SQL Server 2008 R2, SQL Server 2012, and SQL Server 2014. 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 a DB instance, you should complete the steps in the Setting Up for Amazon RDS section of this guide.

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

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.

  • Databases on a DB instance in a SQL Server Multi-AZ with Mirroring deployment cannot be renamed.

  • The minimum storage size for a Microsoft SQL Server DB Instance is 20 GB for the Microsoft SQL Server Express and Web Editions and 200 GB for the Standard and Enterprise Editions.

  • The maximum storage size for a Microsoft SQL Server DB Instance is 4 TB for all instances except the SQL Server Express edition, which limits storage to a total of 300 GB.

    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 does not 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 will need to export the data, create a new DB Instance with increased storage, and then import the data into the new DB Instance. For more information, see Importing and Exporting SQL Server Databases.

  • A newly created SQL Server DB instance does not contain a database. The instance has one master user account with the name and password you specified when you created the DB instance that you can use to create users and grant permissions. You must use a SQL Server tool such as SQL Server Management Studio to log in as the master user, and then use SQL Server commands and SQL statements to add the users and elements required by your applications to store and retrieve data in the DB instance.

  • To import SQL Server data into a DB instance, follow the information in the Importing and Exporting SQL Server Databases section.

  • Because of limitations in Microsoft SQL Server, restoring to a point in time before successful execution of a DROP DATABASE may not reflect the state of that database at that point in time. For example, the dropped database will typically be restored to its state up to 5 minutes before the DROP DATABASE command was issued, which means that you will not be able to 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. Note that dropping a database removes the transaction logs for that database.

  • 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 does not have enough resources for a full implementation of SQL Server 2012 or SQL Server 2014.

  • While Amazon RDS doesn't support some features of SQL Server, you can run SQL Server components in an Amazon EC2 instances with EBS storage, pursuant to Microsoft licensing policies. This includes components such as SQL Server Analysis Services, SQL Server Integration Services, SQL Server Reporting Services, Data Quality Services, and Master Data Services.

  • System time is maintained in UTC. We do not support changing the timezone for RDS SQL Server DB instances. Depending on your scenario, you may be able to use the datetimeoffset data type to convert system time (UTC) to your local time zone. For more information on the datetimeoffset data type, see Date and Time Functions in the Microsoft SQL Server documentation.

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

Microsoft SQL Server 2014 Support on Amazon RDS

Amazon RDS supports Microsoft SQL Server 2014 SP1 CU2 ( 12.00.4422.0.v1. 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.

SQL Server Web, SQL Server Standard, and SQL Server Express editions are supported. SQL Server Enterprise Edition is currently not supported. SQL Server 2014 Standard Edition works with up to 128 GB of memory and can be used with the db.r3.4xlarge and db.r3.8xlarge DB instance classes.

In addition to the unsupported features of SQL Server 2012, the following SQL Server 2014 features are not supported:

  • Buffer pool extension

  • All Enterprise Edition only features are not supported

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 on the backup checksum parameter, see this Microsoft support page.

Upgrading to SQL Server 2014 on Amazon RDS

You can upgrade the following SQL Server versions to SQL Server 2014 SP1 CU2 (12.00.4422.0.v1):

  • 11.00.6020.0.v1 (SQL Server 2012 SP3)

  • 11.00.5058.0.v1 (SQL Server 2012 SP2)

  • 11.00.2100.60.v1 (SQL Server 2012 RTM)

  • 10.50.6529.0.v1 (SQL Server 2008 R2 SP3)

  • 10.50.6000.34.v1 (SQL Server 2008 R2 SP3)

  • 10.50.2789.0.v1 (SQL Server 2008 R2 SP1)

You can perform an in-place upgrade to SQL Server 2014 using the AWS console, AWS CLI, or Amazon RDS API by modifying the DB instance. Select the SQL Server 2014 as the new DB engine.

When you upgrade to SQL Server 2014 from a SQL Server 2012 or SQL Server 2008 R2, you should be aware that all existing databases will remain at their original compatibility level. For example, if you upgrade from SQL Server 2012, all existing databases will have a compatibility level of 110, the compatibility level of SQL Server 2012. Any new database created after the upgrade has compatibility level 120, the compatibility level of SQL Server 2014. You can alter the compatibility level of a database using the ALTER DATABASE command.

For example, to change a database named customeracct to be compatible with SQL Server 2014 (compatibility level 120), issue the following command:

ALTER DATABASE  customeracct  SET COMPATIBILITY_LEVEL = 120  

Microsoft SQL Server 2012 Support on Amazon RDS

Amazon RDS supports versions 11.00.2100.60.v1 (RTM), 11.00.5058.0.v1 (SP2), and 11.00.6020.0.v1 (SP3) of Microsoft SQL Server 2012. For more information on SQL Server 2012, see Features Supported by the Editions of SQL Server.

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 not supported)

  • 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, go to Contained Databases.

Microsoft SQL Server 2008 R2 Support on Amazon RDS

Amazon RDS supports versions 10.50.2789.0.v1 (SP2), 10.50.6000.34.v1 (SP3), and 10.50.6529.0.v1 (SP3) of Microsoft SQL Server 2008 R2. For a complete list of features supported by the 2008 R2 SQL Server database engine, go to Features Supported by the Editions of SQL Server.

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

Microsoft SQL Server Licensing

There are two licensing options available for Amazon RDS for Microsoft SQL Server; License Included and Bring Your Own License (BYOL). After you create a SQL Server DB instance on Amazon RDS, you can change the licensing model by using the AWS Management Console, the Amazon RDS API ModifyDBInstance action, or the AWS CLI modify-db-instance command.

In accordance with Microsoft’s usage rights, SQL Server Web Edition can be used only to support public and Internet-accessible web pages, websites, web applications, and Web services. For more information, go to AWS Service Terms.

The secondary instance of a SQL Server Multi-AZ deployment is passive and does not take writes or provide reads until a failover occurs. Therefore, you don't need a license for this secondary instance. For more information, see Multi-AZ Deployments Using Microsoft SQL Server Mirroring.

License Included

In the License Included model, you don't need to purchase SQL Server licenses separately; AWS holds the license for the SQL Server database software. License Included pricing includes the software license, underlying hardware resources, and Amazon RDS management capabilities.

The License Included model is supported on Amazon RDS for the following Microsoft SQL Server database editions:

  • Microsoft SQL Server Enterprise Edition (2012, 2008 R2)

    • US West (Oregon), US East (N. Virginia), and EU (Ireland) regions only.

    • R3.2xlarge, R3.4xlarge, and R3.8xlarge DB instance classes only.

  • Microsoft SQL Server 2012 Standard Edition (2014, 2012, 2008 R2)

  • Microsoft SQL Server Web Edition (2014, 2012, 2008 R2)

  • Microsoft SQL Server 2012 Express Edition (2014, 2012, 2008 R2)

Bring Your Own License (BYOL)

Microsoft’s License Mobility program allows Microsoft customers to easily move current on-premises Microsoft Server application workloads to Amazon Web Services (AWS), without any additional Microsoft software license fees. This benefit is available to Microsoft Volume Licensing (VL) customers with eligible server applications covered by active Microsoft Software Assurance (SA) contracts. Refer to Microsoft’s Product Use Rights for the latest licensing terms.

For more information about License Mobility, see SQL Server License Requirements for Microsoft LicenseMobility .

The Bring Your Own License model is supported on Amazon RDS for the following Microsoft SQL Server database editions:

  • Microsoft SQL Server Enterprise Edition (2012, 2008 R2)

  • Microsoft SQL Server Standard Edition (2014, 2012, 2008 R2)

Restoring License-Terminated DB Instances

Microsoft has requested that some Amazon RDS customers who did not report their Microsoft License Mobility information terminate their DB instance. Amazon RDS takes snapshots of these DB instances, and you can restore from the snapshot to a new DB instance that has the License Included model.

You can restore from a snapshot of SQL Server Enterprise Edition to either Enterprise Edition or Standard Edition. You can restore from a snapshot of SQL Server Standard Edition to either Standard Edition or Enterprise Edition.

To restore from a SQL Server snapshot after Amazon RDS has created a final snapshot of your instance:

  1. Sign in to the AWS Management Console and open the Amazon RDS console at https://console.aws.amazon.com/rds/.

  2. In the navigation pane, choose Snapshots.

  3. Choose the snapshot of your SQL Server DB instance. Amazon RDS created a final snapshot of your DB instance; the name of the terminated instance snapshot is in the format: '<name of instance>-final-snapshot'. For example, if your DB instance name was mytest.cdxgahslksma.us-east-1.rds.com, the final snapshot would be called mytest-final-snapshot and would be located in the same region as the original DB instance.

  4. Choose Restore Snapshot.

    The Restore DB Instance window appears.

  5. For License Model choose license-included.

  6. Choose the SQL Server DB engine you want to use.

  7. In the DB Instance Identifier text box type the name for the restored DB instance.

  8. Choose Restore DB Instance.

For more information about restoring from a snapshot, see Restoring From a DB Snapshot.

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 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 will automatically fail 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, or set up multiple endpoints, when a failover occurs.

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

Amazon RDS supports Multi-AZ with Mirroring for the following SQL Server versions and editions:

  • SQL Server 2014: SQL Server Standard Edition

  • SQL Server 2012: SQL Server Standard and Enterprise Editions

  • SQL Server 2008 R2: SQL Server Standard and Enterprise Editions

Amazon RDS supports Multi-AZ with Mirroring for SQL Server in the following AWS Regions:

  • US East (N. Virginia)

  • US West (Oregon)

  • Asia Pacific (Seoul)

  • Asia Pacific (Sydney) – Supported only for DB instances in VPCs.

  • Asia Pacific (Tokyo) – Supported only for DB instances in VPCs.

  • EU (Ireland)

  • South America (São Paulo) – Not supported on m1 or m2 DB instances.

Database Engine Version Management

With Amazon RDS, you can 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, SQL Server database major and minor version upgrades must be done manually. For more information about upgrading a SQL Server DB instance, see Upgrading the SQL Server DB Engine.

Note

Amazon RDS periodically aggregates official Microsoft SQL Server database patches and assigns an Amazon RDS-specific DB Engine version. The current supported versions are SQL Server 2008 R2 SP2 and SP3, SQL Server 2012 RTM, SP2, and SP3, and SQL Server 2014 RTM.

Upgrading from 2008 R2 to 2012

Amazon RDS supports major version upgrades from Microsoft SQL Server 2008 R2 to SQL Server 2012. You perform the upgrade by using the Amazon RDS modify DB instance operation. You should thoroughly test any major version upgrade before upgrading your production instances. For information about upgrading a DB instance, see DB Instance Maintenance and Upgrades

Upgrading to SQL Server 2014 on Amazon RDS

You can upgrade the following SQL Server versions to SQL Server 2014 SP1 CU2 (12.00.4422.0.v1):

  • 11.00.6020.0.v1 (SQL Server 2012 SP3)

  • 11.00.5058.0.v1 (SQL Server 2012 SP2)

  • 11.00.2100.60.v1 (SQL Server 2012 RTM)

  • 10.50.6529.0.v1 (SQL Server 2008 R2 SP3)

  • 10.50.6000.34.v1 (SQL Server 2008 R2 SP3)

  • 10.50.2789.0.v1 (SQL Server 2008 R2 SP1)

You can perform an in-place upgrade to SQL Server 2014 using the AWS console, AWS CLI, or Amazon RDS API by modifying the DB instance. Select the SQL Server 2014 as the new DB engine.

When you upgrade to SQL Server 2014 from a SQL Server 2012 or SQL Server 2008 R2, you should be aware that all existing databases will remain at their original compatibility level. For example, if you upgrade from SQL Server 2012, all existing databases will have a compatibility level of 110, the compatibility level of SQL Server 2012. Any new database created after the upgrade will have compatibility level 120, the compatibility level of SQL Server 2014. You can alter the compatibility level of a database using the ALTER DATABASE command.

For example, to change a database named customeracct to be compatible with SQL Server 2014 (compatibility level 120), issue the following command:

ALTER DATABASE  customeracct  SET COMPATIBILITY_LEVEL = 120  

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 a SQL Server DB instance:

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

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

Some DB engines offer additional features that make it easier to manage data and databases, and to provide additional security for your database. Amazon RDS supports Microsoft SQL Server Transparent Data Encryption (TDE), which transparently encrypts stored data for SQL Server 2008 R2 Enterprise Edition and SQL Server 2012 Enterprise Edition. 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.