Amazon Relational Database Service
User Guide (API Version 2014-10-31)
Did this page help you?  Yes | No |  Tell us about it...
« PreviousNext »
View the PDF for this guide.Go to the AWS Discussion Forum for this product.Go to the Kindle Store to download this guide in Kindle format.

Microsoft SQL Server on Amazon RDS

Amazon RDS supports DB instances running several editions of Microsoft SQL Server 2008 R2 and SQL Server 2012. 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 SQL Server on Amazon RDS

These are the common management tasks you perform with an Amazon RDS SQL Server DB instance, with links to information about each task:

  • For planning information, such as SQL Server versions, storage engines, security, and features supported in Amazon RDS, see Planning Your SQL Server DB Instance on Amazon RDS.

  • If you are creating a DB instance for production purposes, you should understand how instance classes, storage, and Provisioned IOPS work in Amazon RDS. For more information about DB instance classes, see DB Instance Class For more information about Amazon RDS storage, see Amazon RDS Storage Types. For more information about Provisioned IOPS, see Amazon RDS Provisioned IOPS Storage to Improve Performance.

  • A production DB instance should also use Multi-AZ deployments. All Multi-AZ deployments provide increased availability, data durability, and fault tolerance for DB instances. Multi-AZ deployments for SQL Server is implemented using SQL Server’s native Mirroring technology. For more information about Multi-AZ deployments, see High Availability (Multi-AZ). For more information on SQL Server's Multi-AZ using Mirroring, see Planning your Multi-AZ Deployments Using SQL Server Mirroring.

  • There are prerequisites you must complete before you create your DB instance. For example, DB instances are created by default with a firewall that prevents access to it. You therefore must create a security group with the correct IP addresses and network configuration you will use to access the DB instance. The security group you need to create will depend on what EC2 platform your DB instance is on, and whether you will be accessing your DB instance from an EC2 instance. For more information about the two EC2 platforms supported by Amazon RDS, EC2-VPC and EC2-Classic, see Determining Whether You are Using the EC2-VPC or EC2-Classic Platform. 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. For more information about security groups, see Amazon RDS Security Groups or the Setting Up for Amazon RDS section of this guide.

  • If your AWS account has a default VPC (a default virtual private network), then your DB instance will automatically be created inside the default VPC. If your account does not have a default VPC and you want the DB instance to be inside a VPC, you must create the VPC and subnet groups before you create the DB instance. For more information about determining if your account has a default VPC, see Determining Whether You are Using the EC2-VPC or EC2-Classic Platform. For more information about using VPCs with Amazon RDS, see Using Amazon RDS with Amazon Virtual Private Cloud (VPC).

  • If your DB instance is going to require specific database parameters or options, you should create the parameter or option groups before you create the DB instance. For more information on parameter groups, see Working with DB Parameter Groups. For more information on options for SQL Server, see Appendix: Options for SQL Server Database Engine.

  • 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. For more information on connecting to a DB instance, see Connecting to a DB Instance Running the SQL Server Database Engine.

  • You can configure your DB instance to take automated backups, or take manual snapshots, and then restore instances from the backups or snapshots. For information, see Backing Up and Restoring.

  • You can monitor an instance through actions such as viewing the SQL Server logs, CloudWatch Amazon RDS metrics, and events. For information, see Monitoring Amazon RDS.

There are also several appendices with useful information about working with Amazon RDS SQL Server DB instances:

Planning Your SQL Server DB Instance on Amazon RDS

You can chose the version of SQL Server you want to have on your DB instance. Amazon RDS supports DB instances running several editions of Microsoft SQL Server 2008 R2 and SQL Server 2012. You should also be aware of the limits for SQL Server DB instances.

General Limits for SQL Server DB Instances

The Amazon RDS implementation of 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 cannot be renamed.

  • The maximum storage size for a Microsoft SQL Server DB Instance is 1024 GB 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, go to the RDS SQL Server Data Migration Guide.

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

  • 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 Data section. You cannot use the BACKUP and RESTORE commands to import data into a DB instance because Amazon RDS does not allow OS-level access that would enable you to place files in a physical location that the database engine could access. You also cannot import data using the Copy Database Wizard in SQL Server Management Studio because the tool requires sysadmin privilege on the source and destination servers and this permission is not available to the master user account for a DB instance.

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

  • 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 use case, 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 cannot be used when creating a DB instance.

