Shulou(Shulou.com)06/01 Report--

2.3.4 add AG as a cluster resource

Install the SQL Server resource agent integrated with Pacemaker for SQL Server

Run on all nodes:

Sudo yum install mssql-server-ha

Create a SQL Server login for Pacemaker

Run on all nodes:

USE [master] GOCREATE LOGIN [pacemakerLogin] with PASSWORD= Nucleus ComplexPhoneAlter SERVER ROLE [sysadmin] ADD MEMBER [pacemakerLogin]

Alternatively, you can set permissions at a finer level:

GRANT ALTER, CONTROL, VIEW DEFINITION ON AVAILABILITY GROUP::ag1 TO pacemakerLoginGRANT VIEW SERVER STATE TO pacemakerLogin

On all nodes, save the credentials for the SQL Server login.

Echo 'pacemakerLogin' > > ~ / pacemaker-passwdecho' ComplexPenew0rdwords > > ~ / pacemaker-passwdsudo mv ~ / pacemaker-passwd / var/opt/mssql/secrets/passwdsudo chown root:root / var/opt/mssql/secrets/passwdsudo chmod 400 / var/opt/mssql/secrets/passwd # Only readable by root

Create AG resources in the cluster

Create an AG resource ag_cluster with availability group ag1 of type ocf:mssql:ag master/slave:

Sudo pcs resource create ag_cluster ocf:mssql:ag ag_name=ag1 meta failure-timeout=30s master notify=true

Note: after the resource is created, the Pacemaker resource agent automatically sets the REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT value of AG according to the configuration of AG on a regular basis.

Create virtual IP resources in the cluster

Run on one node:

Sudo pcs resource create virtualip ocf:heartbeat:IPaddr2 ip=

There is no virtual server name in Pacemaker. To replace the IP address with a string server name in the connection string, register the virtual IP address and the desired virtual server name in DNS. For disaster recovery configurations, register the virtual IP address and the desired virtual server name in the DNS of the primary site and the disaster recovery site.

Add Colocation constraint

Colocation constraint is mainly used to determine the location of resource B according to the node location of resource A, that is, when resource B is started, it depends on the node location of resource A. For example, if resource An and resource B are constrained by Colocation, assuming that resource An is already running on node1, resource B will also start on node1, and if node1 fails, resource B and A will simultaneously switch to node2 instead of one of the resources to node3.

Almost all decisions in the Pacemaker cluster, such as choosing where resources run, are based on comparative scores. Each resource calculates a score. Cluster Explorer selects the node with the highest score for a specific resource. If a node has a resource with a negative score, the resource cannot run on that node.

On Pacemaker clusters, you can make decisions about clusters with constraints. A constraint has a score. If the score of the constraint is lower than that of INFINITY,Pacemaker, treat it as a suggestion. A score of INFINITY is required.

To ensure that the master copy and the virtual ip resource run on the same host, define a host hosting constraint with a score of INFINITY. To add a colocation constraint, run the following command on a node.

Sudo pcs constraint colocation add virtualip ag_cluster-master INFINITY with-rsc-role=Master

Add sort constraint

The Colocation constraint has an implicit sort constraint. Before moving an AG resource, it moves the virtual IP resource.

To prevent the IP address from temporarily pointing to a node with a secondary copy before failover, add a sort constraint.

To add a sort constraint, run the following command on a node:

Sudo pcs constraint order promote ag_cluster-master then start virtualip

View cluster status

Sudo pcs status

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.