Amazon Relational Database Service
User Guide (API Version 2014-10-31)
AWS services or capabilities described in AWS documentation might vary by Region. To see the differences applicable to the China Regions, see Getting Started with Amazon AWS.

Multi-AZ Deployments for Microsoft SQL Server

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 secondary DB instance. This functionality lets database operations resume quickly without manual intervention. The primary and standby instances use the same endpoint, whose physical network address transitions to the secondary replica as part of the failover process. You don't have to reconfigure your application when a failover occurs.

Amazon RDS supports Multi-AZ deployments for Microsoft SQL Server by using either SQL Server Database Mirroring or Always On availability groups. Amazon RDS monitors and maintains the health of your Multi-AZ deployment. If problems occur, RDS automatically repairs unhealthy DB instances, reestablishes synchronization, and initiates failovers. Failover only occurs if the standby and primary are fully in sync. You don't have to manage anything.

When you set up SQL Server Multi-AZ, RDS automatically configures all databases on the instance to use Mirroring or Always On. Amazon RDS handles the primary, the witness, and the secondary DB instance for you.

Because configuration is automatic, RDS selects Mirroring or Always On based on the version of SQL Server that you deploy. Amazon RDS supports Multi-AZ with Mirroring or Always On for the following SQL Server versions and editions, with the noted exceptions:

  • SQL Server 2017: Standard and Enterprise Editions (Always On not yet supported)

  • SQL Server 2016: Standard and Enterprise Editions (Always On only in 13.00.5216.0 or later)

  • SQL Server 2014: Standard and Enterprise Editions

  • SQL Server 2012: Standard and Enterprise Editions

  • SQL Server 2008 R2: Standard and Enterprise Editions

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

  • China: Always On is not supported in China

  • US West (N. California): Neither Mirroring or Always On are supported here

  • Asia Pacific (Sydney): Supported for DB instances in VPCs

  • Asia Pacific (Tokyo): Supported for DB instances in VPCs

  • South America (São Paulo): Supported on all DB instance classes except m1 or m2

Adding Multi-AZ to a Microsoft SQL Server DB Instance

When you create a new SQL Server DB instance using the AWS Management Console, you can add Multi-AZ with Mirroring or Always On. You do so by choosing Yes (Mirroring / Always On) from the Multi-AZ Deployment list on the Specify DB Details page. For more information, see Creating a DB Instance Running the Microsoft SQL Server Database Engine.

When you modify an existing SQL Server DB instance using the AWS Management Console, you can add Multi-AZ with Mirroring or Always On by choosing Yes (Mirroring / Always On) from the Multi-AZ Deployment list on the Modify DB Instance page. For more information, see Modifying a DB Instance Running the Microsoft SQL Server Database Engine.

Microsoft SQL Server Multi-AZ Deployment Notes and Recommendations

The following are some restrictions when working with Multi-AZ deployments for Microsoft SQL Server DB instances:

  • Cross-region Multi-AZ is not currently supported.

  • You can't configure the secondary to accept database read activity.

  • Multi-AZ with Always On supports in-memory optimization.

  • You can't rename a database on a SQL Server DB instance that is in a SQL Server Multi-AZ deployment. If you need to rename a database on such an instance, first turn off Multi-AZ for the DB instance, then rename the database. Finally, turn Multi-AZ back on for the DB instance.

  • You can only restore Multi-AZ DB instances that are backed up using the full recovery model.

The following are some notes about working with Multi-AZ deployments for Microsoft SQL Server DB instances:

  • Amazon RDS exposes the Always On availability group listener endpoint. The endpoint is visible in the console, and is returned by the DescribeDBInstances API as an entry in the endpoints field.

  • Amazon RDS supports availability group multisubnet failovers.

  • To use SQL Server Multi-AZ with a SQL Server DB instance in a VPC, first create a DB subnet group that has subnets in at least two distinct Availability Zones. Then assign the DB subnet group to the primary replica of the SQL Server DB instance.

  • When a DB instance is modified to be a Multi-AZ deployment, during the modification it has a status of modifying. Amazon RDS creates the standby, and makes a backup of the primary DB instance. After the process is complete, the status of the primary DB instance becomes available.

  • Multi-AZ deployments maintain all databases on the same node. If a database on the primary host fails over, all your SQL Server databases fail over as one atomic unit to your standby host. Amazon RDS provisions a new healthy host, and replaces the unhealthy host.

  • Multi-AZ with Mirroring or Always On supports a single standby replica.

  • Users, logins, and permissions are automatically replicated for you on the secondary. You don’t need to recreate them. User-defined server roles (a SQL Server 2012 feature) are only replicated in Multi-AZ instances for Always On instances.

  • If you have SQL Server Agent jobs, recreate them on the secondary. You do so because these jobs are stored in the msdb database, and you can't replicate this database by using Mirroring or Always On. Create the jobs first in the original primary, then fail over, and create the same jobs in the new primary.

  • You might observe elevated latencies compared to a standard DB instance deployment (in a single Availability Zone) because of the synchronous data replication.

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

The following are some recommendations for working with Multi-AZ deployments for Microsoft SQL Server DB instances:

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

  • You can't select the Availability Zone (AZ) for the secondary instance, so when you deploy application hosts, take this into account. Your database might fail over to another AZ, and the application hosts might not be in the same AZ as the database. For this reason, we recommend that you balance your application hosts across all AZs in the given AWS Region.

  • For best performance, don't enable Mirroring or Always On during a large data load operation. If you want your data load to be as fast as possible, finish loading data before you convert your DB instance to a Multi-AZ deployment.

  • Applications that access the SQL Server databases should have exception handling that catches connection errors. The following code sample shows a try/catch block that catches 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); } }
  • Don't use the Set Partner Off command when working with Multi-AZ instances. For example, don't do the following.

    --Don't do this ALTER DATABASE db1 SET PARTNER off
  • Don't set the recovery mode to simple. For example, don't do the following.

    --Don't do this ALTER DATABASE db1 SET RECOVERY simple
  • Don't use the DEFAULT_DATABASE parameter when creating new logins on Multi-AZ DB instances, because these settings can't be applied to the standby mirror. For example, don't do the following.

    --Don't do this CREATE LOGIN [test_dba] WITH PASSWORD=foo, DEFAULT_DATABASE=[db2]

    Also, don't do the following.

    --Don't do this ALTER LOGIN [test_dba] SET DEFAULT_DATABASE=[db3]

Determining the Location of the Secondary

You can determine the location of the secondary replica by using the AWS Management Console. You need to know the location of the secondary if you are setting up your primary DB instance in a VPC.


				Single AZ Scenario

You can also view the Availability Zone of the secondary using the AWS CLI command describe-db-instances or RDS API action DescribeDBInstances. The output shows the secondary AZ where the standby mirror is located.

Migrating from Mirroring to Always On

To migrate from Mirroring to Always On, first check your version. If you are using a DB instance with a version prior to 13.00.5216.0, modify the instance to patch it to 13.00.5216.0.

To upgrade to Always On, modify the instance to remove Multi-AZ, and then modify it again to add Multi-AZ. This converts your instance to use AlwaysOn.