Commands we can use to manage SQL Server availability group on Linux Pacemaker cluster

NAME

Description

How to use it

cluster-recheck-interval

Polling interval for time-based changes to options, resource parameters and constraints.

A Pacemaker cluster is an event-driven system. As such, it won’t recalculate the best place for resources to run unless something (like a resource failure or configuration change) happens. If time-based rules are needed, the cluster-recheck-interval cluster option (which defaults to 15 minutes) is essential.This tells the cluster to periodically recalculate the ideal state of the cluster. For example, starting a resource between a certain period of time.

sudo  pcs property set cluster-recheck-interval=2min

corosync totem token timeout

This is  to make sure corosync is resilent against intermittent network glitches; the default is 1000 ms, or 1 second for a 2 node cluster, increasing by 650ms for each additional member; To use a value other than the default, add or edit the token line in /etc/corosync/corosync.conf e.g. totem { token: 5000}

cat /etc/corosync/corosync.conf

totem {

    version: 2

    cluster_name: pcmk

    secauth: off

    transport: udpu

    token: 10000   

}

start-failure-is-fatal

Indicates whether a failure to start a resource on a particular node prevents further start attempts on that node. When set to false, the cluster will decide whether to try starting on the same node again based on the resource's current failure count and migration threshold.

sudo pcs property set  start-failure-is-fatal=true

failure-timeout

cluster-recheck-interval indicates the polling interval at which the cluster checks for changes in the resource parameters, constraints or other cluster options. If a replica goes down, the cluster tries to restart the replica at an interval that is bound by the failure-timeout value and the cluster-recheck-interval value. For example, if failure-timeout is set to 60 seconds and cluster-recheck-interval is set to 120 seconds, the restart is tried at an interval that is greater than 60 seconds but less than 120 seconds. We recommend that you set failure-timeout to 60s and cluster-recheck-interval to a value that is greater than 60 seconds. Setting cluster-recheck-interval to a small value is not recommended.

sudo pcs resource update resource_name meta failure-timeout=60s

monitor_policy

Monitoring policy options are:

1) SERVER_UNRESPONSIVE_OR_DOWN: Fail if the SQL Server instance is unresponsive (unable to establish a connection) or down (the process is not running)
2) SERVER_CRITICAL_ERROR: Fail if sp_server_diagnostics detects a critical system error
3) SERVER_MODERATE_ERROR: Fail if sp_server_diagnostics detects a critical system or resource error
5) SERVER_ANY_QUALIFIED_ERROR: Fail if sp_server_diagnostics detects any qualified error 
pcs resource update C2PODDAG monitor_policy=1

sudo pcs resource  update resource_name  monitor_policy=2

This shows up as health-threshold [2]; in corosync logs

Connection_timeout

This is the timeout of the connection request to the sql server by the pacemaker. The timeout can be set using the pcs command.

sudo pcs resource update resource_name connection_timeout=90

demote action timeout (s)

Demote action demotes relevant resources that are running in master mode to slave mode. The timeout can be set using the pcs command.

sudo pcs resource update resource_name op demote  timeout=20

promote action timeout (s)

Promote action promotes relevant resource to the master mode. The timeout can be set using the pcs command.

sudo pcs resource update resource_name  op promote timeout=60

start action timeout (s)

Start action starts the resource. The timeout can be set using the pcs command.

sudo pcs resource update resource_name  op start timeout=60

stop action timeout (s)

Stop action stops the resource. The timeout can be set using the pcs command.

sudo pcs resource update resource_name  op stop timeout=60

monitor action timeout (s)

Monitor action checks the resource’s state.

sudo pcs resource update resource_name op monitor interval=11  role=Master timeout=11

Monitor timeout should be greater than the  connection timeout.