By: Greg Robidoux | Last Updated: 2014-05-14 | Comments (12) | 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 |
|
SQL Server 2008 - Standard |
2 nodes |
|
SQL Server 2008 R2 - Enterprise |
up to 8 nodes on Windows 2003 |
|
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.
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.
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.
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.
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
- Take some time to learn about SIOS DataKeeper Cluster Edition
- Read these whitepapers
- Learn step by step how to setup a cluster for SQL Server
MSSQLTips.com Product Editorial sponsored by SIOS Technology Corp., makers of SIOS DataKeeper Cluster Edition.
Last Updated: 2014-05-14
About the author
Greg Robidoux is the President of Edgewood Solutions and a co-founder of MSSQLTips.com.
View all my tips
