Rajendra Gupta
AWS basic terms

Multi-AZ Configuration for AWS RDS SQL Server

October 10, 2019 by

Amazon provides AWS RDS SQL Server as a managed relational database service. You can deploy databases instantly without worrying about underlying hardware platform, license cost, managing data centers. AWS RDS database instances are highly available. It provides a failover mechanism using the Mult-AZ deployments.

We will cover answers to the following questions in this article:

  • What is a Multi-AZ configuration?
  • Configuration Multi-AZ configuration in AWS RDS SQL Server
  • Validation steps

Let me explain a few useful terms in AWS before explaining the Multi-AZ configuration:

AWS basic terms

Region: Region represents a geographical area in AWS. It is a collection of two or more availability zones. Each region is wholly independent of each other. We deploy resources in AWS in a specific region using the AWS console.

Availability Zone (known as AZ): Availability zone refers to a data center. Each region contains multiple AZ’s that are isolated from each other.

Overview of Multi-AZ Configuration in AWS RDS

Suppose you configured an AWS RDS SQL Server. If you do not have an RDS instance, follow the article AWS RDS SQL Server – Launching a new database instance and create an RDS SQL instance before proceeding for this article.

Open the RDS instance and you can see my RDS instance is in an us-east-1f availability zone:

RDS instance availabiity Zone

Multi-AZ deployment in AWS RDS SQL Server

Let’s say my AWS RDS instance is in availability zone us-east-1a. Suppose the availability zone is down due to power failure or hardware failure. In this case, your application cannot connect to the RDS database hosted in that AZ. We require a database to be highly available and fault tolerance for any such issues.

Amazon provides Multi-AZ deployment for RDS instance. It maintains a synchronous and stands by database copy in a different AZ but belonging to the same region. You can failover RDS instance to secondary replica and application can be available. It automatically performs a failover to the secondary replica in the following scenarios:

  • Primary AZ availability issues
  • Network connectivity issues for primary AZ
  • Hardware failure (Compute unit, storage)
  • Scheduled patching in availability zone servers

In the following image, note down the following:

  • Primary AWS RDS SQL Server instance is in us-east-1a AZ
  • It has a synchronous copy in another AZ in us-east-1b
  • Both primary and secondary RDS instance belongs to a region
  • The user connects to an RDS instance using the endpoint
  • Endpoint automatically connects to a primary replica

Multi-AZ deployment in AWS RDS SQL Server

Now, due to some issues, AWS RDS SQL Server instance on the primary replica is unavailable. In this case, failover happens from primary to the secondary replica. RDS instance in us-east-1b becomes primary.

User connections are automatically diverted to new primary replica (us-east-1b):

Multi-AZ deployment in AWS RDS SQL Server

By default, the Multi-AZ feature is not enabled for the RDS instance. Open the AWS management console, navigate to databases. In the RDS instance property, we have a property Multi-AZ.

In the following screenshot, we can see value N/A for Multi-AZ that shows this feature is disabled. We do not have any failover mechanism configured for this instance:

Multi-AZ configuration

Amazon RDS provides two kinds of Multi-AZ deployments for SQL Server.

Always On availability Group based Multi-AZ deployments

  • SQL Server 2017 14.00.3049.1 or later( Enterprise Edition)
  • SQL Server 2016 13.00.5216.0 or later( Enterprise Edition)

Database mirroring Group based Multi-AZ deployments (except versions specified above)

  • Standard and Enterprise Editions
  • SQL Server 2017, 2016,2014,2012

Enable Multi-AZ deployment in RDS instance

We can modify the RDS instance to enable the Multi-AZ feature. The instance should be in an available status to make the changes. In the following screenshot, we can see Modify is greyed out because the instance is in the starting state:

Enable Multi-AZ deployment

We can connect to this RDS instance using endpoint: myrdsinstance.cumznzii9fba.us-east-1.rds.amazonaws.com as shown below:

SSMS connection

Once the instance is available, click on modify. We do not get any option to enable Multi-AZ in this instance because I have SQL Server expression edition installed; therefore, I do not get an option to enable Multi-Az deployment.

Let’s create a new RDS instance with following edition and version of SQL Server:

  • SQL Server Standard edition
  • SQL Server 2017 14.00.3049.1v1

create a new RDS instance

In the Availability & durability, you get an option for Multi-AZ deployment. Click on Yes (Mirroring/Always ON). RDS automatically selects mirroring or always on based on the instance edition and version:

Multi-AZ deployment

  • Note: AWS RDS SQL Server Standard edition is not free to use. In the Create instance page, it gives you an estimate of a monthly estimate:

    Estimated cost analysis

