Category Archives: Clustering

Resources in a SQL Server Failover Cluster

In this article I will discuss about the resources that are part of a SQL Server failover cluster.

A SQL Server failover cluster instance consists of 2 kinds of resources.

1)Local Resources: Local resources are those components that are installed on each possible owner node. They remain on that node and do not move over during a failover.
2)The shared resources: Those resources that fail over between the owner nodes.

Let us now go through the resources that are part of a resource group in a SQL Server failover cluster.
a) SQL Server Database Engine\Agent service : The database engine and SQL Server agent resources are always part of any SQL Server failover cluster’s resource group where the database engine has been installed. If SQL server analysis service is installed, then it will be a part of the resource group. From an availability and performance perspective it is better if both are installed on separate resource groups.

b) IP Address and Network name: The IP address and network name component ensures an interface that clients can use to connect to the SQL Server instance  irrespective of the fact that which node is currently serving the cluster. During a failover event,  the network name and IP addresses is registered to redirect to the new node that is serving the SQL Server failover cluster instance. To the clients that are connecting to the SQL Server, this is a absolutely transparent process.

c) Shared disks:A SQL Server shared disk contains all of the system and user data including databases, logs for the SQL Server failover cluster instance. When a failover occurs, the disks are mounted on the new node. When SQL Server instance is started on the new node, it goes through recovery as part of the SQL Server startup and maintains access to the same database files that existed when it was running on the previous node.The shared disks are the single point of failure for a failover cluster instance. Windows Server and SQL Server failover clustering provides redundancy for machines, operating systems, and SQL Server binaries, but requires reliable storage to assure availability of the shared storage.

What is the difference between LooksAlive check and IsAlive check.

LooksAlive Check:
This check performs a basic verification that the SQL Server service is running on the hosted node which should be online on a given interval of time. The default time that has been set is 5 seconds.

In case the check fails, the cluster service performs another check which is very throrugh in nature called IsAlive Check to verify the failure.
The LooksAlive check is also known as Basic resource health check in Windows Server 2008.

IsAlive check:
This process checks and verifies the cached result of the internal IsAlive process in the SQL Server resource DLL. The internal IsAlive process runs every 60 seconds and verifies if SQL server is online on not. The check uses SELECT @@SERVERNAME to verify the state of the SQL Server.

In case the query fails , it runs additional retry login to avoid failures. On the event of retry logic failure, the internal IsAlive process shuts down the SQL server service and failover event is triggered. The IsAlive check also known as Thorough resource health check in Windows Server 2008

%d bloggers like this: