Configure Multi-AZ with Amazon RDS Custom for SQL Server

Amazon Relational Database Service (Amazon RDS) Custom for SQL Server now supports high availability by using block-level replication in a Multi-AZ environment. Previously, to enable high availability on the instance, you had to configure an Always On Availability Group (AG) between two instances. Setting up and configuring Always On requires planning and expertise. With the new Multi-AZ capability, high availability is simplified.

In this post, we show you how to create a highly available RDS Custom instance, discuss some considerations, and describe the expected time taken to fail over.

You must set up your RDS Custom pre-requisites for Multi-AZ to work. If you previously set up your pre-requisites, then you may need to update them with additional permissions that allow Multi-AZ, as indicated here. If this is your first time using RDS Custom for SQL Server, and you have not set up your prerequisites, refer to Get started with Amazon RDS Custom for SQL Server using an AWS CloudFormation template (Network setup). Setting up your prerequisites is required. If you are part of an AWS Organization, consider that your service control policies (SCPs) could potentially restrict RDS Custom. For more information about these requirements, refer to Prerequisites for setting up RDS Custom for SQL Server.

Overview of high availability

Amazon RDS provides support for high availability when you deploy your instance using the Multi-AZ option through a standby DB instance. Amazon RDS uses a variety of technologies to provide Multi-AZ and failover support. For RDS Custom for SQL Server, AWS doesn’t use Always On. Rather, AWS uses its own technology platform. In a Multi-AZ deployment, Amazon RDS automatically provisions and maintains a synchronous standby replica in a different Availability Zone. The time taken for a failover is 60 to 120 seconds; however, it can take longer if there are large transactions or a lengthy recovery process.

Data from the primary instance is synchronously block-level replicated to the standby instance to provide data redundancy. In the event of an unplanned service disruption, Amazon RDS automatically fails over to the up-to-date standby instance. This allows your database operations to resume with minimal interruption using the same DNS endpoint, removing the need to reconfigure your connection string after a failover.

Solution overview

In this post, we assume a scenario in which the company requires a high availability setup using RDS Custom for SQL Server across two Availability Zones, as shown in the following figure. For this post, the majority of the steps are performed using the AWS Management Console; however, there are options to deploy the instances using an AWS CloudFormation template and the AWS Command Line Interface (AWS CLI).

To implement the solution, complete the following high-level steps:

Create a Multi-AZ RDS Custom for SQL Server instance.
Use SQL Server Management Studio (SSMS) to connect to RDS Custom for SQL Server from a Windows instance running on Amazon Elastic Compute Cloud (Amazon EC2).
Perform a failover.
Verify the database is available on the new primary instance.

Prerequisites

Before we begin, we assume that you have the following prerequisites:

An AWS account.
The networking, instance profile, and encryption key needed to deploy RDS Custom for SQL Server. For a step-by-step tutorial to set up these items, refer to Get started with Amazon RDS Custom for SQL Server using an AWS CloudFormation template (Network setup).
If you previously created a Single-AZ instance, you must ensure you included the new pre-requisites for Multi-AZ, more information in the RDS documentation..
A Windows EC2 instance with SSMS is installed.
Communications between your EC2 instance and RDS Custom via the security group is allowed.
The AWS CLI is installed and configured (optional). For instructions, refer to Installing or updating the latest version of the AWS CLI.

For more details, refer to Creating and connecting to a DB instance for Amazon RDS Custom for SQL Server.

This solution involves the creation and utilization of new AWS resources. Therefore, it will incur costs on your account. Refer to AWS Pricing for more information.

We strongly encourage that you set this up in a non-production environment and run end-to-end validations before implementing this solution in your production environment.

Create a Multi-AZ RDS Custom for SQL Server instance

Before you create an instance, you need to check the Region and version availability to make sure the combination of SQL Server editions and versions are enabled for you in the Region where you want to host the RDS instance.

To create your Multi-AZ RDS Custom instance, complete the following steps:

