SQL Server Failover Clustering for High Availability

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:

1. SQL Server Database Services.
2. SQL Server Analysis Services.
3. 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.