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.
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