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) built on top of . 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 .
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
- Install and setup SQL Server on both nodes. For detailed instructions see .
- Designate one node as primary and the other as secondary, for purposes of configuration. Use these terms for the following this guide.
- On the secondary node, stop and disable SQL Server.
The following example stops and disables SQL Server:
sudo systemctl stop mssql-server sudo systemctl disable mssql-server
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:
nfs-utilssudo yum -y install nfs-utils
Enable and start
rpcbindsudo systemctl enable rpcbind && systemctl start rpcbind
Enable and start
nfs-serversystemctl 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 --reloadConfigure all cluster nodes to connect to the NFS shared storage
Do the following steps on all cluster nodes.
nfs-utilssudo 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
- On the primary node only, save the database files to a temporary location.
- On all cluster nodes edit
/etc/fstabfile 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-haCreate 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
masterdatabase 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/enabledHow 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.logAdd 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 sqlfcivm1, sqlfcivm2, 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.
Follow the instructions to mount the database file directory to the shared location:
From the NFS server, install
nfs-utilssudo 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.
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 mssqlhaTroubleshoot 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.
| Daemon | Description |
|---|---|
| Corosync | Provides quorum membership and messaging between cluster nodes. |
| Pacemaker | Resides on top of Corosync and provides state machines for resources. |
| PCSD | Manages 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.
- FirewallThe 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
No comments: