Overview of Always On SQL Server Availability Groups

Rating: 4
Views: 1248
by Arogyalokesh
Last modified: February 19th 2019

Always On SQL Server

Every organization’s success and service reputation is built on ensuring that its data is always accessible and protected. In the IT world, this means delivering a product that achieves the highest level of availability and disaster recovery while simultaneously minimizing data loss and downtime. In SQL SERVER 2012, Microsoft introduced a new capability known as AlwaysOn, which provided customers with superior availability and disaster-recovery alternatives. The AlwaysOn brand included two features for achieving high availability and disaster recovery: AlwaysOn Availability Groups and AlwaysOn Failover Cluster Instances (FCIs). In the next section, we shall discuss about AlwaysOn enhancements in SQL Server 2014.

Learn how to use SQL Server, from beginner basics to advanced techniques, with online video tutorials taught by industry experts. Enroll for Free SQL Server Training Demo!

Always On Availability Groups enhancements

AlwaysOn is a new feature of SQL Server 2012 for high-availability and disaster recovery. The AlwaysOn feature is an extension to the principles of SQL Server Database Mirroring. However, it includes enhancements that go beyond the existing high-availability solutions that Database Mirroring and Database Replication offer.

Always On Availability Groups are a fundamental component of the availability story for SQL Server, and provide a robust disaster recovery solution as well. Availability Groups were first introduced in SQL Server 2012, replacing the prior Database Mirroring technologies. They enable a DBA to configure two SQL instances to host replicas of a set of databases, which can be kept exactly in sync, giving zero data loss guarantees, or near exactly in sync (async replication, which is optimal for geographically remote replicas). This technology has become the standard which the majority of critical production SQL Server instances leverage.

AlwaysOn Availability Groups gave organizations the ability to automatically or manually fail over a group of databases as a single unit with support for up to four secondary replicas. The solution was integrated with SQL Server Management Studio and provided high availability and disaster recovery with zero data loss. It could be deployed on local storage or shared storage, and it supported both synchronous and asynchronous data movement. The application failover was very fast and supported automatic page repair, and the secondary replicas could be used to offload reporting and a number of maintenance tasks such as backups. Figure 3.2 depicts an AlwaysOn Availability Groups deployment strategy that is based on SQL Server 2012. The illustration shows one primary replica and three secondary replicas.

AlwaysOn Availability Groups was a great success, yet the SQL Server product group wanted to expand its capabilities and provide increased availability, greater efficiency, and easier deployment and management. Similarly, the product group wanted to provide tighter integration with Windows Azure for hybrid-cloud opportunities and innovations. In the next section, let us explore the results of the product group’s efforts.

FIGURE 3.2 : An AlwaysOn Availability Groups deployment running SQL Server 2012 with four replicas spanning two data centers for high availability and disaster recovery.

Related Page: Master Data Services SQL Server 2012 In SSIS

Additional secondary replicas

The AlwaysOn Availability Groups active secondary capabilities include support for read-only access to one or more secondary replicas.

To provide additional offloading capabilities, the maximum number of secondary replicas was increased from four to eight in SQL Server 2014. Some readers may ask why more replicas were needed. Additional replicas can be distributed around the world, which positively affects high availability and disaster-recovery efforts for any organization. New wizards have also been included that can be used to provide integrated hybrid-cloud scenarios in which replicas can be easily deployed in Windows Azure. Likewise, additional replicas can also be used to offload read workloads such as scale-out operations, reporting backups, and maintenance tasks. It is worth keeping in mind that eight secondaries, two of which can be configured as synchronous secondaries, can be used for high availability.

Figure 3.3 illustrates an AlwaysOn Availability Groups deployment with SQL Server 2014. Here, the deployment uses a primary replica and seven secondary replicas. The secondary replicas provide maximum high availability and disaster-recovery protection because they reside in the primary data center, the secondary data center, and in the Windows Azure cloud.

