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

 

Implement a SQL Server HA failover solution without shared storage


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

Problem

One of the primary duties for a SQL Server DBA is to ensure that the database server stays online without causing disruption to users. DBAs do all types of things to improve performance, such as using new indexing strategies, maintaining indexes, optimizing code, and separating data to different file groups and disks, etc. DBAs also put a lot of effort into making sure that databases are backed up in case there is a loss of data or some type of data corruption.

DBAs also need to implement some type of technology to improve the overall availability of both the database and the SQL Server instance. SQL Server offers built-in options such as log shipping, replication, database mirroring and AlwaysOn Availability Groups. and when you need maximum protection, there is Windows Server Failover Clustering.

The advantages of Windows Server Failover Clustering is that, in the event of a hardware failure, your instance of SQL Server can fail over to a secondary server with minimal downtime to the end users. The downside to setting up a traditional failover cluster is that you need shared storage, such as a SAN, so that all nodes in the cluster can access the shared storage. The problem is that having a SAN or shared storage may be an option for some environments, but not all (i.e., cloud, virtual server, high performance storage). Another option is to use AlwaysOn Availability Groups to replicate SQL Server databases and eliminate the need for shared storage.  The downside to this feature is that it is a SQL Server Enterprise edition feature, so it’s not an option if you are using Standard Edition.

So what options are available to setup a Windows Server Failover Cluster without using shared storage either for SQL Server Standard or Enterprise editions?

Solution

Before we get into how this can be done without shared storage, let's discuss Windows Server Failover Clustering options for SQL Server and what is available. The table below shows you what is supported and the maximum number of nodes supported for each version and edition of SQL Server.

Version - Edition Cluster Support

SQL Server 2008 - Enterprise

up to 8 nodes on Windows 2003
up to 16 nodes on Windows 2008

SQL Server 2008 - Standard

2 nodes

SQL Server 2008 R2 - Enterprise

up to 8 nodes on Windows 2003
up to 16 nodes on Windows 2008

SQL Server 2008 R2 - Standard

2 nodes

SQL Server 2012 - Enterprise

up to OS maximum

SQL Server 2012 - Business Intelligence

2 nodes

SQL Server 2012 - Standard

2 nodes

SQL Server 2014 - Enterprise

up to OS maximum

SQL Server 2014 - Business Intelligence

2 nodes

SQL Server 2014 - Standard

2 nodes

A node is a separate server that allows SQL Server operations to continue in the event of a server failure. As you can see above, the Standard and Business Intelligence editions only support two-node clusters, where Enterprise supports more than two nodes.

Typical Two-Node Cluster

The diagram below represents a typical two-node failover cluster. Basically there are two servers (nodes) that have their own dedicated hardware and if the active node fails, the cluster will automatically failover to the secondary node. This is also true for failback from the secondary to the primary node. The one component that is not dedicated is the storage. Both nodes shares the same storage, so there is only one copy of the SQL Server databases. If the primary node is active it will be reading and writing the transactions and the secondary node is in a passive (non-active) mode.

typical two node sql server cluster

A few of the disadvantages to this configuration are:

  • A single point of failure for the data. Since the storage is shared, if there are any issues with the storage it can take down the entire cluster and everything will come to a halt, regardless of how many nodes you have implemented.
  • Geographical distance - You are limited by how far apart the nodes in the cluster are from each other. So if the nodes are in the same data center and there is an issue at the data center, the entire cluster could become unusable.
  • Implementing SAN storage can get very expensive for both the initial purchase and ongoing administration.

SANLess Two Node Cluster

The diagram below shows a SANLess two-node cluster where each node has its own storage. The implementation of Windows Server Failover Clustering for SQL Server is setup, configured and supported in the usual way, but this gives you the flexibility to implement clustering with servers that are not attached to shared storage and the ability to replicate your data to geographically dispersed data centers.

sanless two node cluster

