Jignesh Raiyani
Fileshare witness for cluster

Configuring SQL Server Always On Availability Groups on AWS EC2

July 6, 2020 by

In this article, we will review how to set up multi-subnet Always On Availability groups using AWS EC2 instances and AWS FSx as file share witness.

The following are the steps involved in creating a multi-subnet Always On Availability groups in AWS EC2 instances:

  • Prepare EC2 instances
  • Create AWS FSx for file share witness
  • Create Windows Server Failover cluster
  • Create Always On Availability Groups

Let’s go over these steps one by one.

Preparing EC2 instances

Log in to the AWS management console, click on Services, and navigate to the EC2 console.

EC2 Console

Click on the Launch Instance button and choose the instance of your choice. For demo purposes, I used Windows Server 2019 with SQL Server 2017 Enterprise edition image. You can also launch windows server image and install SQL Server by bringing your own license method (BYOL).

Instance for Always On Availability groups

Choose the instance type of your choice and configure the instance details like virtual private cloud network (VPC), subnet, etc. In this case, I am launching node 1 in the us-east-1a availability zone.

EC2 instance details

Add the storage and tag the instances. Configure the security group and launch the instance. Similarly, launch node 2 in the same virtual private cloud network (VPC) but a different availability zone i.e., us-east-1b. So, we have both the nodes up and running in the respective availability zones.

Ec2 instances in different zones

Add two secondary IPs for each node. One is used for Windows Server Failover Clustering, and the other will be used for Always On Availability group listener.

To add the secondary IPs, select the node, and click on Actions (1). Click on Networking (2) and finally select Manage IP Addresses (3).

Secondary IP address for EC2 instances

In Manage IP Addresses pop up, Click on Assign new IP two times and then click on Yes, Update as shown in the below image. You can input the custom IP addresses that are available or use the auto-assign option, which assigns a random IP address automatically.

Similarly, assign two secondary IP addresses on node-2 as well.

Node 1 IP addresses:

Windows OS

172.31.44.241

Windows Failover cluster

172.31.44.223

Always On Availability groups Listener

172.31.44.110

Node 1 IP addresses

Node 2 IP addresses:

Windows OS

172.31.3.187

Windows Failover cluster

172.31.4.222

Always On Availability groups Listener

172.31.15.89

Node 2 IP addresses

Now we need to add both the nodes to a domain. For detailed steps of adding the nodes to the domain, you can refer to the following SQL Shack articles:

Adding to domain

After adding nodes to the window’s active directory domain, restart both the nodes.

Log in to each node using the remote desktop protocol (RDP) and start installing the Failover Cluster feature. To install the Failover Cluster feature, Open Server Manager, and click on Add roles and features.

Install failover cluster

Click on the Next button and select Roles-based or feature-based installation. Select the server and click on the Next button. On Features pane, select the Failover clustering feature and click on the Add Features button.

Failover cluster feature

Click Next and select restart the destination server if required. Click on the Install button. Similarly, install the failover cluster feature on node-2 as well.

Creating AWS FSx for file share witness

Log in to the AWS management console, click on Services and navigate to FSx console, as shown in the below image.

Fsx console

Click on Create file system and select Amazon FSx for Windows File Server. Click on the Next button.

Fsx file server

Enter the file system name, select the configuration options like virtual private cloud (VPC) network, subnet, and security groups. Select self-managed Microsoft Active Directory and enter the domain name and the IP address of the domain controller. Enter the user name and password, which has the right permissions to create AWS FSx objects in Microsoft Active Directory. Click on the Next button and click on Create file system.

Fsx DNS name

Once the file system is created successfully and available, click on the file system name to navigate to the file system details page where you can see the DNS name of the file system. You can access the file system using the DNS name. For example, if DNS name is amznfsxnhgf0abc.domain.com then you can access file share using amznfsxnhgf0abc.domain.com\share and the same will be used as file share witness in Always On Availability groups cluster

Fsx details

Make sure you have the connectivity to file system from both the nodes by adding rules in the Fsx security group to allow inbound connections from both the nodes.

Creating Windows Server Failover cluster

Log in to node 1 using the remote desktop protocol (RDP) and launch failover cluster manager. Click on Create Cluster, as shown in the below image.

Windows failover cluster

Click on the Next button. Add both the nodes and click the Next button.

Adding nodes to wfcs

Select Yes to run the validation tests and access the validation report. In this case, I am ignoring the validation test. Click Next.

Validation on cluster creation

Enter the name of the failover cluster and click on the Next button to create a Windows failover cluster which manages the Always On Availability groups failover.

Cluster name

Once the cluster creation is successful, open the failover cluster manager. Click on the cluster you created (1). Right-click on the IP address under cluster core resources and click on Properties (2). Select the Static IP address and input one of the secondary IP address from the node, which is in 172.31.32.0/20 subnet.

Similarly, right-click on the cluster network 1 IP address and click on Properties. Select the static IP address and input one of the secondary IP addresses from another node.

Cluster IP addresses

Now bring the cluster resource online.

Cluster resource

Now let us configure the file share witness. Right-click on the cluster (1) and select More Actions (2). Finally, click on Configure Cluster Quorum Settings… (3).

Fileshare witness for cluster

Click on the Next button. Select the Quorum witness and click Next. Select configure a file share as witness and click Next. Input the file share path and click Next.

Creating Always on Availability Group

Log in to the node 1 using the remote desktop protocol (RDP) and open SQL Server configuration manager. Click on SQL Server Services and right-click on SQL Server service. Click on Properties. Navigate to AlwaysOn High Availability tab and Enable AlwaysOn Availability groups and restart the SQL Server services. Repeat the same steps on node 2 to enable AlwaysOn Availability Groups.

Enable Always-On Availability groups

Open SQL Server management studio (SSMS) on node-1 and log in to the SQL server. Navigate to Always On High Availability and right-click on it and select New Availability Group Wizard. Click on the Next button.

Enter the Always On Availability group’s name and click on the Next button.

Availibilty group name

Select the databases that need to be added to the availability group and Add the secondary node as Replica. In the listener tab, enter the listener name and add the secondary IP from both nodes which are not used. Click Next.

AOAG listner

Select the type of data synchronization as per your choice. In this case, I used a full database backup and log backup. Click Next and Finish.

After successful creation of Always on Availability group, navigate to the availability group in SQL Server management studio (SSMS). Right-click on the availability group and click on Show Dashboard to see the health of Always on Availability groups. Please refer to the below image for the dashboard.

AOAG health

To test the failover, I shutdown the current primary node and connected using Always On listener name from SQL Server management studio (SSMS). Please refer to the below image for the Always-On dashboard after automatic failover. We can see that the previous primary node is shut down, and the previous secondary node became the primary node after automatic failover. The listener will serve requests from the listener IP address of the currently active node.

Always on failover

Conclusion

In this article, we explored how to set up multi-subnet Always On Availability Groups using AWS EC2 instances. In case you have any questions, please feel free to ask in the comment section below. To continue you learning about Always On Availability Groups, Please refer to the AlwaysOn Availability Groups category.

Jignesh Raiyani
1,079 Views