Nov 22, 2016

How to Set Up Shared Disk Cluster for SQL Server on Linux


This guide will walk you through the steps to create a two-node shared disk cluster for SQL Server on Red Hat Enterprise Linux 7.2. The clustering layer is based on Red Hat Enterprise Linux (RHEL) HA add-on built on top of Pacemaker. The SQL Server instance is active on either one node or the other.




Note
Access to Red Hat documentation requires a subscription.
As the diagram below shows storage is presented to two servers. Clustering components - Corosync and Pacemaker - coordinate communications and resource management. One of the servers has the active connection to the storage resources and the SQL Server. When Pacemaker detects a failure the clustering components manage moving the resources to the other node.


For more details on cluster configuration, resource agents options, and management, visit RHEL reference documentation.
Note
This is not a production setup. This guide creates an architecture that is for high-level functional testing.
The following sections walk through the steps to set up a failover cluster solution.

Setup and configure the operating system on each cluster node

The first step is to configure the operating system on the cluster nodes. For this walk through, use RHEL 7.2 with a valid subscription for the HA add-on.

Install and configure SQL Server on each cluster node

  1. Install and setup SQL Server on both nodes. For detailed instructions see Install SQL Server on Linux.
  2. Designate one node as primary and the other as secondary, for purposes of configuration. Use these terms for the following this guide.
  3. On the secondary node, stop and disable SQL Server. 

The following example stops and disables SQL Server:

  1. sudo systemctl stop mssql-server
    sudo systemctl disable mssql-server
    
On the primary node, create a SQL server login for Pacemaker and grant the login permission to run sp_server_diagnostics. Pacemaker will use this account to verify which node is running SQL Server.

sudo systemctl start mssql-server
Connect to the SQL Server master database with the sa account and run the following:

USE [master]
GO
CREATE LOGIN [] with PASSWORD= N''

GRANT VIEW SERVER STATE TO 

On the primary node, stop and disable SQL Server.

Configure the hosts file for each cluster node. The host file must include the IP address and name of every cluster node.

Check the IP address for each node. The following script shows the IP address of your current node.

sudo ip addr show

Set the computer name on each node. Give each node a unique name that is 15 characters or less. Set the computer name by adding it to /etc/hosts. The following script lets you edit/etc/hosts with vi.

sudo vi /etc/hosts

The following example shows /etc/hosts with additions for two nodes named sqlfcivm1and sqlfcivm2.

127.0.0.1   localhost localhost4 localhost4.localdomain4
::1       localhost localhost6 localhost6.localdomain6
10.128.18.128 sqlfcivm1
10.128.16.77 sqlfcivm2
In the next section you will configure shared storage and move your database files to that storage.

Configure shared storage and move database files

There are a variety of solutions for providing shared storage. This walk-through demonstrates configuring shared storage with NFS.

Configure shared storage with NFS

On the NFS Server do the following:


Install nfs-utils

sudo yum -y install nfs-utils
Enable and start rpcbind

sudo systemctl enable rpcbind && systemctl start rpcbind
Enable and start nfs-server

systemctl enable nfs-server && systemctl start nfs-server
Edit /etc/exports to export the directory you want to share. You will need 1 line for each share you want. For example:

/mnt/nfs  10.8.8.0/24(rw,sync,no_subtree_check,root_squash,all_squash)
Export the shares

sudo exportfs -rav
Verify that the paths are shared/exported, run from the NFS server

sudo showmount -e
Add exception in SELinux

sudo setsebool -P nfs_export_all_rw 1
Open the firewall the server.

sudo firewall-cmd --permanent --add-service=nfs
sudo firewall-cmd --permanent --add-service=mountd
sudo firewall-cmd --permanent --add-service=rpc-bind
sudo firewall-cmd --reload

Configure all cluster nodes to connect to the NFS shared storage

Do the following steps on all cluster nodes.

From the NFS server, install nfs-utils

sudo yum -y install nfs-utils
Open up the firewall on clients and NFS server