On the Amazon RDS console, choose Create database.
Select Standard create.
Select Microsoft SQL Server as the engine type.
For Database management type, select Amazon RDS Custom.
Select any SQL Server Edition. For this post, we select Standard Edition.
Choose the latest engine version.
Under Settings, enter a name for the instance and a primary user name and password.
For Instance, configuration, and storage, keep the default parameters.
Under Availability & durability, select Create a standby instance to create a Multi-AZ instance.
Under Connectivity, choose the VPC and subnet group you have created.
Select No for Public access.
For VPC security group, select Choose existing and choose the security group you have created.

If you used the CloudFormation template to set up security groups, look for the one ending in rds-custom-instance-sg. Remove the default security group.

Under RDS Custom security, choose the instance profile and the AWS Key Management Service (AWS KMS) key you have created.
Choose Create database and wait for Amazon RDS to provision the Multi-AZ RDS Custom instance.

Alternatively, you can create a Multi-AZ instance using the AWS CLI.

The following code is for Windows:

aws rds create-db-instance ^
--engine custom-sqlserver-se ^
--engine-version 15.00.4261.1.v1 ^
--db-instance-identifier rds-sqlserver-custom-maz ^
--db-instance-class db.m5.xlarge —allocated-storage 100 ^
--storage-type io1 —iops 3000 ^
--db-subnet-group <your db subnet group> ^
--vpc-security-group-ids "<your security group id>" ^
--master-username XXXXX ^
--master-user-password XXXXX ^
--backup-retention-period 7 —multi-az —port 1433 ^
--kms-key-id <your KMS key id> --no-publicly-accessible ^
--custom-iam-instance-profile <your instance profile> —region <your AWS region>

The following code is for Mac:

aws rds create-db-instance —no-verify-ssl \
--engine custom-sqlserver-ee \
--engine-version 15.00.4261.1.v1 \
--db-instance-identifier rds-sqlserver-custom-maz \
--db-instance-class db.m5.xlarge \
--allocated-storage 100 —storage-type io1 —iops 3000 \
--master-username xxxxx \
--master-user-password xxxx \
--kms-key-id <your KMS key id> \
--db-subnet-group-name <your db subnet group> \
--vpc-security-group-ids "<your security group id>" \
--no-publicly-accessible —multi-az —port 1433 \
--region <your AWS region> —custom-iam-instance-profile <your instance profile>

Use SSMS to connect to RDS Custom for SQL Server from a Windows EC2 instance

After you create the RDS Custom instance, create an RDP session to your Windows EC2 instance. We use SSMS to connect to the RDS Custom instance.

Complete the following steps to connect to your RDS Custom and create a dummy database for testing purposes:

Remote desktop to your Windows EC2 instance. For details on how to RDP, refer to Connect to your Windows instance using RDP.
When you’re on the Windows EC2 instance, in the search window, search for SSMS, choose Connect, and Database Engine.
For Server name, enter the RDS Custom for the SQL Server endpoint.
Enter the login and password details that were specified when you created the RDS Custom for SQL Server instance.
Choose Connect.

Open a new query window and run the following query to create a database:

USE master;
GO
CREATE DATABASE MAZDB;
GO
USE MAZDB;
GO
CREATE TABLE dbo.test
(
ID int identity(1,1) primary key,
[Desc] char(100)
)
GO
INSERT INTO dbo.test
VALUES
('RDS Custom MAZ')
go 50
SELECT COUNT (*) FROM dbo.test;
GO

You will see the database being created and data inserted into the table (50 records).

Take note of your primary Availability Zone.

Perform a failover

We now perform a failover using the console. The failover process promotes the secondary standby as the new primary instance. You will notice the change of the primary Availability Zone as well.

On the Amazon RDS console, select your RDS Custom instance.
On the Actions menu, choose Reboot.

The Reboot DB Instance page appears, and the option Reboot With Failover has been selected for you.

Choose Confirm to reboot your DB instance.

Alternatively, you can initiate a failover using the AWS CLI:

aws rds reboot-db-instance --db-instance-identifier rds-sqlserver-custom-maz --region ap-south-1 --force-failover

Verify the database is available on the new primary after failover

After you complete the failover process, you can connect to the new primary instance to verify the database that was created earlier is in the available state and that you are able to query it.

