SQL Server Failover Clustering for High Availability
I have already written about Database Mirroring and Log shipping in my previous posts. Both Database Mirroring and Failover Clusters provide the following benefits:
- Automatic detection – Using a Witness Box in DB Mirroring or the Cluster Service in case of Failover Clustering
- Automatic, fast failover – DB Mirroring is the fastest to failover. However both provide automatic failover options.
- Manual failover – Manual failover options for Server Maintenance Tasks, Service Pack, Patching etc.
- Transparent client redirect
- Zero work loss – 2 Copies of Database in Mirroring Vs. Shared Storage in Clustering.
There are two different types of High Availability and Failover options using WIndows Clusters from SQL Server 2012 Onward.
- SQL Server Always On using Failover Clusters and “Shared storage”
- SQL Server Always On using Availability Groups and Windows Cluster Service without a mandatory requirement for a “Shared Storage“
In this post, I would be only talking about the first option, which is SQL Server High Availability using “Failover Clustering”
With Always On Failover Clustering, you get the following benefits:
- Hot Standby – Automatic failover
- Built on Microsoft Windows Server Clusters (MSCS)
- No Application changes: Multiple nodes provide availability, transparent to client
- Maximum of 2, 4,8,16 or more nodes depending on OS and SQL edition.
- Automatic detection and failover
- If you are using a physical server, it would require a certified hardware, for more information see Windows Catalog: Clustered
- Supports many scenarios: Multiple Active Instances, N+1, N+I
Additional Licensing implications may be applicable. This technology is also available in SQL Server Standard Edition, restricted up-to 2 nodes.
The requirement for a shared storage, makes storage as the single point of failure.
The Hot Standby Instance provides you the following:
- All SQL Instances point to the same storage location. So, Zero work loss, zero impact on throughput
- Instance Failover – entire instance works as a unit. Unlike Database Mirroring, where Failover happens at the Database Level.
- Single copy of instance databases. Redundancy at the storage level needs to be addressed.
- Available since SQL Server 7.0 till date.
- Setup is performed at install time.
- Clients connect to ‘virtual’ server-name / IP. Application pointers or DNS routing is not required.
The following SQL Server Services are cluster aware:
- SQL Server Database Services.
- SQL Server Analysis Services.
- SQL Server Agent Services.
Other Services, like Reporting Services (SSRS) or Integration Services (SSIS) are not cluster aware.
In the next post of this series, I will run you through the Failover Cluster checklist prior to installation.