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:
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:
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
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):
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:
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:
We can connect to this RDS instance using endpoint: myrdsinstance.cumznzii9fba.us-east-1.rds.amazonaws.com as shown below:
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
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:
-
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:
By default, it enables automatic backs and specifies the backup retention period 7 days:
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:
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:
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:
Note down the endpoint from connectivity and security. Connect the instance using the endpoint.
Endpoint: multiazdemo.cumznzii9fba.us-east-1.rds.amazonaws.com
Execute the command, and it creates a new database into AWS RDS:
|
1 |
CREATE DATABASE MultiAZTest; |
In the following screenshot, we do not see this database configured in the database mirroring:
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:
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:
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:
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:
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:
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:
Once the RDS instance fails over and becomes available, verify the primary and secondary availability zones:
-
Primary AZ before failover: us-east-1f
-
Secondary AZ before failover: us-east-1d
-
Primary AZ after failover: us-east-1d
-
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:
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.
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
- Understanding PostgreSQL SUBSTRING function - September 21, 2024
- How to install PostgreSQL on Ubuntu - July 13, 2023
- How to use the CROSSTAB function in PostgreSQL - February 17, 2023