Remote desktop to your Windows EC2 instance (if you closed the connection previously).
In your EC2 instance, in the search window, search for SSMS, choose Connect, and Database Engine.
For Server name, enter the RDS Custom for SQL Server endpoint.
Enter your login and password details that were specified when you created the RDS Custom for SQL Server instance.
Choose Connect.
Open a new query window and run the following query to verify you are able to retrieve the data:
```
USE MAZDB;
GO
SELECT COUNT (*) FROM dbo.test;
GO
```

You should see 50 rows returned.

Your new primary Availability Zone will be different compared to when it was first created. For this post, the initial primary was in ap-south-1b and after the failover, the new primary is now in ap-south-1a. It takes a few minutes for the Amazon RDS console to reflect the new Availability Zone.

Considerations

In this section, we discuss considerations around certain key SQL Server features on a Multi-AZ RDS Custom for SQL Server instance. These guidelines help address a failover event or instance replacement. It is recommended that you have a checklist available to reconfigure the features when required.

SQL Server Reporting Services (SSRS) – To use SSRS, it’s advisable to perform the configuration using Reporting Services Configuration Manager on both the primary and standby instances. This is helpful in minimizing the time needed to configure SSRS after a failover. Remember to keep a backup copy of the encryption key in a secure location after the initial configuration. If you faced an issue after a failover or instance replacement, you need to perform the following high-level steps on your new primary instance to restore your SSRS service:
- Launch Reporting Services Configuration Manager.
- Ensure that you are connected to the existing ReportServer database.
- Restore your SSRS encryption key.
Log shipping – You can use RDS Custom for SQL Server as the secondary server in a SQL Server Log shipping configuration. Setting up log shipping on RDS Custom is similar to how you would on premises. If you face an issue after a failover, complete the following high-level steps on your new primary instance to recover:
- Open the command prompt, enter hostname, and press Enter. Take note of the hostname.
- Open SSMS and run sp_dropserver and sp_addserver stored procedures to update the instance name.
- Modify the Copy and Restore log shipping jobs to reflect the new hostname from the first step in the -server parameter of the jobs.
Replication – RDS Custom for SQL Server supports native replication as a publisher using a remote distributor. The steps to set up replication are the same as on a self-managed SQL Server instance. You may want to script out your replication topology to recreate them if needed. If you face an issue after a failover, complete the following steps on your new primary instance to set up your replication topology again:
- Open the command prompt, enter hostname, and press Enter. Take note of the hostname.
- Open SSMS and run sp_dropserver and sp_addserver stored procedures to update the instance name.
- Reconfigure your replication topology using the script that was generated initially.

It’s important that your RDS Custom for SQL Server instance remains in a supported configuration; this allows the Amazon RDS automation to manage your instance for you, including providing automated backups. Refer to RDS Custom support perimeter and unsupported configurations for examples of unsupported configuration issues and how to fix them.

Clean up

To avoid incurring unwanted charges, delete the resources you created as part of this post. Refer to the following instructions:

Deleting an RDS Custom for SQL Server DB instance
Terminate your instance
If you created the prerequisite resources (network, instance profile, and encryption key) using the provided CloudFormation template, delete the stack to remove the resources

Summary

In this post, you learned how to set up a Multi-AZ RDS Custom for SQL Server instance and perform a failover to test the availability of a database. This feature is useful to meet your business and application high availability requirements and helps you minimize the operational overhead required to set up high availability on your own.

Try out this solution in your AWS account and if you have any comments or questions, leave them in the comments section.

About the authors

Sudarshan Roy is a Senior Database Specialist Cloud Solution Architect with World Wide AWS Database Services Organization (WWSO). He has led large scale Database Migration & Modernization engagements for Enterprise Customers and his passionate of solving complex migration challenges while moving database workload to AWS Cloud.

Barry Ooi is a Senior Database Specialist Solution Architect at AWS. His expertise is in designing, building and implementing data platform using cloud native services for customers as part of their journey on AWS. His areas of interest includes data analytics and visualization. In his spare time, he loves music and outdoor activities.

Santhosh Reddy Talla is an accomplished Database Engineer , works with RDS SQL Server team at AWS, with expertise in relational databases such as SQL Server, Oracle, Postgres, and MySQL. He has a strong ability to solve complex problems, optimize database performance, and automate tasks using Python, Shell, and PowerShell. Outside of work he is avid outdoor enthusiast, with a love for skiing and hiking.