Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Quick Start Deployment of a SQL Server Windows Failover Cluster on Amazon AWS


By:   |   Last Updated: 2017-07-26   |   Comments (2)   |   Related Tips: More > Clustering

Problem

Adoption of the cloud is moving fast and there are now several cloud platform providers to choose from.  Also, the shift to move SQL Server to the cloud has been catching on and many companies are interested in implementing SQL Server solutions on cloud platforms such as Microsoft Azure, Amazon AWS, Google Cloud Platform and others. This seems to be a growing trend, so companies are looking at ways to deliver high availability for SQL Server solutions in the cloud.

Although cloud providers offer several solutions to assist with high availability on their environments and servers, SQL Server adds additional challenges for high availability due to the nature of database transactions as well as the need to keep these business-critical systems up and running with minimal to no downtime.  For those of you that often implement a Windows Server Failover Cluster (WSFC) for SQL Server for on-premises installations, the cloud introduces issues such as the lack of shared storage to implement a WSFC along with all of the other nuisances with implementing SQL Server in the cloud.  Due to the complexity of setting up a proper high availability solution on the cloud, DBAs are often reluctant to do cloud implementations.  In this tip, we will look at how to implement a Windows Server Failover Cluster for SQL Server in the cloud with minimal effort to ease some of these concerns.

Solution

SQL Server on cloud platforms such as Azure, AWS and Google Cloud are on the rise, but the need to protect SQL Server still exists.  Windows Server Failover Clusters have been the mainstay for solid high availability implementations, but the nature of the cloud infrastructure limits the use of WSFC due the lack of share storage.

Cloud Shared Storage Limitation

Below is a typical two node cluster for SQL Server.  The image depicts both servers sharing storage which is the norm for implementing a WSFC, but the cloud doesn’t offer shared storage, so this is not a viable solution.

two node failover cluster with shared storage

SIOS DataKeeper Solution

What is available in the cloud are servers with their own allocated storage, but there needs to be a way to share the data between the nodes of the cluster. This is where SIOS DataKeeper Cluster Edition comes into play.  We can still implement a Windows Server Failover Cluster like we normally do, but with SIOS DataKeeper the data between the nodes is synchronized through host-based replication instead of shared storage.  Within the Failover Cluster Manager, the storage looks shared and the cluster is configured and administered the same way as any other WSFC.  SIOS DataKeeper replicates the data both ways, so if you failover from one node to another, SIOS DataKeeper will automatically change the way the data replicates so the nodes are always in synch.

two node failover cluster with replicated storage

AlwaysOn Availability Groups (AG) vs. AlwaysOn Failover Clustering Instances (FCI)

To offer high availability for SQL Server, Microsoft offers both Availability Groups and Failover Clusters, but there some key differences in how these work and what they offer.

When implementing SQL Server in the cloud you do have the option of implementing Availability Groups to protect SQL Server, but there are several advantages of implementing a Windows Server Failover Cluster instead such as:

  • Support for SQL Standard Edition – SQL Server Standard allows you to implement a two node cluster, whereas Availability Groups require SQL Server Enterprise Edition.  With SQL Server 2016 there is the option to implement a Basic Availability Group, but this only supports one database. The cost difference between SQL Server Standard and Enterprise is vast, so this can be a big cost savings.
  • Distributed Transactions – Availability Groups have limited support for distributed transactions, so if your applications and databases use distributed transactions a WSFC is a better option.
  • Unlimited Databases – Availability Groups have a limitation on how many databases can be used.  This is because of the additional overhead that is put on SQL Server.  With a WSFC there is no limit.
  • Protection of System Databases – Availability Groups only work for user databases, so you need to configure and manually keep things in synch on both servers such as logins, SQL Agent jobs, operators, alerts, database mail settings, etc.  With a WSFC all databases including the system databases are kept in synch, so you don’t need to maintain both servers separately.
  • Replication of other data – With the use of SIOS DataKeeper you can also replicate other data stored outside of SQL Server, such as images, blobs, etc.
  • Less administration – Also, Availability Groups take additional time to setup, configure, maintain and monitor. 

SQL Server Cluster on AWS using SIOS DataKeeper

As mentioned earlier, cloud providers offer several options to protect servers running on their platform by providing geographically separate environments (i.e., availability zones, fault domains, or zones). It is still a best practice to take advantage of what they have to offer along with implementing a cluster in the cloud to further protect SQL Server. 

In the image below, we can see that each node of the cluster along with the file share witness are deployed to different AWS availability zones. This ensures that if one of the cloud provider’s availability zones has issues, the cluster components that are deployed in the other availability zones can still perform as needed. 

SIOS DataKeeper can then replicate the data between the different nodes that are deployed in the different cloud provider availability zones.

amazon aws windows cluster implementation

AWS Quick Start Deployment Templates

Getting started with SQL Server in the cloud and implementing a cluster in the cloud can be quite a daunting and time-consuming task. This is why many have shied away from this type of implementation, whether it be to implement a production server or just for proof-of-concept. This is where the AWS Quick Start Templates come into play.

AWS Quick Start deployment templates allow you to easily implement a two node SQL Server cluster using SIOS DataKeeper. Specific benefits include:

  • Automate deployments for key workloads
  • Launch, configure and run the AWS service to deploy a workload
  • Use AWS best practices for security and availability
  • Eliminate manual steps and mistakes
  • Use pre-defined Amazon Machine Images (AMI) to do the following:
    • Add to existing deployment
    • Deploy a new SQL Server two node cluster with SIOS DataKeeper
    • Rapid deployment for proof of concept

SIOS DataKeeper Cluster Edition on AWS

This AWS Quick Start with SIOS DataKeeper sets up an AWS architecture for SIOS DataKeeper Cluster Edition and deploys it into your AWS account in a few easy steps.

This Quick Start uses AWS CloudFormation templates to deploy SIOS DataKeeper Cluster Edition into a virtual private cloud (VPC) in a single AWS Region, across two Availability Zones. You can build a new VPC for SIOS DataKeeper, or deploy the software into your existing VPC.

Below you can see the AWS Quick Start for SIOS DataKeeper Cluster Edition.  You can simply click on the Deploy now button to get started or review the deployment guide for additional information.  You can view the Quick Start guide here.

sios datakeeper cluster edition on aws

Below is a depiction of what this Quick Start implementation looks like in AWS.  This may seem like a lot is going on, but most of this is to configure IPs and permissions that are required to run on AWS. You can see there are two different availability zones that are used and there are two nodes for the Failover Cluster. The Failover Cluster nodes will both have SQL Server installed and SIOS DataKeeper installed and configured.

aws failover cluster configuration map

You have two different options for the implementation. You can bring your own license (BYOL) or use the pay as you go (PAYG) option which includes the SQL Server license.  Both options run on Windows Server 2012 R2 and the option with SQL Server includes SQL Server 2014 Standard edition.  Additional, AMIs are being configured and will be available in the very near future.

sios datakeeper amazon aws ami options

Summary

As you can see, implementing a two node SQL Server cluster on AWS with SIOS DataKeeper has been simplified due to the use of the AWS Quick Starts.  This allows you to spend less time preparing and focusing more on implementing a quicker deployment.

SIOS is also working with other cloud providers to build automated deployment templates to allow you to implement a cluster anywhere with minimal effort.

Next Steps

To learn more and to get started, check out the following links:

MSSQLTips.com Product Spotlight sponsored by SIOS Technology



Last Updated: 2017-07-26


next webcast button


next tip button



About the author
MSSQLTips author Greg Robidoux Greg Robidoux is the President of Edgewood Solutions and a co-founder of MSSQLTips.com.

View all my tips
Related Resources


Become a paid author


Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Wednesday, July 26, 2017 - 5:55:47 PM - Clifton S Dunaway Back To Top

 As a Senoir SQ Server DBA, at a prior job, I was asked to create a PowerShell script to spin up a 3-node cluser int he Amazon AWS site. I did this all with PowerShell! it spins up 3 nodes, in 3 AZs. It will configure SQL Server, Create a cluster, join each node to the cluster, Load all standard SQL jobs, Setup TDE encryption. All this is called from a single script that would kick it all off. 

 

 

Wednesday, July 26, 2017 - 9:55:53 AM - David Koth Back To Top

Greg,

A few comments as we leverage AGs extensively.  Three node Windows Clusters with 3 AGs and read-able secondaries that maintain ~1,200 distinct databases across each cluster.  The environments are all in AWS.

Unlimited Databases - both solutions come down to understanding the activity on the servers and the resources, nothing is just unlimited.  MS documentation was even updated from the early times from "100 databases/10 AGs" to basically "mind your resources."

In addition, the notion of "unlimited databases" does not take into account the failover time.  If you are the  last database to go through recovery where there are 1,200 databases present, the HA aspect is seen as quite slow.  AG's failover much quicker.  We experience failover times with AGs that were painful before when on FCIs.

All else was spot-on, but as a very heavy user of the AGs, I thought I would throw some comments out there.

David

 

 

Learn more about SQL Server tools