Always On Failover Cluster for SQL Server on AWS Cloud

Hardware, Network connectivity, Operating System, applications, or services failure may affect availability. Through this document we describe how to configure Windows Server Failover Clustering (WSFC) on AWS Cloud to achieve resilience for MS SQL Server running on EC2 Instances.

Configuration Summary

• A virtual private cloud (VPC) configured with public and private subnets across two availability zones. This provides the network infrastructure for SQL Server deployment. Optionally you may choose a third Availability Zone for the file-share witness or for an additional SQL Server cluster node.
• In the public subnets, Windows Server–based Remote Desktop Gateway (RD Gateway) instances and network address translation (NAT) gateways for outbound Internet access will be deployed.
• In the private subnets, Active Directory Domain Services (AD DS) domain controllers Will be deployed and required DNS records are created.
• In the private subnets, Windows Server-based instances as WSFC nodes and SQL Server Enterprise edition with SQL Server Always On availability groups on each node. This architecture provides redundant databases along with a witness server.

Preparing EC2 instances

• Launch two EC2 Instances in two different AZ in same region (Window Server 2019 or higher with SQL Server 20 Enterprise Edition). You may opt BYOL.
• Add two secondary IP in each node. One IP to be used for Windows Server Failover Clustering and second for always On availability group listener.
• Add both the nodes to AD domain, and restart.
• Install failover cluster features through Server Manager in both the nodes.

Amazon FSx for Windows File Server

Amazon FSx for Windows File Server provides simple, fully managed, highly reliable file storage that’s accessible over the industry-standard Server Message Block (SMB) protocol.

• Create a file system in multi-AZ, in same VPC and subnets where EC2 instances launched.
• Select ‘Self-managed Microsoft Active Directory’ option under Windows Authentication and fill domain name, DNS Server IP Address, service account username and password having permissions to create objects in Microsoft AD.

Creating Windows Server Failover cluster

• Login to node-1 (through RDP) and launch failover cluster manager and click on ‘create cluster’, follow instructions and add both the nodes.
• Select ‘Yes’ to run validation test and review the validation report.
• 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.
• Upon successful creation of the cluster, Open failover cluster manager. Click on the cluster you created. Right-click on the IP address under cluster core resources and click on properties. Select the Static IP address and input one of the secondary IP addresses from the node. 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.
• Bring the cluster resource online.
• Configure the file share witness, Right-click on the cluster and select More Actions, as final step, click on Configure Cluster Quorum Settings.

Creating Always on Availability Group

• Login to node-1 (through RDP) and open ‘Sql Server Configuration Manager’.
• Click on SQL Server Services
• On the right pane, Right-click on SQL Server and click on ‘Properties’.
• Navigate to ‘AlwaysOn High Availability’ tab and tick the “Enable AlwaysOn Availability Groups” option.
• Restart the SQL Server services.
• Repeat the same steps on node 2 to 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’, right-click on it and select New Availability Group Wizard. Click on the Next button.
• Enter the Availability group name (for example EC2AG1) and click on the Next button.
• Select the databases that need to be added to the availability group.
• Add the secondary node as Replica.
• In the listener tab, enter the listener DNS name and add the secondary IP from both nodes which were not used earlier and Click Next.
• Select the databases that need to be added to the availability group.
• 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.

Test Resilience

• Connect SQL Server Management Studio (SSMS) using Always on Listener Name.
• One node is acting as Primary and another as Secondary.
• Shutdown/restart the primary node.
• You will notice that secondary node has automatically became primary. The listener will serve requests from the listener IP address of the currently active node.

Conclusion

In this blog, we explored how to set up multi-az Always On Availability Groups using AWS EC2 instances. Please follow AWS WAF and best practices to setup VPC, launch EC2 Instances and FSx for Windows.

In case you have any questions, please feel free to ask in the comment section below.