Support for SQL Server Features on Amazon RDS

The following list shows a subset of the key database engine features that are currently supported by the 2008 R2 version of SQL Server. 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.

  • 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

  • Change Tracking

  • Database Mirroring

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

In addition to the features above, the following list shows a subset of the key database engine features that are currently supported by the 2012 version of SQL Server. For more information on SQL Server 2012, see What's New in SQL Server 2012.

  • 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

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

  • Service Broker

  • Database Log Shipping

  • Windows Authentication

  • 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

  • Additional T-SQL endpoints

  • Distribution Transaction Coordinator (MSDTC)

  • WCF Data Services

  • FILESTREAM support

  • Policy-Based Management

  • SQL Server Audit

  • BULK INSERT and OPENROWSET(BULK...) features. These must be run from client-based server storage.

  • Data Quality Services

  • Always On (2012 Enterprise Edition)

  • File Tables

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.

SQL Server Licensing

Currently, Amazon RDS offers two licensing options for SQL Server, License Included and License Mobility (Bring Your Own License). This section explains each.


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.

License Included

Amazon RDS uses the License Included service model for DB Instances running the Microsoft SQL Server Express Edition, Microsoft SQL Server Web Edition, and Microsoft SQL Server Standard Edition (SE). In this model, the license is held by AWS and is included in the price of the DB Instance.

License Mobility (Bring Your Own License)

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. Currently, Microsoft SQL Server Standard Edition and Microsoft SQL Server Enterprise Edition are the eligible Database editions for this program. Refer to Microsoft’s Product Use Rights for the latest licensing terms.

Licensing for SQL Server 2012

The following table shows the license models that are supported for each SQL Server 2012 version.

SQL Server 2012 Engine Type license-included bring-your-own-license
Enterprise EditionNoYes
Standard EditionYesYes
Web EditionYesNo
Express EditionYesNo

For more information on supported licensing methods on Amazon RDS for SQL Server, see SQL Server License Requirements for Microsoft License Mobility.

Planning your Multi-AZ Deployments Using SQL Server Mirroring

Amazon RDS supports SQL Server Multi-AZ deployments using SQL Server Mirroring. All Multi-AZ implementations 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 failover to the up-to-date standby such that 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, so you do not have to reconfigure your application or set up multiple endpoints when a failover occurs. For more information about Multi-AZ, see High Availability (Multi-AZ).


SQL Server Multi-AZ using Mirroring is currently available in the US East (N. Virginia), US West (Oregon), and EU (Ireland) AWS regions. We plan to support other regions in the future.

Multi-AZ deployments are available for SQL Server Standard and Enterprise Edition with SQL Server 2008R2 and SQL Server 2012. Multi-AZ with Mirroring supports one standby mirror. You can enable Multi-AZ using the RDS console or by setting the Multi-AZ Deployment for SQL Server Using the Mirroring Option in an option group and then associating that option group with your DB instance. For more information on working with Mirroring, see Working with SQL Server Multi-AZ with Mirroring

With a Multi-AZ deployment using Mirroring, Amazon RDS manages failover by actively monitoring your Multi-AZ deployment and proactively initiating a failover when a problem with your primary occurs. Failover does not occur unless the standby and primary are fully in sync. In addition, Amazon RDS will actively reestablish your Multi-AZ deployment by automatically repairing unhealthy DB instances and reestablishing synchronous replication. There is nothing for you to manage; 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 will be mirrored automatically. The Amazon RDS console, CLI, and API can show what Availability Zone the standby instance is located in.

Multi-AZ deployments, including Multi-AZ with Mirroring maintain all databases on the same node. If a database on the primary host fails over, all your SQL Server databases will failover as one atomic unit to your standby host. This allows Amazon RDS to provision a new, healthy host and replace the unhealthy host.

