A Brief Introduction to SQL Server Clustering

jeffluo

Computer Clusters

Collection of independent computer system interconnected with each other that works in a unit-way system is known as computer cluster, or windows cluster. The cluster may be of two system connected or multiple systems connected with each other. The basic concept behind SQL Server clustering is to provide high availability and high throughput for SQL Server database.

Figure Demonstrating the Overview of Window Cluster Setup

In Windows Cluster, every node/system interconnected with each other has their own hard disk, memory, operating system, and subset of the resources. For using cluster service, the providers provide users with a username for using the service.

Types of Clusters in SQL Server

There are following two types of SQL Server Clustering:

1. Network Load Balancing Cluster in SQL Server

The type of cluster in which the load is distributed among interconnected systems is known as network load balancing cluster (or NLB cluster). It enhances the availability and scalability of server applications by set of cluster IP addresses dedicated to each host. The client host communicates to) and does not know which system in the cluster is actually serving their request. However, if a host fails thensingle server ofautomatically redistribute load cluster (a virtual server . the among the active at that timeare interconnected computers that

2. SQL Server Failover Clustering

Two or more independent servers interconnected with each other with as SQL Server failover clustering. The servers in failover type of clustering to as nodes. SQL Server failover cluster to provide services of availability and application services. Failover is a process in which if another node then one of the nodes are used is known means of physical cables and softwarefails provides the same service. When failure occurs within one system of cluster, then the resources are redirected and the workload is redistributed to another system of the cluster. are referred

Figure Demonstrating Layout of SQL Server Failover Clustering

Each node operates on its own, independent of other servers in the cluster. Therefore, if one server gets fail, then the other nodes take over its functioning. In failover cluster, the cluster service has to register the address of network for usage of resources on the new server to route the traffic to the new server. We can configure our server in following two manners:

a. Active and Active Server- In this cluster runs the applications along with backing up the data for the other server.of each server

b. Active and Passive Server- In this at once, only one server runs an applications while the other ones serve as backup.

Shared storage is on a SAN (Storage Area Network) in a failover cluster. At the time of SQL Server installation on a cluster, system and user databases are required for storing which allows the cluster to move the SQL instance to any node whenever you require. Only one copy of data is available, but network name and SQL server service can be activated from any node, at any instance of time.

At the time the failover cluster starts, one of the nodes takes the right of resource groups and makes SQL Server instance online. Such resources are owned by following node:

> Network Name

> IP Address

> Shared Disks

> SQL Server Database Engine Service

> SQL Server Agent Service

> SQL Server Analysis Services

Following are the events when failover occurs in SQL Server:

1. All unusual pages of the buffer cache are written to disk, unless a hardware or system failure occurs

2. All SQL Server Services in resource group gets stopped on the active node

3. The resource group ownership is given to othercrystal node within

4. The new resource owner starts its SQL Server services

The cluster changes the SQL Server instance if one of the servers is failed or has some problem. The method guarantees redundancy. Since there is only one storage space, regular SQL Server maintenance requirements are essential.

Example of SQL Server Failover Clustering

Suppose we have two nodes within a cluster named SRV 01 and SRV 02. Now, due to some issue SRV 01 crashes and failover cluster service gets aware of such problem automatically. Then the cluster service automatically will start the functioning on SRV 02.

Hence, SQL Server failover clustering is helpful where there is an environment, in which the downtime is measured within seconds. Because of only one storage space, regular SQL Server maintenance is still needed.