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.
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.
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.
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’
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
|SQL IP Address (Virtual Server Name)||NONE|
|SQL Network Name (Virtual Server Name)||SQL IP Address|
|SQL Server||Disk Resource(s),|
SQL Network Name
|SQL Server Agent||SQL Server|
|SQL Server Full Text||Disk Resource(s)|
|Analysis Services (2005 only)||Disk Resource(s),|
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.
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