1) SQL server clustering is a high-availability solution that assists businesses in meeting their availability and uptime targets
2) This availability assures that mission-critical services, applications, and data are always accessible in the event of a failure or disaster.
3) As an automatic technique, it enables one physical server to take over the functions and responsibilities of another that has failed.
4) Tasks and service cluster nodes are shared by the SQL server cluster.
5) Each of which can contain one (or more) shared discs organised into logical units known as resource groups.
Clustering Topologies:
There are four types of clustering topologies:
1) Single Instance (Active-passive)
2) Multiple Instances (Active-Active)
3) N+1 (No. Of Actives + 1 Passive)
4) N+M (number Actives actives plus multiple passives)
Single instance.
1. In this architecture, one node controls all resource groups, while the other nodes serve as backups. If the primary node fails, the resource group is moved to the backup node.
2. When the standby node comes online, it typically consists of discs containing the database files and the transaction log file.
3. When the secondary node becomes operational, the SQL server launches on the virtual server and recovers the database. This architecture is known as active-passive.
Multiple Instances:
1) In this design, resource groups are shared by virtual servers.
2) In a multiple-instance cluster, each node can host two (or more) virtual servers at the same time.
3) It is less expensive than a single-instance cluster because no nodes are held in reserve.
4) Multiple instance clusters are used for high performance, and load balancing is required.
N+1:
1) This topology is a variant of multiple-instance clustering; more nodes share the same failover nodes.
2) The standby node required large hardware capabilities to handle all N servers when they failed concurrently.
3) N+1 makes good use of resources because one of the backup nodes is down.
4) It does not provide the performance of a multi-instance cluster.
N+M
1) There are two (or more) operational nodes and two (or more) standby (passive) nodes in this design.
2) This configuration is more cost-effective than the other topologies.
3) It is not advised for use in production unless the client (or firm) requires N+M.
Terminologies
1) Node
2) Public network
3) Private network
4) Private storage
5) Shared disk (Resource group)
6) Quorum
7) MSDTC
8) Heart Beat
9) Look Alive
10) Is-Alive
11) Failover
12) Failback
13) Possible owner
14) Preferred owner
15) San
16) Virtual IP
Node:
1. Physical nodes running a Windows cluster-compatible operating system
2. Each node should have at least two network cards for public and private networks, as well as at least one local hard disk for the operating system and SQL binaries (predefined SQL directories) and some executable files (.dll files).
Public N/w/Private N/w
1. Each node requires at least two n/w cards, one public and one private.
2. Private N/W is utilized for internal node communication. It is known as a heartbeat.
3. A public network is used to connect to an external communication network (or an intranet).
Private Storage
Local Storage Discs There will be at least one private disc (OS and SQL binaries) referred to as private storage. You can add one additional disc if you like.
Shared Disk
1) Each server must be connected to an external storage device (SAN or RAID).
2) Non-cluster sql server databases (sys (or) resources) are stored on local attach DISC STORAGE.
3) However, with a cluster SQL server database, all nodes that are participating in clustering (or are physically connected to a shared disk) store the database.
4) This storage setup enables the programme to failover between cluster server nodes.
Quorum
1) It is a cluster configuration file. This file (Quorum.log) is located on the Quorum disc (one disc from the common disc).
2) Serve as a link between the nodes. It stores the most recent cluster configuration and resource data.
3) When one node fails, the other node can take over. In real life, it is similar to an emergency contact number and other medical information.
In Quorum we have different types
1) Node majority
2) Node-Disk Majority
3) Node + file share majority
MSDTC (Microsoft Distribution Transaction Co-ordinator)
1) It is stored on a shared hard disk.
2) It merges many data sources from the client application into a single transaction while maintaining transaction integrity (ACID characteristics). While interacting with the servers
3) MSDTC is responsible for the ACID characteristics of transactions until the end.
4) It is primarily used in two-way communication to handle distribution queries.
5) It is not recommended to store MSDTC on a local drive.
Heart Be at:
1) It is a cluster health check mechanism.
2) A single UDP (user datagram protocol) packet is sent between cluster nodes over a private network to confirm that nodes are still online.
3) By default, the cluster service will wait 5 seconds (one heartbeat is sent every 1 second).
4) Prior to considering the cluster node to be unavailable,
UDP
When messages are transferred between networks, it is a frequent practice that provides a limited level of service.
Looks Alive:
1) It is a simple resource health check to ensure that the service (SQL Services) is operational.
2) To accomplish this, the cluster service requests the Windows service control manager to determine the state of the service.
3) By default, the look-alive check will take 5 seconds.
IS Alive
1) It checks to see if the resource is running properly or not.
2) If this check fails, the resource is decommissioned and the failover procedure is initiated.
3) Examine the cluster when it is IS-Alive.
4) The service connects to the SQL server instance and runs the select @@servername command.
5) It will just check the availability of the SQL server. It does not verify the availability of user databases. This function is invoked approximately every 30–60 seconds.
Note: When the seems-alive test fails, the is-alive check calls are immediately executed.
Failover:
When a node becomes inactive for any reason, the cluster service fails over any group that the node Failbacks retain.
There are two types
1) Failback immediate
2) Restrict failback -> manual failback
When a node becomes inactive for any reason, the cluster service fails over any groups that the node is a member of.
When a node becomes active again, the cluster service failback groups that the node previously held are restored.
Possible Owner
These are the server nodes that the cluster group can failover to.
Preferred Owner
It is the server node that is ideally suited to executing an application (or group).
Cluster service
1) It is required to run the Windows cluster.
2) It manages all cluster activities.
3) It will play a significant role anytime a failover occurs.
4) Each cluster node is responsible for its own cluster service.
Aware and Unaware Services
Aware:
1) Database engine service (Agent complete text).
2) The analysis service (SSAS)
3) Notepad Services
4) Printing Services
Unaware:
5) Reporting (SSRS)
6) Integration (SSIS)
7) Notification
8) Online books & components
Virtual IP:
1) Used for SQL IP and client communication.
2) The client should always connect to the SQL IP / virtual IP.
Can do on cluster
1) Clustering is intended to improve the availability of physical hardware and SQL server services (Any cluster Aware Services).
2) Closing can help reduce downtime during cluster node maintenance, such as by changing the hardware of the physical node or applying a new operating system service pack.
Cannot doon cluster
1) It will not provide high availability in the event of a shared disc failure or a power outage in the data centre.
2) Clustering is not designed to secure data; instead, backups, logshipping, and mirroring should be used.
3) A failover cluster is not a load-balancing solution. Load balancing is the process of acting as many servers as one, dispersing the load among the servers.
Cluster service architecture:
Microsoft Cluster Service is made up of three main words.
I) Cluster services
2) Resource monitor
3) Resource DLLs
1) Cluster Services:
➤ It will check the node status to see if it is running (or not).
➤If one node fails, the failover is pushed because it is a high-priority service.
2) Resource Monitor
➤ The resource monitor acts as an interface between the cluster and the resource DII and runs as an independent process.
➤ Cluster services communicate with resources using the resources monitor. DII (Dynamic link Library)
➤ Multiple copies of the resource monitor can run on a single node.
➤ When a cluster service needs to conduct an operation on a resource, it sends the request to the resource monitor, who will handle it.
3) Resource DLL’s:
➤ It handles all communication with the resource. Resource monitor and resource DLL command utilising resource apps Ex: disc, network name, IP address, database, websites, application programme.
Cluster Technologies:
Microsoft servers offer clustering with three technologies.
1) Network load balancing (NLB)
2) Component Load balancing (CLB)
3) Microsoft cluster service (MSCS)
1) Network load balancing (NLB)
➤ It serves as a front-end cluster, distributing incoming IP traffic among cluster services.
➤ In Windows 2003, up to 32 machines can share a single lp address (in Windows 2008, up to 64 computers). As traffic grows, additional servers can be added to the cluster; up to 32 servers are possible in any one cluster.
➤ NLB also enables high availability by recognising the failover cluster automatically.
2) Component Load balancing (CLB)
➤ It enables dynamic balancing of COM+ components among a set of up to eight identical servers.
➤ It serves as a middleware cluster.
Useful commands for clustering
➤ How to View SQL Clusters
Go to cmd→ cluster/list
➤ View Status of Nodes
Cluster node -or cluster node /status
➤ View Status of Cluster Groups
Cluster group –or cluster group /status
➤ View Status of Cluster Networks
Cluster network –or cluster network /status
➤ View Properties of All Network Interface Devices
Cluster netinterface –or cluster netinterface/statu
➤ View Status of Cluster Resources
Clusterter resource –or Clusterter resource/status
➤ Failover Service to a New Node
Cluster group “groupname” /move:nodeName
➤ What machine owns the cluster resources
Select serverproperty (‘computernamephysicalnetbios’)
➤ Get all cluster node machine names
sys.dm_os_cluster_nodes