Amazon Relational Database Service
User Guide (API Version 2013-09-09)
« 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.Did this page help you?  Yes | No |  Tell us about it...

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 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 Amazon RDS SQL Server DB Instance.

  • 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 Overview. For more information about Provisioned IOPS, see Provisioned IOPS Storage.

  • 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 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 DB Engine. For more information about creating a DB instance, see Creating a DB Instance Running the Microsoft 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 Microsoft 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 Back Up and Restore.

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

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

Planning your Amazon RDS SQL Server DB Instance

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.

  • 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 masteruser, 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 Data into SQL Server on Amazon RDS 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.

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

  • The database time zone is not modifiable.

  • 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

  • SSL

  • Transparent Data Encryption (Enterprise Edition only)

  • Spatial and location features

  • Change Tracking

  • Database Mirroring

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) - only supported for DB instances not in a VPC

  • 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

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

Note

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

Note

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');";
    
    			try
    			{
    				connection.Open();
    				
    				while (keepInserting)
    				{
    					command.ExecuteNonQuery();
    					intervalCount++;
    				}
    				connection.Close();          
    			}
    			catch (Exception ex)
    			{
    				Logger(ex.Message);
    			}
    		}
    	}
    
    	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   
    go  
  • You should not set the recovery mode to simple. For example, DO NOT do the following:

    alter database db1 set recovery simple   
    go  
  • 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:

    CREATE LOGIN [test_dba] WITH PASSWORD=foo, DEFAULT_DATABASE=[db2]   
    GO   
    or   
    ALTER LOGIN [test_dba] SET DEFAULT_DATABASE=[db3]   
    GO  
  • 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:

DB 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 Upgrading a DB Instance.

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

  • ADMINISTER BULK OPERATIONS

  • ALTER ANY CREDENTIAL

  • ALTER ANY EVENT NOTIFICATION

  • 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

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