During my time as part of the AWS Professional Services SAP Global Specialty Practice, one of the key things I’ve found that customers look for is a high availability (HA) set up for their business/mission-critical SAP applications. In this blog post, we will talk about the HA design and configuration option for customers running the SAP workloads on Windows with the Microsoft SQL Server.

The Windows operating system provides a clustering feature that can be used to configure HA for the database layer of the SAP workload running on Microsoft SQL Server. This feature enables customers to deploy a HA cluster across AWS Availability Zones (AZs) in a region for SAP NetWeaver based applications.

Prerequisites

1. Three nodes are required to configure HA
   • Two nodes will work as the primary and secondary database nodes
   • The third node will work as a witness server to maintain quorum for high availability cluster
2. All three nodes are connected to Active Directory and part of the same Active Directory domain
3. Nodes have the Windows failover clustering feature enabled
4. One shared directory between all three nodes
   • This directory will be used by the witness node to keep information about the database node’s status
   • Amazon FSx can be used to share a directory between cluster nodes if you are planning to deploy Microsoft SQL Server nodes and witness servers in two AZs
5. Allocate four additional IPs for the Windows cluster and SQL Server listener
   • These IPs should be part of the same private subnets as the primary and secondary in different AZs where we have primary and secondary DB deployed
   • The additional IPs should be assigned as secondary IPs to primary and secondary DB nodes

List of required IP’s

High Availability design

We will use multiple AWS Availability Zones to configure HA for the SQL database nodes. AWS Availability Zones which are physically separated by a meaningful distance, many kilometers, from any other Availability Zones, although all are within 100 km (60 miles) of each other. Availability Zones has a low latency link and meets customer requirements for HA requirements.

Cluster Configuration

We need to configure the Windows cluster set up first, and these steps can be performed from the primary or secondary database. Use Windows Failover cluster manager feature (Follow Windows standard documentation) to complete Windows clustering configuration. You should see both primary and secondary nodes available after Windows clustering is configured.

Note: You also need to complete the Windows Quorum configuration. Quorum helps to maintain cluster integrity in split-brain scenarios. A file share witness quorum can be used with sharing a directory between primary, secondary, and witness nodes to maintain a quorum of the cluster.

SQL AlwaysOn Configuration

Microsoft SQL Server provides a native mechanism to replicate data between primary and secondary databases. We will use this feature to synchronize DB nodes between Availability Zones and Windows Cluster feature will be used for automatic failover of the database between the primary and secondary node in case the availability zone becomes unavailable.

First, you need to complete the AlwaysOn configuration using Microsoft SQL server native tool SQL Management studio.

You will see both nodes added to cluster configuration and their database synchronization status after AlwaysOn Configuration is complete.

Failover Testing

Microsoft SQL Server provides native tool SQL Server Management studio (SSMS) to test failover testing of SQL Server AlwaysOn High Availability Group

Please use SSMS Failover Wizard to perform failover testing

You can see the role of SAPNODE2 is changed from secondary to primary, This confirms successful testing failover cluster.

To test behavior of unplanned outage, You can shut down EC2 node in Availability Zone to document the behavior and failover timings. Using this described approach, we were able to configure HA availability configuration for customer’s critical SAP workload on SQL within 4 hours of efforts and failover/failback experience was also smooth with no interruption to end-users.

Conclusion

In this blog, we have shown how to configure a Microsoft SQL Server Database for High Availability using the Availability group and Windows Cluster software for SAP Application on AWS. This blog also helps to design SAP Application on AWS with Multi-AZ architecture that provides high availability with Windows native clustering feature.

Customer running Microsoft SQL Database on AWS can also use SQL AlwaysOn with FCI to configure High Availability.

Let us know if you have any comments or questions—we value your feedback.

————————————————————————————–