Skip to content

sql-server-linux-availability-group-cluster-rhel.md

Latest commit

 

History

History
137 lines (91 loc) · 6.61 KB

sql-server-linux-availability-group-cluster-rhel.md

File metadata and controls

137 lines (91 loc) · 6.61 KB
title Configure RHEL Cluster for SQL Server Availability Group | Microsoft Docs
description
author MikeRayMSFT
ms.author mikeray
manager jhubbard
ms.date 03/17/2017
ms.topic article
ms.prod sql-linux
ms.technology database-engine
ms.assetid b7102919-878b-4c08-a8c3-8500b7b42397

Configure RHEL Cluster for SQL Server Availability Group

This document explains how to create a two-node availability group cluster for SQL Server on Red Hat Enterprise Linux. The clustering layer is based on Red Hat Enterprise Linux (RHEL) HA add-on built on top of Pacemaker.

Note

Access to Red Hat documentation requires a subscription.

For more details on cluster configuration, resource agents options, and management, visit RHEL reference documentation.

Note

At this point, SQL Server service's integration with Pacemaker on Linux is not as coupled as with WSFC on Windows. From within SQL, there is no knowledge about the presence of the cluster, all orchestration is outside in and the service is controlled as a standalone instance by Pacemaker. Also, virtual network name is specific to WSFC, there is no equivalent of the same in Pacemaker. It is expected Always On dmvs that query cluster information to return empty rows. You can still create a listener to use it for transparent reconnection after failover, but you will have to manually register the listener name in the DNS server with the IP used to create the virtual IP resource (as explained below).

The following sections walk through the steps to set up a failover cluster solution.

Configure Pacemaker for RHEL

[!INCLUDE RHEL-Configure-Pacemaker]

Disable STONITH

Run the following command to disable STONITH.

sudo pcs property set stonith-enabled=false

Important

This is not supported by the clustering vendors in a production setup. For details, see Pacemaker Clusters from Scratch and Red Hat High Availability Add-On with Pacemaker: Fencing.

Create a SQL Server login for Pacemaker

[!INCLUDE SQL-Create-SQL-Login]

Create availability group resource

To create the availability group resource, set properties as follows:

  • clone-max: Number of AG replicas, including primary. For example, if you have one primary and one secondary, set this to 2.
  • clone-node-max: Number of secondaries. For example, if you have one primary and one secondary, set this to 1.

The following script sets these properties.

sudo pcs resource create ag_cluster ocf:mssql:ag ag_name=ag1 \
--master meta master-max=1 master-node-max=1 clone-max=2 clone-node-max=1 notify=true

Create virtual IP resource

To create the virtual IP address resource, run the following command on one node. Use an available static IP address from the network. Replace the IP address between `<...>'

sudo pcs resource create virtualip ocf:heartbeat:IPaddr2 ip=**<10.128.16.240>**

There is no virtual server name equivalent in Pacemaker. To use a connection string that points to a string server name and not use the IP address, register the IP resource address and desired virtual server name in DNS. For DR configurations, register the desired virtual server name and IP address with the DNS servers on both primary and DR site.

Important

Due to a known issue, listener like capabilities are not working properly in the curent preview. We are working on a fix to be available in the upcoming releases.

Add colocation constraint

To add colocation constraint, run the following command on one node.

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

Add ordering constraint

The colocation constraint has an implicit ordering constraint. It moves the virtual IP resource before it moves the availability group resource. By default the sequence of events is:

  1. User issues pcs resource move to the availability group primary from node1 to node2.

  2. The virtual IP resource stops on node 1.

  3. The virtual IP resource starts on node 2.

    [!NOTE] At this point, the IP address temporarily points to node 2 while node 2 is still a pre-failover secondary.

  4. The availability group primary on node 1 is demoted to secondary.

  5. The availability group secondary on node 2 is promoted to primary.

To prevent the IP address from temporarily pointing to the node with the pre-failover secondary, add an ordering constraint.

To add an ordering constraint, run the following command on one node:

sudo pcs constraint order promote ag_cluster-master then start virtualip

Manual failover

Important

After you configure the cluster and add the availability group as a cluster resource, you cannot use Transact-SQL to fail over the availability group resources. SQL Server cluster resources on Linux are not coupled as tightly with the operating system as they are on a Windows Server Failover Cluster (WSFC). SQL Server service is not aware of the presence of the cluster. All orchestration is done through the cluster management tools. In RHEL or Ubuntu use pcs and in SLES use 'crm' tools.

Important

If the availability group is a cluster resource, there is a known issue in current release where manual failover to an asynchronous replica does not work. This will be fixed in the upcoming release. Manual or automatic failover to a synchronous replica will succeed.

Manually failover the availability group with pcs. Do not initiate failover with Transact-SQL.

To manually failover to cluster node2, run the following command.

sudo pcs resource move ag_cluster-master node2 --master

[!INCLUDE Move-Resource]

[!INCLUDE Manage-Sync-Commit]