SQL Server Multi-AZ Deployment Recommendations

  • For databases used in production or pre-production we recommend Multi-AZ deployments for high availability, Provisioned IOPS for fast, consistent performance, and instance classes (m1.large and larger) that are optimized for Provisioned IOPS.

  • Users, Logins, and Permissions are automatically replicated for you on the standby mirror . You don’t need to worry about recreating them. User-defined server roles (a SQL 2012 feature) are not replicated in Multi-AZ instances.

  • To use SQL Server Mirroring with a SQL Server DB instance in a VPC, you must create a DB subnet group that has 3 subnets in distinct Availability Zones. You must then assign the DB subnet group to the SQL Server DB instance that is being mirrored.

  • You cannot select the Availability Zone for the standby instance, so you should deploy any applications using the SQL Server databases into all AZs of the region to take advantage of this feature.

  • Note that you cannot configure the standby to accept database read activity.

  • Failover times will be affected by the time it takes to complete the recovery process. Large transactions will increase the failover time.

  • For best performance, do not enable mirroring during a large data load operation. If you want your data load to be as fast as possible, complete the loading before you convert your DB instance to a Multi-AZ deployment.

  • Your application that accesses the SQL Server databases should have exception handling that will catch connection errors. The following code sample shows a try/catch block that will catch a communication error.

    for (int iRetryCount = 0; (iRetryCount < RetryMaxAttempts && keepInserting); iRetryCount++)
    	using (SqlConnection connection = new SqlConnection(DatabaseConnString))
    		using (SqlCommand command = connection.CreateCommand())
    			command.CommandText = "INSERT INTO SOME_TABLE VALUES ('SomeValue');";
    				while (keepInserting)
    			catch (Exception ex)
    	if (iRetryCount < RetryMaxAttempts && keepInserting)
    		Thread.Sleep(RetryIntervalPeriodInSeconds * 1000);
  • If you created SQLAgent jobs, these will need to be recreated in the secondary, as these jobs are stored in the msdb and this database cannot be replicated via Mirroring. You should create the jobs first in the original primary, then fail over, and create the same jobs in the new primary.

  • You should not use the Set Partner Off command when working with Multi-AZ instances. For example, DO NOT do the following:

    alter database db1 set partner off   
  • You should not set the recovery mode to simple. For example, DO NOT do the following:

    alter database db1 set recovery simple   
  • You should not use the DEFAULT_DATABASE parameter when creating new logins on Multi-AZ DB instances as these settings cannot be applied to the standby mirror. For example, DO NOT do the following:

    ALTER LOGIN [test_dba] SET DEFAULT_DATABASE=[db3]   
  • Cross-region Multi-AZ is not currently supported. If you are interested in cross-region disaster recovery, you are encouraged to try our cross-region snapshot copy feature that is available today.

  • You may observe elevated latencies relative to a standard DB Instance deployment in a single Availability Zone as a result of the synchronous data replication performed on your behalf.

Video Introduction to SQL Server Multi-AZ Deployments

The video "Getting Started with Multi-AZ Deployments in Amazon RDS for SQL Server" is available here:

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.

Unless you specify otherwise, your DB Instance will automatically be upgraded to new SQL Server minor versions as they are supported by Amazon RDS. This patching will occur during your scheduled maintenance window, and it will be announced on the Amazon RDS Community Forum in advance. To turn off automatic version upgrades, set the AutoMinorVersionUpgrade parameter for your DB instance to false.

If you opt out of automatically scheduled upgrades, you can manually upgrade to a supported minor version release by following the same procedure as you would for a major version update. For information, see Major DB Engine Version Upgrades for a DB Instance.


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 Service Pack 1 and SQL Server 2012.

Major Version Change: 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 Major DB Engine Version Upgrades for a DB Instance

Supported SQL Server Roles and Permissions