A few of the advantages to this configuration are:

  • You don't need to implement a shared storage solution such as a SAN, which can greatly reduce the overall costs.
  • By having multiple sets of data, the storage does not become the single point of failure.
  • You have the ability to have duplicates sets of data without having to purchase the Enterprise edition and implementing AlwaysOn Availability Groups.
  • The data can now be in physically separate data centers, so if there is a data center failure all the servers are not effected (no single point of failure)
  • If servers are virtualized you can easily implement a failover cluster with this approach.
  • You can take advantage of fast SSD storage for the fastest SQL Server solution.

Clustering to the Cloud

As the cloud becomes more and more mainstream and companies are implementing cloud solutions for disaster recovery and high availability, what about the possibility of clustering your SQL Server both locally and in the cloud? Here is an implementation of a two-node cluster using non-shared storage and also replicating data to the cloud for a failover solution. This gives you local failover, but also a second level of failover to the cloud for disaster recovery without the cost of a DR site or second data center.

failover cluster to the cloud

Clustering in the Cloud

Although some companies are slower to move their SQL Servers to the cloud, some have already moved down that path entirely or have at least migrated some of their SQL Server instances to the cloud. With this solution you also have the ability to cluster and replicate data in the cloud to implement a failover solution.

failolver cluster in the cloud

How to implement a SANLess Cluster

All of these options and more are possible today using SIOS DataKeeper solutions. SIOS DataKeeper Cluster Edition provides seamless integration with Windows Server Failover Clustering with full support for SQL Server as well as other critical business applications. You can build a #SANLess cluster and reduce your costs as well as provide full support for cloud or hybrid implementations.

Next Steps

MSSQLTips.com Product Editorial sponsored by SIOS Technology Corp., makers of SIOS DataKeeper Cluster Edition.



Last Updated: 2014-05-14


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




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, June 14, 2018 - 4:39:03 PM - David Bermingham Back To Top

Barney,

I'm not sure what is misleading about the title "Implement a SQL Server HA failover solution without shared storage". This article goes on to explain how to create a SQL Server Failover Cluster Instance (FCI) without shared storage, which is exactly what the title promises.

As you mentioned, Always On Availability Groups does require a Windows Server Failover Cluster as the framework to provision Availability Groups and also does not use shared storage. In most cases it also uses a File Share Witness or Cloud Witness for quorum configurations. The three options for cluster quorum witnesses are Disk, File Share or Cloud. 

The article mentions upfront that Availability Groups are an HA option, but if you are looking for a less expensive option than upgrading to SQL Server Enterprise, or need full support for DTC, or require replicating the Master and MSDB databases as well as user databases, then Availabilty Groups may not be right for you and a SANless SQL Server FCI with SIOS DataKeeper would be a better option.

I'm not sure what you are considering a single point of failure? In the description there are multiple copies of your data and no single point of failure. Can you elaborate?

Microsoft themselves list the SIOS DataKeeper solution as a solution for building SQL Server Failover Clusters in Azure, where shared storage is not available. See their documentation here. https://docs.microsoft.com/en-us/azure/virtual-machines/windows/sql/virtual-machines-windows-sql-high-availability-dr

SIOS has been doing this since 1999 and in fact early versions of the SIOS HA solution were actually availble from NCR, before Wolfpack (the original MSCS) was released, so it is anything but a hack.

David Bermingham
Senior Technical Evangelist, SIOS
Microsoft Cloud and Datacenter Management MVP


Thursday, June 14, 2018 - 12:53:37 PM - Barney Back To Top

 Misleading title, SQL AO REQUIRES WSFC and making a Windows Cluster without shared storage is a HACK. Bypassing a default quorum setup for some spoofed witness file share or whatever is still sharing storage between nodes and if you're not careful is a serious point of failure.


Monday, May 08, 2017 - 8:13:39 AM - Greg Robidoux Back To Top

Hi John,

If you have a second server that is used for just failover you don't have to have a second license for SQL Server.  The secondary SIOS node is a not an active SQL Server node, so you only need to license the active node.

Here is info from the Microsoft SQL Server 2016 licensing guide:

