Ladder Level 1 Forever: What is SQL Server AlwaysOn?

by Perry Whittle,2016/02/24(first released:2014/09/24) the seriesThis article is"Ladder series: AlwaysOn Ladder" part of 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 here .The first level of the SQL Server AlwaysOn Ladder series. In this level 1 article, we will find "AlwaysOn", "Failover Cluster Instance" (FCI), and "Windows Server Failover Clustering" technology. We'll cover each detail in detail and summarize the location of the high availability stack where they are located. This will provide us with a good foundation, which is essential for ascending stair levels. A higher-level staircase will explore the required infrastructure and different storage requirements and options used by AlwaysOn availability groups and FCI.after each stair level, you will learn more aboutHow AlwaysOn structures are built. Although as a DBA, you may not have any direct interaction with the core infrastructure projects under AlwaysOn and FCI, but it helps to get a complete picture of how all technologies are integrated. The final staircase will result in a feature AlwaysOn configuration.We must first look at the basics, including theA brief description of 3 technologies. Many acronyms and abbreviations are used in the AlwaysOn description. A list of common terms is included at the end of this article.Needless to say, let's take a closer look at each of these technologies.

Windows Server failover Clustering for 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/or virtual) to provide high availability for a set of applications. Applications are server software, such as SQL Server or exchange, and we want to be able to run on any node. Make your app highly available by rendering a virtual access point that includes a unique IP address and a unique computer name or "virtual network name" to the client. This address and virtual name become resources in the application group and are passed between participating nodes, such as tokens. A serious hardware failure of the active computer node will result in the loss of the group service running on that node. The Cluster service automatically attempts to restart the group on the current node or partner node based on the type of failure (hardware or software). at a higher level, client access point details are transferred to the failover partner node along with any disk and service resources. A failover of a clustered instance causes a client connection to disconnect , and once the service is available on another node, the client can reconnect. Common failures are usually one of them, but any failure of the application can cause the service to move to another node: a public NIC or network failure? Power failure? Motherboard failure? CPU failure when using WSFC, a clustered application is installed into a separate group or "application" that contains a set of resources, such as disks, services, IP addresses, and so on. A group and its resources are owned by a single node at all times, and resources cannot be accessed from any other partner node unless there is a planned switch or failover to that node. the following shows A typical view of a Windows server failover cluster. Cluster nodes are all over a network connection, and the domain controller and DNS service work with WSFC to allow clients to connect to the virtual IP or virtual network name regardless of the node on which the service runs.

in order to deployFCI, computer nodes must use shared storage, which typically appears to each node from the SAN. To deploy a typical AlwaysOn group, the node leverages its own local storage rather than sharing it with other cluster partners.Although cluster nodes may have different hardware, it is often best to keep the hardware between nodes consistent to avoid weaker nodes from being able to handle loads that exceed their functionality. However, the node must use the same operating system patch level and network configurationThis becomes clear when you validate your configuration before deploying a Windows Server failover cluster. The maximum number of cluster nodes in the Windows Server version (8 nodes in Windows 2003, 16 nodes in Windows 2008, and 2012 nodes in Windows 64) is different.Deploy a powerfulWindows Server failover clustering requires careful design, supported hardware, and the appropriate version of the Windows Server operating system. Geographically dispersed clusters (clusters across multiple WANs) further increase the amount of design and planning required and significantly increase costs.knowIt is important that WSFC provide failover partner functionality only. The application has no load balancing or scaling between nodes. Each service runs on one and only one node.Typically, in a large multi-node cluster, you can cross-A subset of Windows server failover cluster nodes is installed in the cluster application. The error of installing an application on all nodes can lead to some unwanted failovers, which we will see later, as well as violations of AlwaysOn group throttling policies, which ensure that all AlwaysOn instances reside on different nodes in the cluster. WSFC requires some form of mediation to control cluster resource ownership. This arbitration is provided in the form of cluster quorum. Since the Windows 2003 SP1, this quorum has maintained a majority of the votes required for quorum in the form of a node voting system. You can also use other quorum resources in the form of disks to localize your cluster, or you can use a remote file share for a multi-site cluster. Starting with Windows Server 2012, quorum uses dynamic node weight configuration to dynamically balance cluster polls during a scheduled outage to prevent unnecessary failovers. We will discuss the quorum in more detail at a future level.

Failover cluster Instance The failover cluster instance of SQL Server has always been a popular high availability technology in SQL Server products. SQL Server highly available instances are clustered to mitigate any node hardware failure and any potential software failure. The only weak link here is the storage; The storage subsystem becomes a single point of failure. the failover cluster instance is the default or named An instance of SQL Server that has been installed as a clustered application on the WSFC. Clustered applications typically have the following resources:? IP address? Network name? shared disk? SQL Server service? The SQL Server Agent service Standalone instance shares the same basic requirements, except that when using a standalone instance,the IP address and network name are obtained from the computer node itself, and the disk storage is provided by the computer's local disk resource.