FIGURE 3.3 : An AlwaysOn Availability Groups deployment running SQL Server 2014 with a total of eight replicas. Two of these replicas reside in Windows Azure to provide disaster-recovery capabilities in the cloud

Related Page: SQL Server The Data Flow Sources In SSIS 2014

Increased availability

AlwaysOn is a feature in SQL Server 2012 and 2014 that provides flexible and cost-efficient high availability and disaster recovery. It provides automatic recovery from failures, increasing the availability of critical applications and potentially reducing the risk for data loss, while avoiding downtime.

In SQL Server 2012, read workloads on a secondary replica failed if a network failure occurred between the primary replica and the secondary replicas. This type of situation arose more often in geo-distributed environments or hybrid deployments because of their increased susceptibility to network equipment failures, network upgrades, or ISP failures. With SQL Server 2014, the availability of readable secondary replicas has been increased and read workloads are no longer affected during network failures, if the primary is down or if the cluster quorum is lost. To make use of this enhancement, a direct connection to a readable secondary is required. In addition, as indicated earlier, whether initiated manually or automatically, a failover of multiple databases can occur concurrently, leading to higher availability. Figure 3.4 details the benefits of increased availability in SQL Server 2014. In the figure, two secondary replicas are used for reporting and backups, even though the network connection to the primary is no longer available.

FIGURE 3.4 : Increased availability and functionality of secondary replicas with SQL Server 2014.

Explore SQL Server Sample Resumes! Download & Edit, Get Noticed by Top Employers!Download Now!

Add Azure Replica wizard

Usage of  Add Replica to Availability Group Wizard helps you to add a new secondary replica to an existing AlwaysOn availability group.

Disaster-recovery and business-continuity planning are two important requirements for many organizations, both from a customer-service and a regulatory perspective. Data loss and system disasters can negatively impact an organization or even permanently shut it down. Hence, there is a need for disaster-recovery sites. Regrettably, many organizations still do not have a disaster-recovery site because of the its high cost and maintenance challenges. To address these challenges, SQL Server 2012 supported secondary replicas on Windows Azure Virtual Machines. This meant that organizations could manage disaster recovery by building hybrid platforms using Microsoft’s cloud platform, known as Windows Azure. Organizations indicated that this opportunity addressed their disaster-recovery requirements, but it also created another issues. The configuration process for database administrators was manual and cumbersome at times. The product group responded to the feedback and has introduced the Add Azure Replica wizard in SQL Server 2014. The wizard automates and simplifies the deployment of on-premises replicas to Windows Azure. When configuring the replicas, a database administrator can choose any Windows Azure data center around the world; however, when a location is considered primarily in terms of latency and politics, the best location for the replicas is near the data center.

AlwaysOn Failover Cluster Instances enhancements

Now that we’ve explored the development efforts in engineering, the new capabilities of AlwaysOn Availability Groups for high availability and disaster recovery, it’s time to move on to another important matter: enhancements to AlwaysOn Failover Cluster Instances (FCIs).

SQL Server Failover Clustering, includes support for both local and multisite failover configurations, as a part of the SQL Server 2012 AlwaysOn implementation suite, designed to provide high availability and disaster recovery for SQL Server. The multisite failover clustering technology has been enhanced significantly in SQL Server 2012.

Support for Cluster Shared Volumes

An exciting new feature in SQL Server 2014 is the support for the deployment of a Failover Cluster Instance (FCI) with Cluster Shared Volumes (CSV).

In conjunction with Windows Server 2012 and later releases, SQL Server 2014 now supports Cluster Shared Volumes (CSVs) as clustered shared disks for AlwaysOn Failover Cluster Instances. Why is this important? CSVs reduce the number of logical unit numbers (LUNs), or disks, required for FCIs, which increases the number of FCIs that can be hosted on a single Windows Server Failover Cluster (WSFC). Previously, the maximum number of instances supported was 24 because a LUN was the unit of failover. CSVs remove this limitation. CSVs also improve storage area network (SAN) utilization and disk space and increase I/O resiliency and failover resiliency, because disks no longer need to be unmounted and mounted.

