SQL SERVER – Understanding FAILOVERCLUSTERROLLOWNERSHIP with SQL Server Cluster Rolling Upgrade

I hardly get questions around the cluster and I try to keep away from the queries because some of these can lead to some deep level working with cluster. Some conversations can lead to a great learning experience. I started to hunt in the blog and saw there were a number of posts around (FAILOVERCLUSTERROLLOWNERSHIP) the cluster that caught my eyes:

SQL SERVER – Add Node in Cluster – Rule “Node and cluster edition match” failed

SQL SERVER – Add failover cluster node fails with “number of cluster nodes supported for edition”

So when a note landed about FAILOVERCLUSTERROLLOWNERSHIP landed into my inbox, I personally felt there needs to be an education around the fundamental need to understand how we get to this.

To upgrade a SQL Server failover cluster to the next higher version, you must run Setup with upgrade action on each failover cluster node, one at a time, starting with the passive nodes. As you upgrade each node, it is left out of the possible owners of the failover cluster. If there is an unexpected failover, the upgraded nodes do not participate in the failover until cluster resource group ownership is moved to an upgraded node by SQL Server Setup.

By default, Setup automatically determines when to failover to an upgraded node. This depends on the total number of nodes in the failover cluster instance and the number of nodes that have already been upgraded. When half of the nodes or more have already been upgraded, Setup causes a failover to an upgraded node when you perform upgrades on the next node. Upon failover to an upgraded node, the cluster group is moved to an upgraded node. All the upgraded nodes are put in the possible owners list and all the nodes that are not yet upgraded are removed from the possible owners list. As you upgrade each remaining node, it is added to the possible owners of the failover cluster.

This process results in downtime limited to one failover time and database upgrade script execution time during the whole failover cluster upgrade.

To control the failover behavior of cluster nodes during the upgrade process, run the upgrade operation at the command prompt and use the /FAILOVERCLUSTERROLLOWNERSHIP parameter.

Understanding FAILOVERCLUSTERROLLOWNERSHIP values

To control failover during rolling upgrade you MUST start setup from the command line using the following parameter: /FAILOVERCLUSTERROLLOWNERSHIP=0 | 1 | 2

Note: There is no option to control failover on UI. To control failover on UI, run setup on the command line by supplying the failover option. Possible FAILOVERCLUSTERROLLOWNERSHIP values are:

/FAILOVERCLUSTERROLLOWNERSHIP=0

• Do not roll cluster ownership to the upgraded nodes.
• Do not add this node to possible owners of the SQL Server cluster at the end of upgrade of this node.

/FAILOVERCLUSTERROLLOWNERSHIP=1

• Roll cluster ownership to the upgraded nodes (if this has not happened already).
• Add this node to possible owners of the SQL Server cluster at the end of upgrade of this node.

/FAILOVERCLUSTERROLLOWNERSHIP=2

• This is the default setting.
• This setting indicates that the SQL Server setup will manage the cluster ownership as needed.

I personally thought this was a great learning experience for our friends who work with Cluster and are doing a Cluster upgrade.

I am personally looking forward from you on your experience of working with Cluster. Have you ever done a rolling upgrade on your environment? What options did you use? Do let me know your experiences via comments.

Reference: Pinal Dave (https://blog.sqlauthority.com), Twitter.