sudo firewall-cmd --permanent --add-service=nfs
sudo firewall-cmd --permanent --add-service=mountd
sudo firewall-cmd --permanent --add-service=rpc-bind
sudo firewall-cmd --reload
Verify that you can see the NFS shares on client machines

sudo showmount -e 
Repeat these steps on all cluster nodes

Mount database files directory to point to the shared storage

  1. On the primary node only, save the database files to a temporary location.
  2. On all cluster nodes edit /etc/fstab file to include the mount command.
:  nfs timeo=14,intr
The following script shows an example of the edit.

10.8.8.0:/mnt/nfs /var/opt/mssql/data nfs timeo=14,intr
Run mount -a command for the system to update the mounted paths.

Copy the database and log files that you saved to /var/opt/mssql/tmp to the newly mounted share /var/opt/mssql/data. This only needs to be done on the primary node.

Validate that SQL Server starts successfully with the new file path. Do this on each node. At this point only one node should run SQL Server at a time. They cannot both run at the same time because they will both try to access the data files simultaneously. The following commands start SQL Server, check the status, and then stop SQL Server.

sudo systemctl start mssql-server
sudo systemctl status mssql-server
sudo systemctl stop mssql-server
At this point both instances of SQL Server are configured to run with the database files on the shared storage. The next step is to configure SQL Server for Pacemaker.

Install and configure Pacemaker on each cluster node

On both cluster nodes, create a file to store the SQL Server username and password for the Pacemaker login. The following command creates and populates this file:

sudo touch /var/opt/mssql/secrets/passwd
sudo echo "" >> /var/opt/mssql/secrets/passwd
sudo echo "" >> /var/opt/mssql/secrets/passwd
sudo chown root:root /var/opt/mssql/secrets/passwd 
sudo chmod 600 /var/opt/mssql/secrets/passwd
On both cluster nodes, open the Pacemaker firewall ports. To open these ports with firewalld, run the following command:

sudo firewall-cmd --permanent --add-service=high-availability
sudo firewall-cmd --reload
If you’re using another firewall that doesn’t have a built-in high-availability configuration, the following ports need to be opened for Pacemaker to be able to communicate with other nodes in the cluster
  • TCP: Ports 2224, 3121, 21064
  • UDP: Port 5405
Install Pacemaker packages on each node.

sudo yum install pacemaker pcs fence-agents-all resource-agents
Set the password for for the default user that is created when installing Pacemaker and Corosync packages. Use the same password for on both nodes.

sudo passwd hacluster

Enable and start pcsd service and Pacemaker. This will allow nodes to rejoin the cluster after the reboot. Run the following command on both nodes.

sudo systemctl enable pcsd
sudo systemctl start pcsd
sudo systemctl enable pacemaker
Install the FCI resource agent for SQL Server. Run the following commands on both nodes.

sudo yum install mssql-server-ha

Create the cluster

One one of the nodes, create the cluster.

sudo pcs cluster auth  -u hacluster
sudo pcs cluster setup --name  
sudo sudo pcs cluster start --all
RHEL HA add-on has fencing agents for VMWare and KVM. Fencing needs to be disabled on all other hypervisors. Disabling fencing agents is not recommended in production environments. As of CTP1 timeframe, there are no fencing agents for HyperV or cloud environments. If you are running one of these configurations, you need to disable fencing. *This is NOT recommended in a production system!*
The following command disables the fencing agents.

sudo pcs property set stonith-enabled=false
sudo pcs property set start-failure-is-fatal=false
Configure the cluster resources for SQL Server and virtual IP resources and push the configuration to the cluster. You will need the following information:

  • SQL Server Resource Name: A name for the clustered SQL Server resource.
  • Timeout Value: The timeout value is the amount of time that the cluster waits while a a resource is brought online. For SQL Server, this is the time that you expect SQL Server to take to bring the master database online.
  • Floating IP Resource Name: A name for the IP address.
  • IP Address: THe IP address that clients will use to connect to the clustered instance of SQL Server.

