HA and DR Solutions for SQL Server Cloud Based Implementations
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.
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).
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.
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.
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.
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.
- Review these related tips:
- SQL Server Hybrid High Availability and Disaster Recovery
- Implement a SQL Server HA failover solution without shared storage
- Visit the SIOS Technology website to learn more
MSSQLTips.com Product Spotlight sponsored by SIOS Technology
Last Updated: 2014-10-14
About the author
View all my tips