Enhanced AlwaysOn High Availability with SQL Server 2014

Puran Kandpal | Published: November 13, 2018 | SQL |

SQL Server 2014 has been available in a variety of versions and functionalities since the beginning of April, bringing many new features and improvements to cloud integration and scalability in addition to new in-memory technology . In addition, SQL Server 2014 also provides many enhancements to AlwaysOn Failover Cluster Instances (FCI) and AlwaysOn High Availability Groups, Failover cluster instances represent the latest generation of failover cluster services that Microsoft SQL Server has supported from the very beginning.

In contrast, SQL Server High Availability Groups were introduced with SQL Server 2012 and provide database administrators database mirroring and log shipping to ensure database-level availability. Both features play a key role in high-availability and disaster-recovery strategies, and have been significantly improved with SQL Server 2014.

Failover Clustering with Cluster Shared Volumes

Prior to SQL Server 2014, only one failover cluster instance could access a volume. As a result, other FCIs were unable to read from or write to the same volume. In SQL Server 2014, the FCI services now support Cluster Shared Volumes (CSV).

CSV is a feature of Windows Server Failover Clustering (WSFC), already introduced in Windows Server 2008 R2, which now also uses SQL Server 2014. With CSV, all nodes in a failover cluster have simultaneous access to the shared disks, and each node can access the files independently of the others.

The CSV structure not only simplifies the storage requirements for a failover cluster, but also provides an additional layer for error tolerance. If a SQL Server FCI loses the connection, the instance can still access the volume via another connection. In addition, the drive does not need to be removed from live operation during a failover process and reinserted. In contrast, this would be necessary for a conventional failover cluster.

Secondary Replication for SQL Server High Availability Groups

A high availability group consists of a primary SQL Server instance and one or more secondary ones. The primary instance, also known as primary replication, hosts the high availability group databases to be replicated. When the high availability group is active, SQL Server 2014 copies the databases from the primary replica to the secondary replica and keeps them in sync on resale. Unlike a failover cluster, the secondary replication databases are called hot copies . As long as the instance is available they are always online and fully readable.

As a result, read and backup operations can be offloaded to the secondary replicas, which of course reduces the workload on the primary databases. So, in addition to providing an efficient mechanism to keep databases highly available, high-availability groups in SQL Server 2014 are also an effective way to distribute read access to different data centers. This ultimately makes it possible to move process-intensive analyzes to nearby data centers.

In comparison to SQL Server 2012, up to eight secondary replicas can be used with SQL Server 2014, with SQL Server 2012 the upper limit was four secondary replicas. This doubles the capacity for geographically distributed reporting while providing more efficient scalability of workloads.

Secondary Replication becomes even more reliable with SQL Server 2014

Of course, the read capabilities of the secondary instances are based on the functionality of the high availability groups. In SQL Server 2012, the secondary replicas were no longer available when the network or primary replica failed or the WSFC service lost its quorum. Of course this is a little bizarre, because when such a failure happens, you need the secondary replicas most urgently.

At this point, another innovation in SQL Server 2014 comes into play. Because even if the secondary replicas are disconnected from the primary replication, the secondary ones now remain online and allow read operations. This improvement is particularly attractive for large-scale solutions that are geographically dispersed. In such scenarios, network disruptions are more likely and secondary instances are more important.

High-Availability Solutions for Azure SQL Database

As of SQL Server 2014, you can also set up a Microsoft Azure Virtual Machine (VM) running SQL Server as a secondary instance in your high availability group configuration . The process of setting up this instance has been integrated into the HA Group Wizard for easier configuration.

An Azure secondary replica can support two scenarios. On the one hand, it can serve as an asynchronous replica for the local and primary databases. If the primary instance fails, you can manually failover to the Azure-based secondary replica. On the other hand, it is also possible to configure the secondary Azure instance to support a primary Azure-hosted instance. In this case, the Azure instance of SQL Server 2014 can act as a synchronous replica, allowing automatic failover.

However, when using an Azure VM as a secondary replication for a local primary instance, you should be aware of Azure’s limitations and costs. This is especially true for large databases. In addition, connecting Azure to your network requires a Virtual Private Network ( VPN) connection , which may require the purchase of a VPN appliance.

Always On and High Availability: SQL Server 2014 is more Reliable than Predecessor

With SQL Server 2014, Microsoft has provided its database management system with high availability and AlwaysOn with a number of important enhancements, such as support for Cluster Shared Volumes or more secondary replication. SQL Server 2014 is even more reliable than any previous version. If high availability is at the top of your list, the improvements to AlwaysOn functionality in SQL Server 2014 could be exactly what you were looking for.

Conclusion

The blog covers various features of Always On and high availability in SQL Server 2014 has become more reliable than Predecessor versions.