Update the values from the script below for your environment. Run on one node to configure and start the clustered service.

sudo pcs cluster cib cfg 
sudo pcs-f cfg resource create  ocf:sql:fci op defaults timeout=
sudo pcs-f cfg resource create  ocf:heartbeat:IPaddr2 ip=
sudo pcs-f cfg constraint colocation add  
sudo pcs cluster cib-push cfg
For example, the following script creates a SQL Server clustered resource named mssqlha, and a floating IP resources with IP address 10.0.0.99. It also starts the failover cluster instance on one node of the cluster.

sudo pcs cluster cib cfg
sudo pcs-f cfg resource create mssqlha ocf:sql:fci op defaults timeout=60s
sudo pcs-f cfg resource create virtualip ocf:heartbeat:IPaddr2 ip=10.0.0.99
sudo pcs-f cfg constraint colocation add mssqlha virtualip
sudo pcs cluster cib-push cfg
After the configuration is pushed, SQL Server will start on one node.

Verify that SQL Server is started.

sudo pcs status
The following examples shows the results when Pacemaker has successfully started a clustered instance of SQL Server.

virtualip     (ocf::heartbeat:IPaddr2):      Started sqlfcivm1
mssqlha  (ocf::sql:fci): Started sqlfcivm2

PCSD Status:
 slqfcivm1: Online
 sqlfcivm2: Online

Daemon Status:
 corosync: active/disabled
 pacemaker: active/enabled
 pcsd: active/enabled

How to Operate Shared Disk Cluster for SQL Server on Linux

This part of the article describes how to do the following tasks for SQL Server on a shared disk failover cluster with Red Hat Enterprise Linux 7.2.

  • Manually failover the cluster
  • Monitor a failover cluster SQL Server service
  • Add a cluster node
  • Remove a cluster node
  • Change the SQL Server resource monitoring frequency

Failover cluster manually

The resource move command creates a constraint forcing the resource to start on the target node. After executing the move command, executing resource clear will remove the constraint so it is possible to move the resource again or have the resource automatically fail over.

sudo pcs resource move    
sudo pcs resource clear 
The following example moves the mssqlha resource to a node named sqlfcivm2, and then removes the constraint so that the resource can move to a different node later.

sudo pcs resource move mssqlha sqlfcivm2 
sudo pcs resource clear mssqlha

Monitor a failover cluster SQL Server service

View the current cluster status:

sudo crm_mon
View the resource agent logs at /var/log/cluster/corosync.log

Add a node to a cluster

Check the IP address for each node. The following script shows the IP address of your current node.

ip addr show
The new node needs a unique name that is 15 characters or less. By default in Red Hat Linux the computer name is localhost.localdomain. This default name may not be unique and is too long. Set the computer name the new node. Set the computer name by adding it to /etc/hosts. The following script lets you edit /etc/hosts with vi.

sudo vi /etc/hosts
The following example shows /etc/hosts with additions for three nodes named sqlfcivm1sqlfcivm2, andsqlfcivm3.

127.0.0.1   localhost localhost4 localhost4.localdomain4
::1         localhost localhost6 localhost6.localdomain6
10.128.18.128 fcivm1
10.128.16.77 fcivm2
10.128.14.26 fcivm3
The file should be the same on every node.

Stop the SQL Server service on the new node.


Follow the instructions to mount the database file directory to the shared location:

From the NFS server, install nfs-utils

sudo yum -y install nfs-utils
Open up the firewall on clients and NFS server

sudo firewall-cmd --permanent --add-service=nfs
sudo firewall-cmd --permanent --add-service=mountd
sudo firewall-cmd --permanent --add-service=rpc-bind
sudo firewall-cmd --reload
Edit /etc/fstab file to include the mount command:

:  nfs timeo=14,intr
Run mount -a for the changes to take effect.

On the new node, create a file to store the SQL Server username and password for the Pacemaker login. The following command creates and populates this file:

