By: Greg Robidoux | Last Updated: 2017-07-26 | Comments (2) | Clustering
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.
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.
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.
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.
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.
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.
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.
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.
To learn more and to get started, check out the following links:
- AWS Quick Start with SIOS DataKeeper
- SIOS DataKeeper high availability in the cloud
- Quick Start deployment guide
- On-Demand MSSQLTips Webinar
- SIOS Website and the SIOS DataKeeper product page
MSSQLTips.com Product Spotlight sponsored by SIOS Technology
Last Updated: 2017-07-26
About the author
View all my tips