SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Preferred Node Policy-Setting on Traditional SQL Cluster

By SQL ADMIN 79,

Note: This document is intended for Active-Active traditional clusters (MSCS) with multiple SQL instances hosted on it. In big IT shops, due to separation of duties, host patching is managed by a different group other than the SQL Server group. The host patching team usually uses automated scripts to patch host servers.
After host patching, SQL instances on an active-active cluster may end up running on one single node. Due to the increase in load, resource consumption on the instances will eventually grow compared to the usage when SQL instances were installed. Due to constraints, some legacy applications/databases cannot be migrated to new infrastructure nodes. In this scenario, there is a need to balance the resources and make sure the instances are load balanced across all available nodes of the cluster. The preferred node setting makes sure that SQL instances are running on their preferred node only. Settings in the failback options helps us to configure the time period when auto failback can happen as well as number of attempts cluster service should try to perform the failback.
The below configuration is required to make sure that SQL instances are on their preferred node. 

First, log on to the server. Then open Failover Cluster Manager. In this example, SQL instance N2APP2 is chosen

Next, right click on the group and select properties.

On the general tab, select the preferred node. In this example preferred node is CHWS2653N2.

Now, click on the Failover tab.

  • Enter Maximum failures in the specified period = 2
  • Period (Hours) = 6
  • Select Radio Button “Allow Failback” radio button.
  • Select “Allow failback” radio button.
  • Select “Failback between” : 1 and 4 hours
  • Click “OK”

Make this change on all the cluster groups on the cluster. The changes will go into effect after the server restart.

Once the change goes into effect, anytime an instance is moved to node which is not a preferred node, cluster service will move back the instance to the preferred node in the above mentioned time window.

Ref: https://support-msft-us1.vtv.stillw.com/en-us/kb/197047

 

Resources:

Preferred Node Configuration 02Feb2017.doc
Total article views: 544 | Views in the last 30 days: 4
 
Related Articles
FORUM

SQL Cluster Failover/Failback

2 NOde Cluster looking to use a script to automate a failback from node 1 and node 2.

FORUM

Failback

Why do we have failback on the two node 2008 cluster environment though we hvae both nodes are in ...

SCRIPT

Check Instance Info

Named or default instance, this will check info like Virtual Server Name, Cluster Name , Current own...

FORUM

Multiple instances on a cluster?

How best to set up multiple instances on a cluster?

FORUM

Do we need to set the preferred node?

Preferred Owner

Tags
administration    
clustering    
 
Contribute