Creating an Oracle RAC like structure with SQL Server Technology

It is no secret that Oracle’ RAC platform has been a best seller in the enterprise database category. Among other things,

1. Lowers overall cost of ownership by providing a single platform to collapse databases onto.
2. Provides out of the box High Availability – and (automatic) Scalability options.

SQL Server has been trying to play catch up for a while. With the release of their AlwaysOn SQL Server cluster feature, they are starting to come close. This post examines implementing a SQL Server RAC like structure on AWS (which offers a few additional optimizations).

Collapsing SQL Server

AWS Specific SQL Collapse – Some advantages of AWS

• EBS Optimized Instances – I/O optimized
• Enhanced Networking on windows.
• AWS Placement Groups – logical grouping of instances within a single Availability Zone. Placement groups are recommended for applications that benefit from low network latency, high network throughput, or both. 

SQL Always-On High Availability Group

Highlights

1. Each set of availability databases is part of an availability replica. These come in two flavors : a single primary replica. which hosts the primary databases, and one to eight secondary replicas, each of which hosts a set of secondary databases. The secondary dbs can serve as potential failover targets.
2. An availability group fails over at the level of an availability replica. An availability replica provides redundancy only at the database level—for the set of databases in one availability group.
3. Failovers are not caused by database issues such as a database becoming suspect due to a loss of a data file or corruption of a transaction log.
4. Relies on underlying Windows Clustering.  AlwaysOn Availability Groups requires a Windows Server Failover Clustering (WSFC) cluster. Each availability replica of a given availability group must reside on a different node of the same WSFC cluster.

SQL Always-On in AWS

AWS Whitepaper

Highlights

1. You must deploy the cluster nodes inside an Amazon VPC.
2. You must deploy WSFC cluster nodes in separate subnets. Keeping these two key requirements in mind, we provide instructions for deploying the WSFC and an AlwaysOn Availability Group.
3. We call out any AWS-specific considerations along the way. First, we provide implementation guidance for setting up AD DS in an Amazon VPC. Next, we walk you through the steps necessary to configure a two-node automatic failover cluster with a file share witness.
4. On this cluster, we then deploy an AlwaysOn Availability Group with two availability replicas.
5. The goal of this configuration is to protect from the failure of a single instance. Other failover cluster and availability group configurations are possible to serve either high availability (HA) or disaster recovery (DR), or both scenarios together.