This article belongs to SQL Server AlwaysOn from getting started to Advanced series articles
This article originates from the stairway to AlwaysOn series. According to the work needs in the course of learning by passing translation for reference. The article series contains:
- SQL Server AlwaysOn from getting Started to advanced (1)-What is AlwaysOn?
- SQL Server AlwaysOn from getting Started to Advanced (2)--storage
- SQL Server AlwaysOn from Getting Started to Advanced (3)--Infrastructure
- SQL server AlwaysOn from getting started to Advanced (4)--Analyzing and deploying Windows Server Failover Cluster
- SQL Server AlwaysOn from Get started to Advanced (5)--Analyze and deploy failover Cluster Instance
- SQL Server AlwaysOn from Get started to advanced (6)--Analyze and deploy AlwaysOn availability Group
- SQL Server AlwaysOn from Getting Started to advanced (7) combination of--FCI and high availability groups using
- SQL Server AlwaysOn from Get started to advanced (8)--Isolate mirrored traffic in AlwaysOn
This article is the first section. That is, SQL Server AlwaysOn from getting Started to advanced (1)-What is AlwaysOn?
Objective:
AlwaysOn is a collection of complex techniques that are often misunderstood. This series will cover technologies for AlwaysOn, how to use them in highly available strategies, and how to use AlwaysOn. In this section, you'll look at several concepts: AlwaysOn, Failover Cluster Instance (FCI), and Windows Server Failover Cluster (WSFC). Understanding these basic concepts can play a big role in the daily operations.
Windows Server Failover Cluster (WSFC):
WSFC (Windows failover Clustering) is a core component of Microsoft's High Availability Technology (HA). WSFC is more of a feature of Windows Server than FCI and AlwaysOn, while the next two are SQL Server features, while the WSFC is lower, creating SQL Server Failover Cluster Instance (fci,sql Server failover cluster instance), SQL server AlwaysOn High Availability Group, other highly available technologies such as exchange, need to deploy and configure WSFC.WSFC can combine multiple computer nodes (pure physical machines, pure virtual machines, physical machine hybrid virtual machines) and provide highly available services to external applications. An application on the server, such as SQL Server, can run on any node of the cluster by providing a virtual access point (consisting of a unique IP address and a unique machine name, or "virtual Network Name") to the client program as a link through cluster. The address and Virtual name act as a "resource group" for an application, and are transmitted as tokens between multiple participating nodes. When the active node is in serious trouble, it causes the active node to stop the external service. This is when the Cluster service automatically attempts to restart the resource group of the current node or partner node.From a high-level perspective, the client's access point is transmitted along all the hard disks and service origins in the failover partner node. A clustered instance causes a disconnection of the client connection when a failover occurs, and then re-connects immediately after the other nodes are available. Common failures that cause failover include the following:
- Public network card or network failure
- Power failure
- Motherboard failure
- CPU failure
In WSFC, clustered applications are installed in separate groups or "applications" that contain a range of resources, such as hard disks, services, IP addresses, and so on. A group and its resources can only belong to one node at a time, and other partner nodes cannot access these resources unless a plan or sudden failover occurs.As shown in the typical WSFC, all nodes are interconnected over the network, and then domain controller and DNS servers are used to enable the client to access the node through a virtual IP or virtual network name without knowing which node is currently active.
For FCI deployments, the computer node "must be"With shared storage, and for a general AlwaysOn free group deployment, nodes can use local storage instead of having to use shared storage." Although the cluster nodes allow for completely different hardware, it is best to configure them uniformly to avoid load imbalance during the failover process and to withstand the stress of the failed nodes so that the transfer fails.However, each node must use the same OS and patch version, network configuration。 Also for the maximum number of nodes in the cluster, depending on the different versions of Windows Server (2003 is 8 nodes, 2008 is 16 nodes, 2012 is 64 nodes).A robust, robust Windows Server Failover cluster requires rigorous design, adequate hardware support, and an appropriate operating system version. Using a cross-public cluster also increases the cost of cluster design, planning, and resources.NeedKey Reminders, WSFC only provides failover capabilities, does not provide load balancing and scale-out capabilities, and each service can only be run on a single node.Typically, in a large multi-node cluster, the application may be a cross-subnet deployment. As you will see later, if you omit some of the settings, you will cause unnecessary failovers and do not violate the throttling policies of AlwaysOn available groups.WSFC requires some form of mediation to control the cluster resource owner. This intermediary is the quorum of the cluster. Starting with Windows 2003 SP1, this quorum is maintained as a node vote and is subject to a majority of minority principles. Additional quorum resources can also be added using the form of a remote file, such as a clustered local disk or a multi-site cluster share. Starting with Windows Server 2012, quorum begins using a feature that becomes a dynamic node weight configuration to balance the cluster against unnecessary failover processes in planned outages, which are further discussed in further detail.
Failover Cluster Instances (Failover cluster instance):Failover Cluster Instance of SQL Server (FCI) has been a common high-availability technology for SQL Server for a long time. SQL Server FCI can fail over between any available nodes in the cluster. The only drawback is storage. Because of the need to use shared storage, the storage subsystem becomes a point of risk for single points of failure.An FCI is an instance of SQL Server installed on the WSFC, whether it is a default instance or a named instance. This instance requires at least the following resources:
- IP Address
- Network Name
- Shared hard drives (n)
- SQL Server Service
- SQL Server Agent Service
The resource above is the same for a separate instance, except that the IP address and network name are from this machine, the hard disk belongs to the machine, and the FCI is different.
As shown, in a two-node FCI, the SQL Server instance uses shared storage that is available to the WSFC node as storage for SQL Server. This storage is typically a two-step deployment of the LUNS,FCI deployed in the SAN, followed by an in-depth introduction:
- Run the SQL Server Setup Wizard on the first node of the FCI and select New SQL Server failover cluster installation. After you complete the first step, you can begin the second step.
- Run the SQL Server Setup Wizard on the other participating nodes of WSFC and select Add nodes to the SQL Server failover cluster and complete the installation.
Note: Although the Standard Edition limits the 2-node FCI, it does not affect WSFC and is limited only at the SQL Server level. FCI is a bit like a team relay process. A computer node runs this clustered SQL Server application and its companion resources, and provides services to the client (holding the baton). Once the active node fails (the baton is dropped), the partner node starts and takes the task to continue (pick up the baton).
AlwaysOn availability Groups (AlwaysOn available groups):
Over the years, failover clustering is the primary technology for SQL Server high availability. When one node fails, the other node will take care of the client. AlwaysOn inherits the WSFC technology and provides a more resilient, highly available platform. But the cluster operates at the instance level, and AlwaysOn operates at the library level. AlwaysOn Available group technology, introduced from SQL 2012, is defined by a predefined set of database collections (available groups) and copied to a read-only partner instance or replica. Each node has a synchronized copy of the AlwaysOn database and is accessed through the listener.AlwaysOn available groups (AG) requires 1 to multiple secondary replicas to hold copies of highly available databases. These secondary databases are either readable or non-readable. It can also be updated synchronously or asynchronously. Asynchronous replicas support only manual forced failover, while synchronous replicas support automatic or manual failover.A secondary read replica can be configured to support only read-only queries, or it can make a secondary copy a place for backup and maintenance operations, thereby reducing the pressure on the primary replica.AlwaysOn relies on the core functionality of WSFC to complete the high-availability features of AO (AlwaysOn), but it differs from the FCI in the following sections:
- Shared hard Drive
- Shared IP Address
- shared Network Name
- Shared SQL Server and SQL Server Agent resources
However, when an AlwaysOn listener is used, an IP address and Network Name resource that is shared to each replica of the AO Group is created. As mentioned above, the disadvantage of FCI is shared storage, although there are many ways to mitigate the risk of this single point of failure, but usually with significant overhead (both configuration and cost), and is often difficult to configure and manage. In addition, the FCI is migrating server hardware and does not provide migration of single or multiple databases. Database mirroring is required, but the image is "single-Library", unreadable, and AlwaysOn available groups can be migrated as one unit in multiple libraries, with library readable.AlwaysOn also uses SQL Server endpoints to communicate between instances. Endpoints are automatically configured during the use of the Available Group Deployment Wizard. You can also create a highly available listener service to receive inbound connections, with a unique IP address and a unique virtual network name for the listener. This is one of the biggest changes so far in making the database highly available within the group.During the creation of AlwaysOn free groups, a clustered role is created in WSFC and contains independent resources. This resource is transferred synchronously during the failover process and identifies the location of the primary replica.
AlwaysOn Listener (AlwaysOn listeners):
when the listener is configured, some resources have been created in the application \ Role in the Failover cluster:
- Virtual IP Address
- Virtual Network Name
The listener connects to the revenue station using the TCP port, which is attached to the primary replica (Primary replica) by default. If a read-only route is configured (read only routing), then the specified connection using "Read Only" is routed to the secondary replica instead of the primary replica. The pressure on the primary replica has been largely diverted.When an AlwaysOn group fails over, the clustered application and its resources are transferred to the other nodes of the cluster. The cluster application tracks the node location of the primary replica and then moves the Cluster service in the underlying node as needed. In this process, the listener is held by the active node of Fci\alwayson.
Conclusion:
This section describes three core high-availability technologies related to SQL Server. We use WSFC as a foundation and deploy FCI or AlwaysOn available groups on top of that. This is followed by a highly available technical demonstration using FCI as SQL Server, followed by an AlwaysOn available group setup.The next section describes what you can do with storage in high-availability SQL Server.
List of terms:
AO (AlwaysOn availability Group) |
AlwaysOn High Availability Groups |
FCI (Failover cluster instance of SQL Server) |
SQL Server Failover cluster instance |
Tcp / ip |
Transmission Control Protocol/Internet Interconnection agreement. |
Os/nos |
Network operating system |
WSFC (Windows Server failover cluster) |
Windows Failover Clustering |
LAN (Local area Network) |
Lan |
WAN (Wide area Network) |
Wan |
DNS (Domain Name System) |
Domain Name System |
Dhcp |
Dynamic Host Setting protocol |
IP Address |
IP Address |
AD (Active Directory) |
Windows Active Directory |
DR (disaster recovery) |
Disaster recovery |
SPF (single point of failure) |
Single point of failure |
SCSI (Small computer Systems Interface) |
Small computer System interface |
ISCSI(Internet Small Computer Systems Interface) |
Internet Small Computer System interface
|
FC (Fibre Channel) |
Fiber |
Replica |
Replicas, SQL Server AlwaysOn available groups, participating in an AlwaysOn SQL Server instance |
SQL Server AlwaysOn from getting Started to advanced (1)-What is AlwaysOn?