referring to the diagram above, we see a single Typical view of an FCI's 2-node cluster. The clustered instance of SQL Server will use any shared storage that has been rendered to the WSFC node. Typically this storage will take the form of LUNs provided from the SAN. The FCI deployment of SQL Server is in a two-step process, which is described in a later step. Now, here's a basic overview of the two-step process of deploying a failover cluster instance of SQL Server: 1. Start the new SQL Server Failover Cluster Installation Wizard on the first computer node that participates in the FCI. Once completed and completed successfully, you will be able to enter the second phase. 2. Start the Add nodes to SQL Server Failover Cluster Wizard on any computer node in the WSFC that you want to join the new SQL Server FCI. Note: Although the Standard Edition will An FCI is limited to 2 nodes, but does not specify how many nodes have membership in Windows Clustering (you may have any number until the maximum value of the operating system). This restriction is performed at the SQL Server Setup level. FCI is a bit like the passing of a runway relay team; the computer node owns the clustered SQL Server application and its resources, and then provides the client with access to the SQL Server service (which holds the baton). When the active computer node fails (drops baton), the partner node enters and obtains ownership of the cluster application and its resources (on the baton).

AlwaysOn Availability Groups over the years, failover clustering has been aSQL Server provides the primary method for high availability. When one node fails, the other node takes over to provide the SQL Server service to the client. AlwaysOn integrates with Windows Server failover clustering technology to provide a more resilient, high-availability platform.Although the cluster works at the instance level,AlwaysOn is configured at the database level. AlwaysOn availability groups are new technologies introduced in SQL Server 2012 that replicate predefined database groups to a known set of read-only partner instances or replicas in AlwaysOn. Multiple nodes each host a synchronized copy of an AlwaysOn database, and it is best to provide access through the configuration of the listener (more on this later). AlwaysOn availability groups require one or more secondary replicas to host a copy of the high Availability database. These secondary databases may be either readable or unreadable. They can also be updated asynchronously or in a synchronous fashion. Asynchronous replicas support only manual forced failover, while synchronous replicas support automatic or manual failover.Secondary read replicas can be configured to respond to read-only queries, and you can also target secondary targets as backups/maintenance operations to relieve the pressure on the primary database. This master-slave relationship is also reversible to ensure true high availability. Any properly configured read-only partner can assume the primary role in the event of a system failure. AlwaysOn relies on the WSFC core functionality to achieve the high availability offered by AO, but does not require any of the following shared resources related to FCI. shared disk? Shared IP address? Share Network Nameshared bySQL Server and SQL Server Agent resourcesthere is one exception to this shared resource rule. CreateAlwaysOn group listeners, an IP address and Network Name resource that will be shared by the AO group copy will be created.as we have discovered, the weak links in the failover cluster instance chain are shared storage. There are many ways to achieve redundancy, but it is often expensive and often difficult to install and maintain. Of course, as mentioned earlier, failover cluster instances can only mitigate server hardware. It does not provide a single or even multiple secondary databases. We areDatabase mirroring is available in versions of SQL Server prior to SQL Server 2012, but these provide scope only for a single, unreadable secondary database. AlwaysOn still communicates with the familiar SQL Server endpoint as an instance. Endpoints are configured automatically when you use the Availability Group Deployment Wizard. Wizard-driven deployment provides the simplest deployment path, while manual deployment requires a lot of manual interaction. Nonetheless, a basic AlwaysOn group configuration is still very easy to deploy and configure and provides HA levels that were previously unavailable, without the need for complex functional integration levels.You can also create a highly available listener service that you will use to accept incoming connections to the availability group. The listener consists of a uniqueAn IP address and a unique virtual network name. This is one of the most significant changes that make the database in the group highly available.When you createDuring an AlwaysOn availability group, a clustered role is created within the Windows Server failover cluster and contains a resource. This resource fails over between partner nodes during an AlwaysOn group failover and identifies the primary replica of an AlwaysOn group.

AlwaysOn Listeners The listener is created as a resource at the time of configuration and resides in the AlwaysOn availability groups in the Failover Cluster application role. Resources are:? Virtual IP address? The virtual network Name Listener uses the TCP port to accept incoming connections and connects to the primary replica by default. When read-only routing is configured, the connection to the listener that points to the read-only intent connection is routed to the secondary partner instead of the primary replica. This is another way we can reduce the load on the primary replica. in the During a failover of an AlwaysOn group, the cluster application and its resources between the nodes in the cluster will fail over. The node location of the cluster application is followed by the?? The primary replica and its underlying nodes, and move 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. Conclusion This is the end of Step 1, 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, we have an FCI at the top of the WSFC that relies on the cluster to service and secure instances of SQL Server. Finally, we have an AlwaysOn group, which sits on a standalone instance of SQL Server and on the failover cluster instance of SQL Servers. in Level 2, we will look at the storage types available in SQL Server high availability and their typical usage. This will help you understand the future ladder levels in the series.

Glossary

AO	AlwaysOn Availability Group
Fci	Failover cluster instance of SQL Server
TCPIP	Transport Control Protocol/internet Protocol. The network protocol used by Microsoft client networks
Os/nos	Network Operating System
WSFC	Windows Server Failover Cluster
LAN	Local Area Network
WAN	Wide Area Network
Dns	Domain Name System
Dhcp	Dynamic Host Configuration Protocol, automatically assigns IP addresses to network computers
IP Address	A + bit (IPV4), unique address assigned to a computer object
AD	Active directory, directory services. The Microsoft technology used for object management in Windows domains
DR	Disaster recovery
Spf	Single point of failure
Scsi	Small Computer Systems Interface
ISCSI	Internet Small Computer Systems interface
Fc	Fibre Channel
Replica	The terminology used in SQL Server AlwaysOn availability Groups to refer to an instance of SQL Server which are part of a P Articular AlwaysOn Group

Ladder Level 1 Forever: What is SQL Server AlwaysOn?