SQL Server clustering is an adventure and the devil lies in the details. I am designing a SQL Server High Availability solution and SQL Server Failover clusters are on top of my agenda right now.
Many DBAs (especially newbies) assume clustering to be a one-stop HA solution protecting the server, the instance, the database and the data !!!
Well, that’s not true. There is a lot of misunderstanding relating to what SQL Server Failover clusters can do and what it cannot. So here is a sneak peak on SQL Server clustering limitations:
What can SQL Server Failover Clusters do?
- Increases the availability of physical server hardware – In case the power goes down on the box on which the SQL instance is running, a failover happens to another node in the cluster.
- Increases the availability of Operating System – In case the OS gets unresponsive; for example after you apply a hotfix or a patch, the OS gets unstable, failover is initiated
- Increases the availability of SQL Server Service itself
What can SQL Server Failover Clusters cannot do?
- Bring the downtime to zero. DBAs need to be aware of the fact that failover could take a few seconds to a few minutes depending on the amount of work that needs to be re-done and undone on the passive node.
- Protect the Data. DBAs should not be surprised here. SQL Server Failover clusters do not protect data as the disks are shared; so the nodes are basically sharing the disk array. In case a disk gets faulty, it’s for all the nodes. DBAs need to implement proper RAID configuration to protect the data.
- Load Balancing. SQL Server Failover Clusters is not a load balancing solution.
- Also note that just because you have implemented SQL Server Failover Clusters, the front-end applications do not automatically become cluster aware. In many cases the logic to re-try connecting to the new node needs to be implemented.