The following sections outline how to add a CSV on a Windows Server Failover Cluster and how to use it during a SQL Server installation.

Add a disk to a CSV on a Windows Server Failover Cluster

In Windows Server 2012 R2, the CSV feature is enabled by default when the failover cluster feature is installed. To add a disk to a CSV, the disk must be added to the Available Storage group of the cluster before it is added to the CSV on the cluster. You can use Failover Cluster Manager or the Failover Clustering cmdlets in Windows PowerShell to perform these procedures.

Related Page: System Center 2012 R2 Configuration Manager Toolkit

To add a disk to the Available Storage group, follow these steps:


1. In the console tree in Failover Cluster Manager, expand the name of the cluster, and then expand Storage.
2. Right-click Disks, and then click Add Disk. A list appears showing the disks that can be added for use in a failover cluster.
3. Select the disk or disks you want to add, and then click OK.
In the next step, add the disk or disks in the Availability Storage group to the CSV:

1. In the console tree in Failover Cluster Manager, expand the name of the cluster, expand Storage, and then click Disks.
2. Select one or more disks that are assigned to Available Storage, right-click the selection, and then click Add To Cluster Shared Volumes.

The disks are now assigned to the Cluster Shared Volume group in the cluster. The disks are exposed to each cluster node as numbered volumes (mount points) under the %SystemDisk%ClusterStorage folder. The volumes appear in the CSVFS file system.

Figure 3.5 shows two 100-GB CSVs (named Cluster Disk 2-CSV1 and Cluster Disk 3-CSV2) that have been allocated to a Windows Server Failover Cluster named AlwaysOn-FC01.

FIGURE 3.5 : Adding available storage to Cluster Shared Volumes in Failover Cluster Manager.

The next step is installing the SQL Server failover cluster instance and leveraging the CSVs. Launch SQL Server setup and choose New SQL Server Failover Cluster Installation to invoke the Install A SQL Server Failover Cluster wizard. When you get to the Cluster Disk Selection page, select the Cluster Shared Volume to use as the shared cluster disk resources for your SQL Server failover cluster. As illustrated in figure 3.6, Cluster Disk 2-CSV1, which was created in the preceding procedure, is used as the CSV on which FCI is installed.

FIGURE 3-6 Using a Cluster Shared Volume to install a SQL Server failover cluster instance.

New dynamic management views (DMVs)

Dynamic management views and functions return server state information that can be used to monitor the health of a server instance, diagnose problems, and tune performance.

sys.dm_hadr_cluster Returns information pertaining to the Windows Server Failover Cluster (WSFC) cluster name and quorum, provided that the cluster has a quorum. It can also be used on AlwaysOn Availability Groups. If the WSFC node has no quorum, no row is returned.

sys.dm_hadr_cluster_members Determines which nodes are currently running on the WSFC cluster and how many failures the WSFC can sustain before losing a quorum. This is a very important DMV. Use it to ensure that the majority-node quorum is set up correctly to guard against the unexpected failure. It is queried from a server instance that is that is enabled for AlwaysOn Availability Groups that resides on a node with quorum, it reflects the state of the down node as “NODE_DOWN”.

sys.dm_hadr_cluster_networks Returns a row for every WSFC cluster member that is participating in an availability group’s subnet configuration. Use this DMV to validate the network’s virtual IP that is configured for each availability replica.

sys.dm_io_cluster_valid_path_names A new DMV is used to return information on all valid shared disks, including CSVs.

Frequently Asked SQL Server Interview Questions & Answers

List of Related Microsoft Certification Courses:

 SSIS  Power BI
 SSRS  SharePoint
 SCCM  BizTalk Server
 Team Foundation Server  BizTalk Server Administrator