The host node in the failover cluster performs a "looks alive" check every 5 seconds. An IsAlive check is performed every 60 seconds using SELECT @@SERVERNAME. If this fails the IsAlive retries 5 times and then attempts to reconnect to the instance of SQL. If all fail, then the SQL Server resource fails. Depending on the failover threshold, configuration of SQL resource, Windows Clustering will either attempt to restart on same node or failover to another available node.

During failover, Windows Clustering starts the SQL Server service for that instance on the new node, and goes through the recovery process to start the databases.  After the service is started and the master database is online, the SQL Server resource is considered to be up. User databases will then go through the normal recovery process: any completed transactions in the t-log are rolled forward, and any incomplete transactions are rolled back. The length of the recovery process is dependent on how much activity must be rolled forward or rolled back upon startup.

Set the recovery interval of the server to a low number to avoid long recovery times and to speed up the failover process. SQL Server generates automatic checkpoints based on the "recovery interval" setting. Long running transactions can lead to much longer restart times than specified in the recovery interval option.

Failover/Failback Strategies

The cluster group containing SQL Server can be configured for automatic failback to the primary node when it becomes available again. By default, this is set to off.

To Configure:

• Right-click the group containing SQL Server in the cluster administrator, select 'properties' then 'failback' tab.
• To prevent an auto-failback, select 'Prevent Failback', to allow select 'Allow Failback' then one of the following options:
• Immediately: Not recommended as it can disrupt clients
• Failback between n and n1 hours: allows a controlled failback to a preferred node (if it's online) during a certain period.

Configure Node Failover Preferences

When you use more than 2 nodes, it's important to consider which nodes should own resources in the event of a failover. For example, in an n+1 configuration, each SQL Server group should have the idle node second in the list of preferred owners. N.B. Do not use cluster admin to remove nodes from the resource definition. USe SQL Server setup for that functionality.

To Configure:

• Right-click SQL Server group in the cluster administrator and select properties
• On the 'General' tab, the preferred owners list box contains all cluster nodes that can potentially own resources in that group, and the current order in which they will failover
• Click 'Modify' to change this order

Configure Thresholds for a Resource

• Right-click the cluster resource and then select 'Propereties'
• Click 'Advanced'
• Select 'Do not restart' if the cluster service should not attempt to restart. Restart is the default
• If 'Restart' is selected:
• Affect the Group: uncheck to prevent a failure of the selected resource from causing the SQL Server group to failover
• Threshold: number of times the cluster service will attempt to restart the resource, and period is the amount of time in seconds between retries
• Do not modify the 'LooksAlive' and 'IsAlive' settings
• Unless necessary, do not alter the 'Pending Timeout'. This is the amount of time the resource is either in the online or pending or offline pending states before the the cluster service puts it in either offline or failed state

Configure Thresholds for a Group

• Right-click the group containing the SQL Server virtual server then click properties
• Click the failover tab
• to configure the failover policy, in the threshold box enter the number of times the group is configured to failover within a set span of hours. In the period box, entrer the set span of hours
• Once the resource group reaches the set number of failovers, it will stay offline. However, other cluster resources, such as cluster IP, could be left online

Cluster Resource Dependencies

Cluster Heartbeat

Cluster nodes use the "heartbeat" signal to check whether each node is alive at both the OS level and SQL Server level. The node hosting the SQL Server resources uses the Service Control Manager to check every 5 seconds whether the SQL Server service appears to be running. This "LooksAlive" check does not impact performance but does not perform a thorough check; the check will succeed if the service appears to be running even though it might not be operational. As a result, a deeper check must be performed; this "IsAlive" check runs every 60 seconds.

IsAlive:

• Runs every 60 seconds
• Runs an @@SERVERNAME T-SQL query against SQL Server to determine whether the server can respond to requests
• Does not gaurantee that all user databases are available or are performing within necessary performance/response-time requirements

If IsAlive Check fails:

• Retried 5 times and then it attempts to reconnect to the instance of SQL Server
• If all 5 retries fail, the server resource fails
• Depending on the failover threshold config, the failover cluster will either restart the resource on the same node or it will failover to another available node

The IsAlive query tolerates a few errors, but ultimately it fails if it's threshold is exceeded

During failover of the SQL Server instance, SQL Server resources start up on the new node and SQL Server goes through the recovery process to restart the databases. After the service is started and the master database is alive, the SQL Server resource is considered to be up. User databases will go through the normal recovery process. Completed transactions in the transaction log are rolled forward (the Redo phase), incomplete transactions are rolled back (the Undo phase).

In SQL Server 2005 Enterprise Edition, each user database is available to the user once the Redo phase is complete. For all other editions (and all 2000 editions), each user database is unavailable until the Undo phase completes. Length of recovery process depends on how much activity needs to be rolled forward or back upon startup.

The 'recovery interval' sp_configure option of the server can be set to a low number to avoid longer Redo recovery times and to speed up the failover process. Undo recovery time can be reduced by using shorter transactions so that uncommitted transactions do not have much to roll back.

Recommended Heartbeat Configurations

• Two or more independent networks must connect the nodes of the cluster to avoid a single point of failure
• Use of 2 LAN's is typical (MS PSS does NOT support the config of a cluster with nodes connected by only one network)
• At least two of the cluster networks must be configured to support heartbeat communications between the cluster nodes to avoid a single point of failure
• To do so, configure the roles of these networks as either "Internal Cluster Communications Only" or "All Communications" for the cluster service
• Typically, one of these networks is a PRIVATE INTERCONNECT dedicated to internal cluster communication.
• Each cluster network must fail independently of all other cluster networks.
• The cluster networks must not have a component in common that can cause both to fail simultaneously.
• The use of a multiport network adapter, for example to attach a node to two cluster networks would not satisfy this requirement in most cases as the ports are not independent 
• Remove all unnecesary network traffic from the network adapter that is set to INTERNAL CLUSTER COMMUNICATIONS ONLY (also known as the "heartbeat" or "private" network adapter, to eliminate possible communication issues
• Clustering communicates using Remote Procedure Calls (RPC) on IP sockets with User Datagram Protocol (UDP) packets

Recommended Configuration for Private Adaptor in Windows 2000 and Windows 2003