Amazon Relational Database Service (Amazon RDS) Custom is a managed database service for legacy, custom, and packaged applications that require access to the underlying operating system and database (DB) environment. Amazon RDS Custom is now available for the SQL Server database engine. Amazon RDS Custom for SQL Server automates setup, operation, and scaling of databases in the cloud while granting access to the database and underlying operating system to configure settings, install drivers, and enable native features to meet the dependent application’s requirements.
Always On Availability Groups (AGs) is a high availability (HA) feature that you can use as a disaster recovery solution to provide an enterprise-level alternative to database mirroring. An availability group supports a replicated environment for a discrete set of user databases, known as availability databases, that can fail over together.
In this post, I explain high availability configuration using Always On AGs on RDS Custom for SQL Server instances. We define HA as when the data from the primary DB instance is synchronously replicated to a secondary instance that resides in a different Availability Zone. Running a DB instance in the HA configuration enhances availability during planned system maintenance, and helps protect your databases against DB instance failure and Availability Zone disruption.
Before you decide to set up HA for your instance, this post assumes that you have a good background knowledge of Always On setup, its various commit modes, and latency requirements. In this post, we setup a domain-independent availability group.
For additional information, refer to What is an Always On availability group and Create a domain-independent availability group. We strongly recommend that you set up Always On in a non-production instance and run end-to-end validations before you implement this solution in a production environment.
To setup an HA configuration, you need to create two RDS Custom for SQL Server DB instances that acts as the primary and secondary nodes, and one Amazon Elastic Compute Cloud (Amazon EC2) instance that acts as a witness node. We strongly recommend that you create these instances in different Availability Zones of the same Region.
The following diagram illustrates our HA setup using Always On AGs.
We implement the solution with the following high-level steps:
Create RDS Custom for SQL Server and EC2 instances.
Prepare the RDS Custom for SQL Server DB instances.
Set up the witness disk using iSCSI.
Set up a Windows cluster.
Set up an Always On AG.
Create a database and add it to the availability group.
Create RDS Custom for SQL Server and EC2 instances
The first step is to launch your RDS Custom for SQL Server instances. For more details about creating RDS Custom for SQL Server instances, refer to Creating and connecting to a DB instance for Amazon RDS Custom for SQL Server.
The following example demonstrates launching two RDS Custom for SQL Server instances within the same VPC and security group. You require two different DB subnet groups to make it available in different Availability Zones.
Launch Node-1, RDS-custom-sqlserver-1, in the us-west-2c Availability Zone:
Launch Node-2, RDS-custom-sqlserver-2, in the us-west-2a Availability Zone:
Next, we launch an EC2 instance that acts as a witness node.
Choose an Amazon Machine Image (AMI). For this post, we use the AMI of Microsoft Windows Server 2019 Base.
Select an instance type. For this post, we use the db.t2.micro instance type.
Configure the instance details:
We use the same VPC network that is being used by the RDS Custom for SQL Server DB instance, but in a different Availability Zone (us-west-2b). In the previous steps, we launched RDS Custom for SQL Server DB instances in the us-west-2c and us-west-2a Availability Zones.
Set Auto-assign Public IP to Enable.
Add the new volume type of GP2 with a storage size of 100 GB.
Configure the VPC security group. For this post, we select the security group RDS-usw2-vpc-sg-1 (the same as what was specified for the preceding RDS Custom for SQL Server DB instances).
Review the instance launch and select a key pair.
Prepare the RDS Custom for SQL Server DB instances
To prepare your RDS Custom for SQL Server DB instances, complete the following steps:
Pause RDS Custom automation on both RDS Custom for SQL Server DB instances, so that RDS automation doesn’t interfere with the configuration tasks that we perform.
To connect RDS Custom for SQL Server instances, you must enable Remote Desktop Protocol (RDP).
Open Windows Power Shell and run the following commands on Node-1 and Node-2:
Start the MSiSCSI service:
Set a DNS suffix. The following example creates a DNS suffix for the domain called custom.com:
Install the failover clustering feature:
Make sure that the administrator password is the same for Node-1 and Node-2:
Set up the witness disk using iSCSI
To set up the witness disk, you run commands on Node-1, and Node-2, and the witness node.
Perform the following steps on witness node:
Start the MSiSCSI service:
Set up the disk for quorum:
Install the iSCSI target server role:
Create an iSCSI Virtual Hard Disk (VHDX) object that has the specified file path and size:
Create a new iSCSI target object named rdscustomquorum.
An InitiatorIds is the private IP address of an RDS Custom for SQL Server instances. You can get information about IP addresses on the Amazon EC2 console or by running Ipconfig on the command prompt of the RDS Custom for SQL Server instances.
We use the following private IP addresses:
Node-1 (RDS-custom-sqlserver-1) — 10.0.3.213
Node-2 (RDS-custom-sqlserver-2) — 10.0.1.174
Run the following code:
Assign the virtual disk “D:rdsquorumrdscustomquorum01.vhdx” to the iSCSI target rdscustomquorum:
Node-1 and Node-2
Run the following command on Node-1 and Node-2 to add a new iSCSI target portal named rdscustomwitness:
Node-1, Node-2, and witness node
These steps are performed on Node-1, Node-2 and the witness node:
Navigate to the folder path C:WindowsSystem32driversetc.
Edit the host file to include entries for Node-1, Node-2, and the witness node:
Make sure that the security groups for both Node-1, Node-2 and the witness node permit the inbound and outbound connections (type: All TCP, protocol: TCP, port range: <>, source: Custom, plus the security groups or IP addresses for all three instances).
With the ping command, verify that you can establish a connection between Node-1 and Node-2.
Similarly, verify a connection between the witness node to Node-1 and Node-2.
Set up a Windows cluster
To set up your Windows cluster, you run commands on Node-1 and Node-2. Complete the Steps 1–10 on Node-1:
Set up the cluster quorum disk:
Create a new failover cluster and specify the IP address.
The following example creates a new a domain-independent Windows cluster called “rdscustomcluster”. To create a cluster, identify an unallocated IP address within the given subnet range that can be used as an IP address for the cluster. In the following example, we use 10.0.3.215 for the IP address:
Get the information about the iSCSI disk (witness):
Add the iSCSI disk to the cluster:
Set the cluster quorum to Node and Disk majority:
Add Node-2 to the cluster:
Rename the resource Cluster IP Address, because we add one more for the subnet on Node-2:
Add a new cluster resource for the subnet of Node-2. In the following code, the private IP address of Node-2 is 10.0.1.174. We can choose any available IP address within the given subnet range, for example: 10.0.1.176.
Add the IP address of the Node-2 control subnet for the cluster resource:
Set the cluster dependency to either the Node-1 or Node-2 subnet cluster resource:
On Node-1 and Node-2, enable SQLAlways On and restart the computer:
Set up an Always On AG
We configure an Always On AG on an RDS Custom for SQL Server instances by performing the following sequence of steps on Node-1 and Node-2:
On Node-1, create an end point and backup certificate:
On Node-2, create an end point and backup certificate:
On Node-1, copy the certificates between the hosts:
On Node-1, create a certificate using the backup certificate of Node-2:
On Node-2, create a certificate using the backup certificate of Node-1:
On Node-1, create an availability group:
Create a database and add it to the availability group
On Node-1, create a database:
On Node-2, join the availability group:
The Always On configuration looks like following screenshot from Node-1.
In this post, I explained how to configure HA on RDS Custom for SQL Server database instances using Always On AGs. I also shared some best practices and recommendations for achieving high availability by replicating data across different Availability Zones.
Try out the solution and if you have any comments or questions, leave them in the comments section.
About the Author
Srikanth Katakam is a Senior Database Engineer at Amazon Web Services. He works on the RDS team, focusing on commercial database engines, RDS Custom and SQL Server. He enjoys working on technical challenges in Amazon RDS and is passionate about learning from and sharing knowledge with his teammates and AWS customers.
Read MoreAWS Database Blog