SQL 2014: Investigating Microsoft's latest database release
A comprehensive collection of articles, videos and more, hand-picked by our editors
SQL Server 2014 has improved its AlwaysOn Failover Cluster Instances and AlwaysOn Availability Groups products. Failover Cluster Instances represents the latest generation of the failover clustering services that have been supported by SQL Server almost since its inception. SQL Server Availability Groups was introduced in SQL Server 2012 and incorporates the best of database mirroring and log shipping to provide database-level availability....
Both features can play significant roles in an organization's high availability and disaster recovery strategies, and both are better in SQL Server 2014.
Clustered Shared Volumes for failover clustering
Prior to SQL Server 2014, only one failover cluster instance (FCI) could access a volume at any given time. As a result, no other FCIs could read from or write to that volume. In SQL Server 2014, the FCI services now support Clustered Shared Volumes (CSV).
Introduced in Windows Server 2008 R2, CSV is a feature of the Windows Server Failover Clustering (WSFC) service, which SQL Server leverages to support failover clustering. With CSV, all nodes in a failover cluster have concurrent access to the shared disks, while allowing each node to access files independently.
Not only does the CSV structure simplify the storage requirements for a failover cluster, but it also builds in an extra layer of fault tolerance. If an SQL Server FCI loses connectivity, it can continue to access the volume via another connection. In addition, the volume doesn't need to be unmounted and then remounted during failover, as is the case in a traditional failover cluster.
Doubling secondaries for SQL Server Availability Groups
An Availability Groups setup is made up of one primary SQL Server instance and one or more secondary instances. The primary instance, or primary replica, hosts the databases slated to be replicated as part of the Availability Groups operation. When Availability Groups is enabled, SQL Server copies the databases from the primary replica to the secondary replicas and then keeps them in sync going forward. However, unlike a failover cluster, the secondary databases are hot copies; that is, they're in a continuous online state and fully readable as long as the instance is available.
Because of this accessibility, you can offload your read and backup operations to secondary replicas, thus reducing loads on the primary databases. Consequently, not only does SQL Server Availability Groups provide an efficient mechanism for implementing a highly available solution, but it also gives you an effective way to distribute read workloads across geographically dispersed data centers in order to better support processing-intense analysis and reporting.
What SQL Server 2014 brings to the equation is the ability to implement eight secondary replicas, rather than four, as was the case in SQL Server 2012, thus doubling the capacity for distributed reporting and other read operations as well as providing a mechanism for more effectively scaling out workloads.
More reliable secondaries for SQL Server Availability Groups
Clearly, the read capabilities of the secondary instances represent a significant feature of the Availability Groups functionality. Yet in SQL Server 2012, if the network fails or the primary replica goes offline, or the WSFC service loses its quorum, the secondary replicas are no longer available. This might seem surprising given that, if such a failure occurs, this is often when you need a secondary replica the most.
But SQL Server 2014 comes to the rescue. Even if the secondary replicas are cut off from the primary replica, they can still remain online and continue to support read operations. This improvement could prove especially important to large-scale solutions widely distributed across geographical regions, where network disruptions are more common and secondary instances more important to the operation.
Azure support for Availability Groups secondaries
A Windows Azure virtual machine (VM) running SQL Server can now be configured as a secondary instance in your Availability Groups configuration. The process of setting up that instance has been integrated into the Availability Groups configuration wizard.
An Azure secondary replica supports two scenarios. The first is as an asynchronous replica for your on-premises primary databases. If the primary instance fails, you can manually fail over to the Azure secondary. You can also set up an Azure secondary replica to support a primary instance also hosted on Azure. In this case, the Azure secondary can support synchronous replication and automatic failover.
Keep in mind, however, that when using an Azure VM as a secondary for an on-premises primary instance, Azure limitations and costs should be taken into account, especially for large databases. In addition, the connection between your network and Azure will require a virtual private network (VPN), which could mean purchasing a VPN appliance.
AlwaysOn availability in SQL Server 2014
Microsoft provides a number of important improvements to the AlwaysOn features in SQL Server 2014. Failover clusters now support CSV, and SQL Server Availability Groups provides a greater number of secondary instances that are more reliable than ever. In addition, you can use Windows Azure VMs as one or more of those instances. You'll also find that the AlwaysOn features provide enhanced diagnostics, with information that is more complete and concise, making it easier to find and correlate data. If high availability is at the top of your list of concerns when assessing a database management system, then the improvements to the AlwaysOn features in SQL Server 2014 could prove just what you're looking for.
About the author:
Robert Sheldon is a technical consultant and the author of numerous books, articles and training material related to Microsoft Windows, various relational database management systems and business intelligence design and implementation.