By default, it enables automatic backs and specifies the backup retention period 7 days:

backup rentension period

If you specify a backup retention period as zero-days, Multi-AZ configuration fails with the following error message. It cannot apply mirroring to instances with backup retention set to zero:

Error message

It takes 15-20 minutes for a database instance creation. Once the new RDS instance is available, open the instance properties.

You can see that primary replica is in us-east–1f and running SQL Server standard edition:

Instance status

In the screenshot below, verify that Multi-AZ is enabled, and it is using database mirroring mechanism. You can also see the secondary zone us-east-1d that acts as a secondary or stand by the database instance:

secondary zone

Note down the endpoint from connectivity and security. Connect the instance using the endpoint.

Endpoint: multiazdemo.cumznzii9fba.us-east-1.rds.amazonaws.com

database connection

Execute the command, and it creates a new database into AWS RDS:

In the following screenshot, we do not see this database configured in the database mirroring:

New database

Wait for some time and refresh the database instance. You can see that the newly created database is also configured in the database mirroring automatically. It’s status changes to Principal Synchronized. It takes database snapshot, restores the copy on standby server and configures database mirroring. We do not require any manual interventions here:

automatically configuration of a new database

We can also view the SQL Server error logs for detailed information about this newly created database. Navigate to the RDS Dashboard and click on Logs. Select the latest logs and click on View:

View logs

It opens the logs in a separate window. You can see that database mirroring is configured for database MultilAZTest and logs show the principal server copy:

SQL Server error logs

Amazon RDS does not allow you to access the stand by a copy of the RDS instance. AWS entirely manages it. It cannot be used for diverting database traffic to the secondary instance. It comes in picture in case of any disaster or unavailability of instance in primary AZ.

Manual database failover for Multi-AZ AWS RDS SQL Server

Let’s say we require rebooting the primary replica of the RDS instance. Click on Actions and Reboot:

Manual DB failover

In Multi-AZ configuration, we get an option to reboot instance with failover. If we enable this checkbox, the RDS instance fails over to standby copy in another availability zone:

  • Failover happens from primary to stand by RDS replica
  • The current stand by replica takes over the role of primary and starts accepting database connections
  • It reboots stand by (old primary) instance

Put a tick in checkbox for a reboot with failover and reboot:

reboot with failover

It takes 1-2 minutes, for instance to failover, to perform database recovery and start accepting database connections. In the rebooting state, it shows the primary AZ, i.e. us-east-1f, in this case:

Rebooting

Once the RDS instance fails over and becomes available, verify the primary and secondary availability zones:

  • Primary AZ before failover: us-east-1f

    Primary AZ before failover

  • Secondary AZ before failover: us-east-1d

    Secondary AZ before failover: us-east-1d

  • Primary AZ after failover: us-east-1d

    Primary AZ after failover: us-east-1d

  • Secondary AZ with failover: us-east-1f

    Secondary AZ with failover: us-east-1f

You can connect to the RDS instance, and it shows all databases in synchronized mode. Application or end-user does not need to worry about the availability zone in which instance is in principal mode:

Verify DB connection

Few important points about Multi-AZ deployment for AWS RDS SQL Server

  • AWS allows a single secondary copy of the RDS instance in a similar region
  • We cannot configure Multi-AZ deployment for standby RDS instance in a different region
  • We cannot access the stand by replica databases
  • Databases should be in full recovery model for Multi-AZ deployment
  • AWS automatically replicates users, logins and permissions on standby replica. No user intervention required
  • RDS maintains a synchronized copy of databases in another availability zone. In synchronized mode, it waits the transaction commit acknowledgment from the standby database and then commits on primary or principal database copy. We might face latency due to synchronous commit in comparison with stand-alone database copy
  • We need to replicate SQL Server agent jobs manually on a standby instance. AWS RDS does not take care of it
  • Usually, we have a 1-2 minute of RDS instance failover including database recovery on a new primary replica. It might take longer depending upon database size, the number of active transactions, recovery process (undo, redo) efforts required

Conclusion

In this article, we explored the Multi-AZ deployments for AWS RDS SQL Server. It is useful for providing an RDS instance failover mechanism for critical application. We can minimize application downtime with this failover mechanism.

Rajendra Gupta
Latest posts by Rajendra Gupta (see all)
AWS RDS

About Rajendra Gupta

Hi! I am Rajendra Gupta, Database Specialist and Architect, helping organizations implement Microsoft SQL Server, Azure, Couchbase, AWS solutions fast and efficiently, fix related issues, and Performance Tuning with over 14 years of experience. I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines. I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups. Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020. Personal Blog: https://www.dbblogger.com I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com View all posts by Rajendra Gupta

168 Views