Stairs to AlwaysOn Level 1: What is SQL Server AlwaysOn?
Perry Whitel, 2016/02/24 (first published: 2014/09/24)
This article is part of the stair series: Access to AlwaysOn stairs
AlwaysOn is a complex set of technologies that are often misunderstood. In this ladder, you'll learn about AlwaysOn technologies, how they fit into the high availability stack, and how to get the most out of them.
Welcome to the first level of the SQL Server AlwaysOn series. In this level of article, we will find technology "AlwaysOn", "Failover Cluster Instance" (FCI), and "Windows Server Failover Cluster". We will discuss each of these issues in detail and summarize their location in the high availability stack. This will provide us with a solid foundation, which is essential to lift the level of the staircase. A higher level of stairways will institute the infrastructure required and the different storage requirements and options that are used with AlwaysOn availability groups and FCIS.
After each stair level, you will learn more about how AlwaysOn structures are built. Although as a DBA, you may not have any direct interaction with the core infrastructure projects under AlwaysOn and Fcis, but it helps to fully understand how all the technologies are integrated. The final staircase will result in an AlwaysOn configuration of functionality.
We'll look at some basics first, including a brief description of the three technologies already mentioned.
Many initials and abbreviations are used in the AlwaysOn description. We have included a glossary of commonly used terms at the end of this article.
If there is no further discussion, let's take a closer look at each of these technologies.
Windows Server failover Cluster
Windows Server failover Clustering (WSFC) is a core high availability (HA) product that is located under all Microsoft High availability applications. WSFC is part of the Windows Server operating system software suite. Before you create a failover cluster instance of SQL Server, AlwaysOn high availability groups, or even a Microsoft Exchange mail server cluster, you need to deploy and configure WSFC.
Windows Server failover clustering provides the ability to combine multiple computer nodes (physical and virtual) to provide services for high availability. Applications are server software, such as SQL Server or exchange, and we want to be able to run on any node. Applications can obtain high availability by providing a virtual access point to the client, including a unique IP address and a unique computer name or "virtual Network name". This address and virtual name become resources in the application group and are passed between participating nodes, such as tokens. A critical hardware failure on the active computer node will cause the group service to run on that node to be lost. The Cluster service will automatically attempt to restart the group on the current node or partner node based on the type of failure (hardware or software).
At a high level, client access point details are transferred to the failover partner node with any disk and service resources. A failover of a clustered instance causes a client connection to disconnect, and the client may reconnect when the service is available on another node. Common failures are usually one of them, but any failure of the application can cause the service to move to another node:
Public NIC or network failure
Wsfcs, a clustered application is installed into a separate group or "application" that contains a set of resources, such as a disk service IP address, and so on. The group and its resources are made by a single node at any one time, and resources are not available from any other partner node unless a scheduled switch or a node fails over.
A typical view of a Windows Server failover cluster is as follows. Cluster nodes are network-connected, and domain controllers and DNS services work with WSFC, allowing clients to connect to virtual IP or virtual network names, regardless of which node the service is actually running on.
In order to deploy an FCI, the computer node must use the shared storage that is presented to each node, typically from a SAN. To deploy a typical AlwaysOn group, nodes use their own local storage, which is not shared with other cluster partners.
Although cluster nodes may have completely different hardware, it is often best to maintain hardware consistency across nodes to avoid the need for weaker nodes to handle loads that exceed their capabilities in any case. However, the nodes must use the same OS patch level and network configuration, and this becomes clear before you deploy the Windows Server failover cluster to validate your configuration. The maximum number of cluster nodes is different between the Windows Server version (8 nodes in Windows 2003, 16 nodes in Windows 2008, and 2012 nodes of Windows 64).
Deploying a robust Windows Server failover cluster requires careful design, supported hardware, and an appropriate version of the Windows Server operating system. Geographically dispersed clusters (clusters spanning multiple WANs) further increase the amount of design and planning required and significantly increase costs.
It is important to know that WSFC only provides failover partner functionality. Applications do not load-balance or scale across nodes. Each service runs on only one node.
Typically, in a large multi-node cluster, you install a clustered application on a subset of the Windows Server failover cluster nodes. Incorrectly installing an application on all nodes can cause some undesirable failovers, as we will see later, which also violates the AlwaysOn group throttling policy, which ensures that all AlwaysOn instances are homed on separate nodes in the cluster.
WSFC requires some form of mediation to control the ownership of cluster resources. This mediation is provided in the form of cluster quorum. Since the Windows 2003 SP1, this quorum has received a majority of the votes required to maintain quorum in the form of a node voting system. You can also use additional quorum resources in the form of disks for localized clusters or remote file shares for multi-site clusters. Starting with Windows Server 2012, Quorums uses dynamic node weight configuration to dynamically dynamically cluster polls during planned outages to prevent unnecessary failovers. We will discuss quorums in more detail at a later level.
Failover Cluster instances
The failover cluster instance of SQL Server has been a popular high availability technology in SQL Server products. To mitigate any node hardware failure and any potential software failure, there is a highly available SQL Server instance in the cluster. The only weak link here is storage, and the storage subsystem becomes a single point of failure.
A failover cluster instance is an instance of SQL Server, default, or named that has been installed on WSFC as a clustered application. Clustered applications typically have the following resources:
Shared Disk (age)
SQL Server Service
SQL Server Agent Service
A separate instance shares the same basic requirements, except for isolated instances, where IP addresses and networkname are from the computer node itself, while disk storage is provided by the computer's local disk resources.
In the above figure, we see a typical 2-node cluster view with an FCI. The clustered instance of SQL Server will take advantage of any shared storage that has been rendered to the WSFC node. Typically this storage is in the form of LUNs provided by the SAN. An FCI for a SQL Server is deployed in a 2-step process that will be overwritten in a later staircase. Here is a basic overview of the two-step process for deploying a failover cluster instance of SQL Server:
Start the new SQL Server Failover Cluster Installation Wizard on the first computer node that will participate in an FCI. Once completed and completed successfully, you will be able to enter the second phase.
In the new SQL Server FCI that you want to participate in, start the Add node to SQL Server Failover Cluster Wizard on any computer node in WSFC.
Note: Although the Standard Edition restricts FCI to 2 nodes, it does not specify how many nodes have a member of the Windows cluster (you may have the largest operating system maximum). The limit is performed at the SQL Server Setup level.
An FCI is a bit like the handover process of a running track relay; the computer node has a clustered SQL Server application and its resources, and then provides access to the client to the SQL Server service (holding the baton). When the active computer node (Baton drop) fails, the partner node intervenes and acquires ownership of the cluster application and its resources (on the baton).
AlwaysOn Availability Groups
Over the years, failover clustering has been the primary method of providing high availability SQL Server. When one node fails, the other node takes over to provide the SQL Server service to the client. AlwaysOn integrates Windows Server failover clustering technology to provide a more resilient, high-availability platform.
However, the cluster works at the instance level, and AlwaysOn is configured at the database level. AlwaysOn availability groups is a new technology introduced in SQL Server 2012 that replicates predefined database groups to a set of read-only partner instances (or replicas) because they are known in AlwaysOn. Multiple nodes each host is a synchronous copy of an AlwaysOn database, and access is best provided by the listener's configuration (this will be described in more detail later).
AlwaysOn availability groups require one or more secondary replicas to host a copy of a highly available database. These secondary databases can be either readable or not readable. They can also be updated asynchronously or synchronously. Asynchronous replication supports only manually enforced failovers, while synchronous replicas support automatic or manual failovers.
Secondary read replicas may be configured to respond to read-only queries, and you can reduce the stress on the primary database for backup \ Maintenance operations on secondary servers. This primary to two level relationship is also reversible to ensure true high availability. When any read-only partner fails in the system, the appropriately configured objects may assume the primary role.
AlwaysOn relies on the WSFC core functionality to achieve the high availability offered by AO, but does not require any shared resources related to FCI.
Shared IP Address
shared Network Name
Shared SQL Server and SQL Server Agent resources
There is one exception to this shared resource rule. When you create an AlwaysOn group listener, an IP address and Network Name resource is created, shared by the AO group copy.
As we have discovered, the weak links in the failover cluster instance chain are shared storage. There are many ways to implement redundancy, but usually it costs a lot and is often difficult to set up and maintain. Of course, as mentioned earlier, failover cluster instances only mitigate server hardware. It does not provide one or more secondary databases. Before SQL Server 2012, we had database mirroring in the version of SQL Server, but these only provided scope for a single, unreadable secondary database.
AlwaysOn still uses the familiar SQL Server endpoint for instance communication. When you use the Available Groups Deployment Wizard, the endpoints are automatically configured. Wizard-driven deployment provides the simplest deployment path, while manual deployment requires a lot of manual interaction. Nonetheless, the basic AlwaysOn group configuration is still very easy to deploy and configure, and it provides an HA level that was previously unusable without the need for complex functional integration levels.
You can also create a highly available listener service that you will use to receive incoming connections to the available groups. A listener consists of a unique IP address and a unique virtual network name. So far, this is one of the most significant changes to make the database in the group highly available.
When you create an AlwaysOn availability group, you create a cluster role in a Windows Server failover cluster and include a resource. During an AlwaysOn group failover, the resource fails between the partner nodes and identifies the primary replica of the AlwaysOn group.
When configured, the listener is created as a resource and resides in the failover cluster application of the AlwaysOn availability Group. The resources are:
Virtual IP Address
The name of the virtual network
The listener accepts incoming connections using TCP ports and connects to the primary replica by default. When a read-only route is configured, the connection to the listener that specifies the read-only intent connection is routed to the secondary partner instead of the primary replica. This is another way to reduce the load on the primary replica.
During the failover process for an AlwaysOn group, the clustered application and its resources fail between the nodes in the cluster. The node location of the clustered application tracks the primary replica and its underlying nodes, and moves through the cluster as needed. In the case where the primary replica is a clustered instance of SQL Server, the listener is owned by the active node of the FCI \ Replica.
This is the first level of the staircase, which provides a quick introduction to 3 core technologies to make our instance of SQL Server and its objects highly available. In our high availability stack, we use WSFC as the base level, which is the primary requirement for installing an FCI or AlwaysOn availability group. Next is the FCI, which is located above the WSFC and relies on the cluster to service and secure instances of SQL Server. Finally, we have AlwaysOn group, which is located on a separate instance of SQL Server and \ or SQL Server's failover cluster instance.
At level 2nd, we will look at the available storage types and their typical usage in SQL Server high availability. This will help you understand the future stair levels in this series.
List of terms
AlwaysOn Availability Groups
Failover cluster instance for SQL Server
Transmission Control Protocol/Internet Protocol. Network protocol used by Microsoft Client Network
Network operating system
Windows Server failover Cluster
Domain Name System
Dynamic Host Configuration protocol that automatically assigns IP addresses to network computers
A 32-bit (IPV4), the only address assigned to the computer object
Active Directory, directory service. Microsoft technology for object Management in a Windows domain
Sun Protection factor
Single point of failure
Small computer System interface
Internet Small Computer System interface
A term used in SQL Server AlwaysOn availability groups to reference an instance of SQL Server that is part of a specific AlwaysOn group
Modify Translation Results
Try a manual translation?
Select the same industry senior translators, expert review touches, let you enjoy fast and accurate human translation!
Translation: Stairs to AlwaysOn Level 1: What is SQL Server AlwaysOn?