The 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:







  • ALTER SETTINGS (You can use the DB Parameter Group APIs to modify parameters. For more information, see Working with DB Parameter Groups.







  • SHUTDOWN (You can use the RDS reboot option instead)




Using SSL with a SQL Server DB Instance

You can use SSL to encrypt connections between your applications and your Amazon RDS SQL Server DB instances. SSL support is available in all AWS regions for all supported SQL Server editions. Amazon RDS creates an SSL certificate for your SQL Server DB instance when the instance is created. The SSL certificate includes the DB instance endpoint as the Common Name (CN) for the SSL certificate to guard against spoofing attacks.


Amazon RDS will rotate all SSL certificates for DB instances on March 23, 2015 but will not initiate a reboot of the instance. If you use SSL to connect to an Amazon RDS DB instance, you must follow the steps in the topic SSL Certificate Rotation to apply a new SSL certificate to your DB instance before March 23, 2015 or you will not be able to connect to the DB instance using SSL.

Note that all new SQL Server instances created after August 5, 2014 will use the DB instance endpoint in the Common Name (CN) field of the SSL certificate. Prior to August 5, 2014, SSL certificate verification was not available for VPC based SQL Server instances. If you have a VPC based SQL Server DB instance that was created before August 5, 2014, and you want to use SSL certificate verification and ensure that the instance endpoint is included as the CN for the SSL certificate for that DB instance, then rename the instance. When you rename a DB instance, a new certificate is deployed and the instance is rebooted to enable the new certificate.

To encrypt connections to an Amazon RDS SQL Server DB instance using SSL, perform these steps on the client computer:

  1. Download the certificate from

  2. Import the certificate into your Windows operating system:

    1. On the Start menu, type Run in the search box and hit Enter.

    2. In the Open box, type MMC and click OK.

    3. In the MMC console, on the File menu, click Add/Remove Snap-in.

    4. In the Add or Remove Snap-ins dialog box, select Certificates in the Available snap-ins box and click Add.

    5. In the MMC console, on the File menu, click Add/Remove Snap-in.

    6. In the Certificates snap-in dialog box, click Computer account, and then click Next.

    7. In the Select computer dialog box, click Finish.

    8. In the Add or Remove Snap-ins dialog box, click OK.

    9. In the MMC console, expand Certificates, right-click Trusted Root Certification Authorities, click All Tasks, and then click Import.

    10. On the Certificate Import Wizard first screen, click Next.

    11. On the Certificate Import Wizard second screen, click Browse and locate the rds-ssl-ca-cert.pem file you downloaded in step 1. You must change the file type in the browse window to All files (*.*) to do this, because .pem is not a standard certificate extension. Click Open to select the certificate file and then click Next in the wizard.

    12. On the Certificate Import Wizard third screen, click Next.

    13. On the Certificate Import Wizard fourth screen, click Finish. You should see a dialog box indicating that the import was successful.

    14. In the MMC console, expand Certificates, expand Trusted Root Certification Authorities, click Certificates, and locate the certificate to confirm it exists:

    15. Restart the computer.

    For more information about adding a certificate to a computer, go to the Windows documentation.

  3. Connect to the Amazon RDS SQL DB instance.

    • In SQL Server Management Studio, follow these steps:

      1. Launch SQL Server Management Studio.

      2. In the Connect to server dialog box, enter the server information, login user name, and password.

      3. Click Options>>.

      4. Select Encrypt connection.

      5. Click Connect.

      For more information on SQL Server Management Studio, go to Use SQL Server Management Studio.

    • For any other SQL client, append "encrypt=true" to your connection string. This may be available as an option or property on the connection page in GUI tools.


      To enable SSL encryption for clients that connect using JDBC, you may need to add the Amazon RDS SQL certificate to the Java CA certificate (cacerts) store. You can do this by using the keytool utility.

  4. Confirm the encrypted status of your connection by running the following query and verifying that encrypt_option is true:

    SELECT encrypt_option FROM sys.dm_exec_connections WHERE session_id = @@SPID

Using the TDE Option to Encrypt Data at Rest

Most Amazon RDS DB engines support option groups that allow you to select additional features for your DB instance. SQL Server support includes the TDE option, which transparently encrypts stored data for SQL Server 2008 R2 Enterprise Edition and SQL Server 2012 Enterprise Edition. For more information about SQL Server TDE, see SQL Server Transparent Data Encryption. For more information about working with option groups, see Working with Option Groups.