The resource DLL for the SQL Server service uses two functions that are used by MS Cluster service to check for availability of the SQL Server resource.

A simple check LooksAlive and more rigorous check called IsAlive

LooksAlive check: Cluster service calls looksAlive function every 5 seconds and LookAlive function Queries the service status by using the Windows NT Service Control Manager. When the LooksAlive test fails ISAlive test is called immediately.

ISalive Check: A more rigorous IsAlive function is called every 60 second and monitors the health of the SQL Server by opening up a connection to SQL Server and issuing “select @@servername” query over the connection. If the checks fail the online Thread reports this failure to the Cluster Service.

For example Assume SQL Server resource is initially in the offline state. Cluster service calls the routine in SQL Server resource DLL to bring the resource to an Online state. First the resource state is set as OnlinePending and then initiates the process of starting up the SQL Server resource. It starts the SQL Server, opens a connection to SQL Server and issues the “select @@servername” query, if this succeeds the resource is put in the Online state. If for some reason SQL Server is not able to start or the connection/query fails and SQL Server is not able to come Online within PendingTimeout, the cluster service tries to restart (or failover) the resource. If after repeated attempt SQL Server cannot be brought online, it is put in the failed state. If the SQLServer Resource DLL encounters an unrecoverable failure like failing to open cluster registry key etc. then resource DLL will put the SQL Server resource in Failed state. Once the resource is put in Failed state Cluster service will not attempt to restart/failover the resource and user intervention is required to diagnose and correct the issue.

By default, LooksAlive is fired every 5 seconds and IsAlive is fired every 60 seconds. The LooksAlive and IsAlive polling intervals can be changed in Cluster Administrator or failover cluster manager from the Advanced tab for the SQL Server resource or using the cluster.exe command prompt utility.