LooksAlive check and is alive Check for SQL Server 2012 failover

What is LooksAlive check and is alive check

A SQL Server failover cluster is a highly available scenario that is based on a hot standby on the Windows Cluster service. While the cluster is running, the Windows Cluster service periodically detects the resource health state of the node and, if a failure occurs, switches the SQL Server service from the failed node to the available node based on a pre-defined failover policy to enable high availability of SQL Server.

LooksAlive and IsAlive are the two methods that Windows Cluster service periodically detects nodes ' resource health, which exist in resource DLLs.

Depending on the resource type, the resource DLL can be divided into two large classes. For the cluster IP, DNS name, service service (SQL Server), cluster disks, and other Windows-brought resource types, the resource DLL is the resource dll--"Clusres.dll" that comes with the Windows cluster Whereas, for those resources of their own exclusive resource type, they have exclusive resource DLLs, such as SQL Server and SQL Server Agent resource DLLs Sqlrvres.dll and Sqlatres.dll respectively.

Shows the types of resources in a cluster and the relationship between resource DLLs and resources:

The original is from the SQL Server 2012 Implementation and Management Guide

LooksAlive check and is alive check detection principle

The RHS.exe process (generated by the Windows Cluster Service process Clussvc.exe) defines the health state of the resource by calling the LooksAlive check and the Is Alive Check method resource DLL.

In a SQL Server failover cluster, for SQL Server resources, the LooksAlive check and is alive check detection are the following principles:

LooksAlive is a basic health check that checks whether the SQL Server service is in the startup state at the active point through the Service Control Manager (SCM), which is checked every 5 seconds by default.

IsAlive performs a SELECT @ @servername (prior to SQL Server 2012) or sp_server_diagnostics (SQL Server 2012) through the Windows Cluster service. It then determines whether SQL Server is available based on the results returned. It is checked every 60 seconds by default.

The SELECT @ @servername is a version of SQL Server 2012 that performs the operation of the IsAlive method. The results of the execution are returned to the failover cluster, and if the failover cluster receives the returned results, the resource is currently in a healthy state. Conversely, this resource is currently faulted.

This method is simple and "rough" and can be adapted to most scenarios, but if SQL Server load is high and there is no response within 60 seconds, a miscarriage of error may occur, causing a failover to occur. To solve this problem, Microsoft improved the IsAlive check method in SQL Server 2012 and later, instead of using the SELECT @ @servername, and replaced it with the stored procedure sp_server_diagnostics.

Sp_server_diagnostics returns more detail than the SELECT @ @servername (for example). SQL Server uses an internal algorithm to process the results of the sp_server_diagnostics, and then evaluates the health of SQL Server (System, resource, query, Io, and so on) from multiple dimensions. This can be used as a basis for the Windows failover cluster to determine if a failover is required. In addition to this, SQL Server 2012 also makes special optimizations:

The thread executing the sp_server_diagnostics uses preemption mode, and the thread's OS priority is higher than the normal thread;

Sp_server_diagnostics occurs every 20 seconds and, by default, fails over if the result of 3 (60 seconds) execution shows that the SQL Server resource is in a failed state

Note: The reason is 60 seconds, because the "resource properties" healthchecktimeout default to 60 seconds, can be artificially changed size, but generally remain the default.

Configuring LooksAlive check and IsAlive check

The interval between looksalive and IsAlive detection can be set in the "Advanced policy" of the "resource properties", generally by default, if you want to change, you can refer to the location settings.

LooksAlive check and is alive Check for SQL Server 2012 failover