The secondary server used for failover support does not need to be separately licensed for SQL Server as long as it is truly passive, and the primary SQL Server is covered with active SA. If it is serving data, such as reports to clients running active SQL Server workloads, or performing any “work”, such as additional backups being made from secondary servers, then it must be licensed for SQL Server.

-Greg


Monday, May 08, 2017 - 5:33:57 AM - John Back To Top

I was wondering if I can run 2 node setup with sios software using one SQL 2016 Standard license. According to microsoft you can license 2 device with one SQL standard license if it is a failover/passive cluster.

What do you recommend regarding the SQL licensing?

 

Thanks


Tuesday, January 13, 2015 - 11:40:16 AM - Greg Robidoux Back To Top

Hi Roman,

Yes you would need to setup the Windows cluster and install SQL Server as a cluster node on both nodes.  This way the failover can occur using Windows clustering. The DataKeeper software will keep the data synchronized using non-shared storage. 

SIOS has a few different products see this page for more info: http://us.sios.com/what-we-do/windows/

-Greg


Tuesday, January 13, 2015 - 11:16:16 AM - Roman Kapilovich Back To Top

Greg,

SIOS also mentioned that SQL installation cannot be Standalone but Failover Cluster.

Do you know what the reasons behind that? Did you try using Standalone version in your testing?

We are utilizing SQL 2014 32 bit version from legacy reasons.

Thank you


Tuesday, January 13, 2015 - 10:56:13 AM - Greg Robidoux Back To Top

Hi Roman,

what you listed below will work.  The DataKeeper software will keep the copies of the data synchronized and therefore you don't need to use AlwaysOn Availability Groups.  I would check out the SIOS website for more information at us.sios.com.

Also, why are using the 32bit version instead of 64bit?

Thanks
Greg


Tuesday, January 13, 2015 - 9:23:31 AM - Roman Kapilovich Back To Top

Greg,

We are trying to implement 2 node cluster solution: WSFC 2012 Standard R2, SQL 2014 Standard, 32 bit, DataKeeper.

Can you please confirm that the configuration is feasible and does not require Alwayson feature (the feature is not available in SQL 2014 Standard, 32 bit).

Thank you


Wednesday, May 14, 2014 - 5:16:45 PM - Tony Tomarchio Back To Top

Hi Alex,

When you create a "Job" (i.e. a mirror) using DataKeeper Cluster Edition, it will ask you if you wish to register the newly created mirror with WSFC as a Cluster Disk.  You will then have a "DataKeeper Volume" resource sitting in the cluster's Available Storage pool, which can be selected as your desired cluster disk during the installation of the SQL FCI (Failover Cluster Instance).

The following blog articles show a Step-by-step walkthrough the configuration process to enable a shared-nothing cluster using WSFC and SIOS's DataKeeper Cluster Edition.  If you take a look at the 2nd article, you'll see a screenshot of the DK Volume sitting in the clusters availablt storage pool, and later a screenshot of selecting the DK Volume during the "Cluster Disk Selection" screen of the SQL installer

http://clusteringformeremortals.com/2012/12/31/windows-server-2012-clustering-step-by-step/

http://clusteringformeremortals.com/2013/01/05/clustering-sql-server-2012-on-windows-server-2012-step-by-step/

 


Wednesday, May 14, 2014 - 4:38:37 PM - Greg Robidoux Back To Top

Hi Daniel,

It is possible to use SQL Server replication to Windows Azure, but the SIOS solution provides a high availability solution with block level replication of the entire SQL Server instance including jobs, logins, etc. in addition to all of your databases.


Wednesday, May 14, 2014 - 4:24:26 PM - Alex Aguilar Back To Top

You mention that implementation of WSFC for SQL Server is setup, configured and supported in the usual way.  Can you elaborate please because the setup of a clustered instance will not let you continue if it does not detect clustered disks.  Do you perform a standalone install of SQL Server and then use the replication utility or do you perform a clustered instance setup of SQL Server?


Wednesday, May 14, 2014 - 7:52:36 AM - Daniel Back To Top

Instead of wasting money with this SIOS my recommendation would be to achieve the same functionality by using SQL Server Replication to Windows Azure.

 


Learn more about SQL Server tools