SQL Server with WSFC on AWS
SQL Server with WSFC Quick Start

Best Practices

The architecture built by this Quick Start supports AWS best practices for high availability and security.

High Availability and Disaster Recovery

Amazon EC2 provides the ability to place instances in multiple locations composed of AWS Regions and Availability Zones. Regions are dispersed and located in separate geographic areas. Availability Zones are distinct locations within a region that are engineered to be isolated from failures in other Availability Zones and that provide inexpensive, low-latency network connectivity to other Availability Zones in the same region.

By launching your instances in separate regions, you can design your application to be closer to specific customers or to meet legal or other requirements. By launching your instances in separate Availability Zones, you can protect your applications from the failure of a single location. WSFC provides infrastructure features that complement the high availability and disaster recovery scenarios supported in the AWS Cloud.

Automatic Failover

Deploying the Quick Start with the default parameters configures a two-node automatic failover cluster with a file share witness. On this cluster, it deploys an Always On Availability Group with two availability replicas.

          SQL Server Always On Availability Groups and automatic failover

Figure 3: SQL Server Always On Availability Groups and automatic failover

The Quick Start implementation supports the following scenarios:

  • Protection from the failure of a single instance

  • Automatic failover between the cluster nodes

  • Automatic failover between Availability Zones

However, the Quick Start default implementation doesn’t provide automatic failover in every case. For example, the loss of Availability Zone 1, which contains the primary node and file share witness, would prevent automatic failover to Availability Zone 2. This is because the cluster would fail as it loses quorum. In this scenario, you could follow manual disaster recovery steps that include restarting the cluster service and forcing quorum on the second cluster node (e.g., WSFCNode2) to restore application availability. The Quick Start also provides an option to deploy into three Availability Zones. This deployment option can mitigate this loss of quorum in the case of a failure of a single node. However, you can select this option only in AWS Regions that include three or more Availability Zones; for a current list, see the AWS Global Infrastructure webpage.

We recommend that you consult the Microsoft SQL Server documentation and customize some of the steps described in this guide or add additional ones (e.g., deploy additional cluster nodes and configure them as readable secondary replicas) to deploy a solution that best meets your business, IT, and security requirements.

Security Groups and Firewalls

When the EC2 instances are launched, they must be associated with a security group, which acts as a stateful firewall. You have complete control over the network traffic entering or leaving the security group, and you can build granular rules that are scoped by protocol, port number, and source or destination IP address or subnet. By default, all traffic egressing a security group is permitted. Ingress traffic, on the other hand, must be configured to allow the appropriate traffic to reach your instances.

The Securing the Microsoft Platform on Amazon Web Services whitepaper discusses the different methods for securing your AWS infrastructure. Recommendations include providing isolation between application tiers using security groups. We recommend that you tightly control ingress traffic in order to reduce the attack surface of your EC2 instances.

Domain controllers and member servers require several security group rules to allow traffic for services such as AD DS replication, user authentication, Windows Time services, and Distributed File System (DFS), among others. The WSFC nodes running SQL Server will need to permit several additional ports to communicate with each other as well. Finally, instances launched into the application server tier will need to establish SQL client connections to the WSFC nodes.

The Quick Start creates a number of security groups and rules for you. For a detailed list of port mappings, see the Security section of the Active Directory deployment guide, and the Security section of this guide.

In addition to security groups, the Windows firewall also needs to be modified on the SQL Server instances. During the bootstrapping process, a script will run on each instance that opens the TCP ports 1433, 1434, 4022, 5022, and 135 on the Windows firewall.