Learn more about SQL Server tools

mssqltips logo
 

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

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

HA and DR Solutions for SQL Server Cloud Based Implementations


By:   |   Last Updated: 2014-10-14   |   Comments (2)   |   Related Tips: More > Clustering

Problem

SQL Server continues to evolve as new features are added with each release.  With these changes database administrators and developers need to rethink the "business as usual" mantra and determine how these new features are a benefit and not a detriment.  Over the last several releases of SQL Server we have been introduced to encryption, compression, common language runtime (CLR) and most recently memory-optimized tables, just to name a few. 

In addition to the new features, the deployment of SQL Server has greatly changed over the last several years.  Everyone used to have standalone SQL Server machines with dedicated storage.  Then SAN storage was introduced and more and more implementations used SAN storage versus dedicated storage. Then SQL Server was moved to virtual machines and over time more and more SQL Servers have been virtualized.  The most recent shift in SQL Server implementations is cloud based.

Another shift over the years has been the introduction of many third party SQL Server tools that allow us to take the rich feature set that Microsoft provides and extend these even further.  There are solutions for monitoring, performance, auditing, high availability and more.  These tools let us to take full advantage of SQL Server by allowing us to do things that were very difficult and time consuming to things we didn't even know were possible. 

With each of these steps along the SQL Server implementation evolution, there have been bumps along the way as well as many naysayers that said anyone going down these paths was just asking for trouble.  But as we all know, databases haven't come to a screeching halt and these new technologies have actually made implementation and administration even better.

So now that we are on this cloud based SQL Server path, one of the most typical cloud based use cases is for high availability and disaster recovery. Having the ability to have an offsite copy of your data in the cloud as well as the ability for quick failover allows us to extend our infrastructure with minimal cost and time.  In this article we will look at some options for cloud based high availability and disaster recovery using #SANLess clustering solutions from SIOS Technology.

Solution

When talking about high availability and disaster recovery the most common and robust solution for SQL Server is Windows Server Failover Clustering (WSFC). This allows you to have a multi-node server configuration, so if the primary node goes down one of the other nodes can take over with minimal downtime.  This solution also allows you to protect the entire SQL instance, unlike other solutions such as log shipping, database mirroring or AlwaysOn Availability Groups that only protect one user database at a time. 

The downside with WSFC is that it requires you to use shared storage which is not an option when trying to span on-premises to cloud based implementations.  Also, when implementing your SQL Server platform completely in the cloud, the cloud based provider probably offers separate and redundant data centers, but they do not offer shared storage which is needed to implement a traditional WSFC implementation.

SIOS #SANLess software overcomes these shortcomings. It eliminates the need to have shared storage by synchronizing data between servers (whether in the cloud, on-premises or both) using block level replication. The synchronized storage then appears as shared storage to WSFC.  This therefore allows you to have multiple copies of the data in different geographic areas if the solution is cloud based only or a copy on-premises and another copy in the cloud if using a hybrid model.

In the next few sections we will talk about a few different scenarios on how to use SIOS #SANLess clusters.

Failover from On-premises to the Cloud

Not everyone has made it to the cloud yet for their SQL Server instances, so there are still plenty of on-premises implementations out there.  The one thing that has not been done with many of these instances is to setup a HA or DR solution.  Therefore when deciding to implement a HA or DR solution, you should consider the cloud as that option.  With the use of SIOS #SANLess software you can have your on-premises server take care of your day to day database needs, but know that a DR environment exists in the event of a failure.

By using SIOS #SANLess software in the cloud, you can implement a two node solution for SQL Server as shown below. This solution allows you to keep your on-premises server, but also set it up as a two node failover cluster with the second node in the cloud.  You can use your current locally attached storage and use the cloud for DR without the cost of building out a recovery site (on-premises and cloud). 

failover clustering on-premises to the cloud
SQL Server - 2 Node Cluster

To make the solution even more robust, if you are using SQL Server Enterprise edition you can setup a three or more node cluster. As the image below depicts, you can have a traditional two node cluster on-premises using shared or non-shared storage and introduce a third node in the cloud for DR purposes.  The SIOS #SANLess software will replicate the data between the nodes to keep the copies current in the event of a failure.  This way you have a local HA solution for immediate failover as well as a secondary failover strategy to the cloud.

failover clustering locally and to the cloud
SQL Server Enterprise - Multi-Node Cluster

Windows Server Failover Clustering in the Cloud

If you have already implemented your database solution in the cloud you still need a HA solution.  Ideally you would want to have your nodes in different data centers, so if there is a failure your entire infrastructure doesn't stop working. In an AWS EC2 cloud, the cluster nodes will most likely be in different subnets, which poses another problem.

Since SQL Server 2012, the Enterprise edition natively offers a solution that allows you to use multi-subnet clusters. This feature allows you to have the WSFC cluster nodes in different subnets, but still communicate for HA and DR purposes.  Even though the cluster can be setup, you still face the challenge of providing shared storage in the cloud.  By using SQL Server Enterprise edition along with SIOS #SANLess software you can create a cluster that spans subnets.  Now you have a cloud solution along with HA and DR options that take full advantage of the WSFC features.

failover clustering in the cloud using SQL Enterprise edition

Clustering in the AWS Cloud using SQL Server Standard edition

So what if you only have SQL Server Standard edition (or want to keep using just the Standard edition) and you want to have a DR solution for your cloud based SQL Server?

Once again, the ideal solution would be to have the data reside in different geographic zones, this way if one site goes down your entire organization doesn't go down with it. In order to do this, the servers would most likely be on different subnets. As mentioned above, in order to utilize WSFC across subnets in an AWS cloud, you have to use SQL Server Enterprise edition. To get past this requirement, you can setup a two-node cluster using SIOS #SANLess software (SIOS Protection Suite) to manage both the failover and create a SANLess cluster. You get HA and DR protection without the cost of SQL Server Enterprise edition.

failover clustering in the cloud using SQL standard edition

Summary

All of these scenarios and more are possible using SIOS #SANLess software.  Whether your servers still reside completely on-premises, a mix of on-premises and cloud or you have totally moved to the cloud, there is a HA / DR solution that will work for you using SIOS Technology.

Next Steps

MSSQLTips.com Product Spotlight sponsored by SIOS Technology



Last Updated: 2014-10-14


get scripts

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




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.



    



Thursday, October 16, 2014 - 3:03:10 PM - Prashant Back To Top

Excellent Article. very well explained. Keep the good work and share your knowledge


Wednesday, October 15, 2014 - 3:43:03 AM - Ashoke Kulkarni Back To Top

 

 

Excellent coverage. Keep it up.

T&R,

Ashoke

(M) :+91 9820457530

DID: +912240323152


Learn more about SQL Server tools