sudo touch /var/opt/mssql/passwd
sudo echo "" >> /var/opt/mssql/secrets/passwd
sudo echo "" >> /var/opt/mssql/secrets/passwd
sudo chown root:root /var/opt/mssql/passwd
sudo chmod 600 /var/opt/mssql/passwd
On the new node, open the Pacemaker firewall ports. To open these ports with firewalld, run the following command:

sudo firewall-cmd --permanent --add-service=high-availability
sudo firewall-cmd --reload
Note
If you’re using another firewall that doesn’t have a built-in high-availability configuration, the following ports need to be opened for Pacemaker to be able to communicate with other nodes in the cluster
  • TCP: Ports 2224, 3121, 21064
  • UDP: Port 5405
Install Pacemaker packages on the new node.

sudo yum install pacemaker pcs fence-agents-all resource-agents
Set the password for for the default user that is created when installing Pacemaker and Corosync packages. Use the same password as the existing nodes.

sudo passwd hacluster
Enable and start pcsd service and Pacemaker. This will allow the new node to rejoin the cluster after the reboot. Run the following command on the new node.

sudo systemctl enable pcsd
sudo systemctl start pcsd
sudo systemctl enable pacemaker
Install the FCI resource agent for SQL Server. Run the following commands on the new node.

sudo yum install mssql-server-ha
On an existing node from the cluster, authenticate the new node and add it to the cluster:

sudo pcs    cluster auth  -u hacluster 
sudo pcs    cluster node add 
The following example ads a node named vm3 to the cluster.

  1. sudo pcs    cluster auth  
    sudo pcs    cluster start
    

Remove nodes from a cluster

To remove a node from a cluster run the following command:

sudo pcs    cluster node remove 

Change the frequency of sqlservr resource monitoring interval

sudo pcs    resource op monitor interval=s 
The following example sets the monitoring interval to 2 seconds for the mssql resource:

sudo pcs    resource op monitor interval=2s mssqlha

Troubleshoot Linux Shared Disk Cluster for SQL Server

In troubleshooting the cluster it may help to understand how the three daemons work together to manage cluster resources.

DaemonDescription
CorosyncProvides quorum membership and messaging between cluster nodes.
PacemakerResides on top of Corosync and provides state machines for resources.
PCSDManages both Pacemaker and Corosync through the pcs tools
PCSD must be running in order to use pcs tools.

Current cluster status

sudo pcs status returns basic information about the cluster, quorum, nodes, resources, and daemon status for each node.

An example of a healthy pacemaker quorum output would be:

Cluster name: MyAppSQL 
Last updated: Wed Oct 31 12:00:00 2016  Last change: Wed Oct 31 11:00:00 2016 by root via crm_resource on sqlvmnode1 
Stack: corosync 
Current DC: sqlvmnode1  (version 1.1.13-10.el7_2.4-44eb2dd) - partition with quorum 
3 nodes and 1 resource configured 

Online: [ sqlvmnode1 sqlvmnode2 sqlvmnode3] 

Full list of resources: 

mssql (ocf::sql:fci): Started sqlvmnode1 

PCSD Status: 
sqlvmnode1: Online 
sqlvmnode2: Online 
sqlvmnode3: Online 

Daemon Status: 
corosync: active/disabled 
pacemaker: active/enabled
In the example, partition with quorum means that a majority quorum of nodes is online. If the cluster loses a majority quorum of nodes , pcs status will return partition WITHOUT quorum and all resources will be stopped.

online: [sqlvmnode1 sqlvmnode2 sqlvmnode3] returns the name of all nodes currently participating in the cluster. If any nodes are not participating, pcs status returns OFFLINE: [].

PCSD Status shows the cluster status for each node.

Reasons why a node may be offline

Check the following items when a node is offline.

  • Firewall
    The following ports need to be open on all nodes for Pacemaker to be able to communicate. **TCP: 2224, 3121, 21064
  • Pacemaker or Corosync services running
  • Node communication
  • Node name mappings




Post a Comment

 
TECH SUPPORT © 2012 - Designed by INFOSBIRD