A couple of months ago, I did my first installation of SQL Server on Linux. I wrote about it in this blog post. So now it’s time to start the new year by talking about high availability on Linux with SQL Server vNext. Running standalone instances will be suitable for scenarios but I guess introducing high-availability with critical environments still remain mandatory.Currently, the CTP1 supports installing a SQL Server Failover Cluster Instance on the top of RHEL HA Add-on based on Pacemaker. This is a good start although I hope to see also availability groups in the future (maybe a future CTP version). In this blog post I will not go into details of my installation process because some steps will certainly change over the time but I would like to share my notes (or feelings) about this new way to achieve high availability with SQL Server on Linux world.
I performed the installation by using the same infrastructure exposed on the Microsoft documentation. As usual, my environment is fully virtualized with Hyper-V.
So, the first step consisted in installing 3 virtual machines based on a CentOS 7 distribution (the Microsoft documentation is intended to RHEL 7 but CentOS 7 is perfectly suitable in our case). The first two ones concerned the cluster and SQL Server. I performed the same installation process to install SQL Server on Linux. No change here. The third one concerned the NFS server. So let’s show my storage configuration:
[[email protected] ~]$ sudo fdisk -l | grep -i sd Disk /dev/sda: 21.5 GB, 21474836480 bytes, 41943040 sectors /dev/sda1 * 2048 2099199 1048576 83 Linux /dev/sda2 2099200 41943039 19921920 8e Linux LVM Disk /dev/sdb: 10.7 GB, 10737418240 bytes, 20971520 sectors /dev/sdb1 2048 20971519 10484736 83 Linux Disk /dev/sdc: 10.7 GB, 10737418240 bytes, 20971520 sectors /dev/sdc1 2048 20971519 10484736 83 Linux
The corresponding partition /dev/sdb1 (ext4 formatted) need to be mounted automatically by the system and will be used as a shared storage by the NFS server afterwards.
[[email protected] ~]$ cat /etc/fstab # # /etc/fstab # Created by anaconda on Thu Jan 12 21:46:34 2017 # … /dev/mapper/cl-root / xfs defaults 0 0 UUID=e4f5fc0b-1fd4-4e18-b655-a76b87778b73 /boot xfs defaults 0 0 /dev/mapper/cl-swap swap swap defaults 0 0 /dev/sdb1 /mnt/sql_data_nfs ext4 auto,user,rw 0 0 /dev/sdc1 /mnt/sql_log_nfs ext4 auto,user,rw 0 0
Then my NFS server will expose the shared directory /mnt/sql_data_nfs to the cluster layer.
[[email protected] ~]$ cat /etc/exports /mnt/sql_data_nfs 192.168.5.0/24(rw,sync,no_subtree_check,no_root_squash) /mnt/sql_log_nfs 192.168.5.0/24(rw,sync,no_subtree_check,no_root_squash)
We will focus only on the directory /mnt/sql_data_nfs in this case.
[[email protected] ~]$ showmount -e Export list for nfs.dbi-services.com: /mnt/sql_log_nfs 192.168.5.0/24 /mnt/sql_data_nfs 192.168.5.0/24
That’s it. My directory is ready to be used by my SQL Server cluster nodes as shared storage for my databases. Let’s continue with the second step. We need to install the cluster underlying infrastructure which includes components as Pacemaker (the resource manager) and Corosync (Communication layer between cluster nodes).
From Microsoft documentation
By reading the Pacemaker documentation on the web, I could find out similar concepts we may have with Microsoft and the Windows Failover Cluster feature. After facing some configuration issues, here my final configuration.
- Two cluster nodes (linux01.dbi-services.test / linux02.dbi-services.test)
- Two resources that concern my SQL Server FCI (sqllinuxfci resource + virtualip resource)
[[email protected] ~]$ sudo pcs status Cluster name: linux_cluster Stack: corosync Current DC: linux01.dbi-services.test (version 1.1.15-11.el7_3.2-e174ec8) - partition with quorum Last updated: Sat Jan 14 19:53:55 2017 Last change: Sat Jan 14 17:28:36 2017 by root via crm_resource on linux01.dbi-services.test 2 nodes and 2 resources configured Online: [ linux01.dbi-services.test linux02.dbi-services.test ] Full list of resources: sqllinuxfci (ocf::sql:fci): Started linux02.dbi-services.test virtualip (ocf::heartbeat:IPaddr2): Started linux02.dbi-services.test ... Daemon Status: corosync: active/enabled pacemaker: active/enabled pcsd: active/enabled
As said previously, I faced some issues during the cluster installation process. The first one concerned a typo in the Microsoft documentation (at least the command did not work in my case). I was not able to create my SQL Server resource after installing the mssql-server-ha package. Indeed, according to Microsoft documentation we need to create a SQL Server resource based on the ocf:mssql:fci resource agent. However, after some investigations, I was able to figure out that this definition doesn’t exist according to the current OCF resource agent folder hierarchy (see below). In my case, I had to change the definition by ocf:sql:fci
[[email protected] ~]$ ls -l /usr/lib/ocf/resource.d/ total 16 drwxr-xr-x. 2 root root 4096 Jan 12 19:31 heartbeat drwxr-xr-x. 2 root root 4096 Jan 12 19:31 openstack drwxr-xr-x. 2 root root 4096 Jan 12 19:31 pacemaker drwxr-xr-x. 2 root root 4096 Jan 14 10:55 sql [[email protected] ~]$ ls -l /usr/lib/ocf/resource.d/sql/ total 20 -rw-r--r--. 1 root root 3473 Jan 14 10:58 cfg -rwxr-xr-x. 1 root root 15979 Dec 16 02:09 fci
Let’s say it was also a good opportunity to understand what an OCF resource agent is. First coming from the Microsoft world, I figured out quickly the OCF resource agents correspond in fact to what we call the Windows Failover Cluster DLL resources. In addition, according to linux documentation, I noticed similar concepts like functions and entry points that a Windows resource DLL is expected to implement. Very interesting!
The second issue concerned a strange behavior when the failover of my SQL Server resource occurred. I first noticed the following messages:
* sqllinuxfci_start_0 on linux02.dbi-services.test ‘unknown error’ (1): call=16, status=complete, exitreason=’SQL server crashed during startup.’,
last-rc-change=’Sat Jan 14 17:35:30 2017′, queued=0ms, exec=34325ms
Then I moved on the SQL Server error log to try to find out some clues about this issue (SQL Server error log is always your friend in this case)
[[email protected] sql]$ sudo cat /var/opt/mssql/log/errorlog 2017-01-14 14:38:55.50 spid5s Error: 17204, Severity: 16, State: 1. 2017-01-14 14:38:55.50 spid5s FCB::Open failed: Could not open file C:\var\opt\mssql\data\mastlog.ldf for file number 2. OS error: 2(The system cannot find the file specified.). 2017-01-14 14:38:55.50 spid5s Error: 5120, Severity: 16, State: 101. 2017-01-14 14:38:55.50 spid5s Unable to open the physical file "C:\var\opt\mssql\data\mastlog.ldf". Operating system error 2: "2(The system cannot find the file specified.)". …
That’s the point. My SQL Server engine was not able to open the master database because it can’t find the specified path. As an apart, you may notice the path used by SQL Server in the error message. A Windows fashion path which includes a drive letter! Well, very surprising but I’m sure it will be changed in the near future.
For the purpose of my tests, I had no choice to change the folder permission to 777 to expect my SQL Server instance starting well. One point to investigate of course because it will not meet the security policy rules in production environment.
In fact one important thing to keep in mind is that SQL Server on Linux uses a special mssql user.
[[email protected] ~]$ grep mssql /etc/passwd mssql:x:995:993::/var/opt/mssql:/bin/bash
Therefore I modified the permissions as follows:
[[email protected] ~]$ sudo chown mssql:mssql /var/opt/mssql/data [[email protected] ~]$ sudo chmod 640 /var/opt/mssql/data
The third one concerned IP and hostname resolution. I had to add my cluster IP and hostnames related information into the /etc/hosts file on each cluster node to get it to resolve correctly as follows:
[[email protected] sql]$ cat /etc/hosts 127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4 ::1 localhost localhost.localdomain localhost6 localhost6.localdomain6 192.168.5.17 linux01.dbi-services.test linux01 192.168.5.18 linux02.dbi-services.test linux02 192.168.5.19 sqllinuxfci.dbi-services.test sqllinuxfci
Finally, after fixing the previous issues, I performed some failover tests (move resources and bring offline a cluster node as well) that ran successfully.
- Move resource
[[email protected] sql]$ sudo pcs resource move sqllinuxfci linux01.dbi-services.test [[email protected] sql]$ sudo pcs status … 2 nodes and 2 resources configured Online: [ linux01.dbi-services.test linux02.dbi-services.test ] Full list of resources: sqllinuxfci (ocf::sql:fci): Started linux01.dbi-services.test virtualip (ocf::heartbeat:IPaddr2): Started linux01.dbi-services.test
- Simulate failure node LINUX02.dbi-services.test
[[email protected] ~]$ sudo pcs status Cluster name: linux_cluster Stack: corosync Current DC: linux01.dbi-services.test (version 1.1.15-11.el7_3.2-e174ec8) - part ition with quorum Last updated: Sun Jan 15 10:59:14 2017 Last change: Sun Jan 15 10:56:54 2017 by root via crm_resource on linux01.dbi-services.test 2 nodes and 2 resources configured Online: [ linux01.dbi-services.test ] OFFLINE: [ linux02.dbi-services.test ] Full list of resources: sqllinuxfci (ocf::sql:fci): Started linux01.dbi-services.test virtualip (ocf::heartbeat:IPaddr2): Started linux01.dbi-services.tes t Daemon Status: corosync: active/enabled pacemaker: active/enabled pcsd: active/enabled
Another “bug” I noticed is that the SERVERPROPERTY() function output does not show correctly the my FCI name. Probably a mistake which will be resolved in the next CTPs. Be patient …
I got some explanations from Mihaela Blendea (Senior Program Manager at Microsoft) and I also noticed updates from the Microsoft documentation. Currently, SQL Server is not as coupled as with WSFC on Windows. Therefore, the virtual network name is WSFC-specific and there is no equivalent of the same in Pacemaker. This is why I noticed this behavior above.
My final thoughts
Here my feeling after playing a little bit with my new infrastructure.
Based on this first installation, we must face the facts: building a SQL Server FCI infrastructure is a fastest process on Linux in comparison to the same in Windows but I prefer to be prudent and not to draw hasty conclusions. Let’s see what we are going to have in the RTM release. One important thing I noticed for example is there is no explicit cluster validation compared to Windows at least in appearance. In fact, if we take a closer look at the cluster side, we already have some validation steps during the cluster creation (node authentication and cluster setup). However, I didn’t see any validation step at the SQL Server side (compared to Windows) except basic verifications which include verifying the standalones instances are able to start and share the same storage.
Moreover, one another important point we may notice is that we don’t need to setup DNS servers to run the cluster infrastructure. During my tests, I didn’t use it (hostname resolution was made only from /etc/hosts file) but as soon as I had to connect my infrastructure from remote computers, DNS resolution became almost mandatory 🙂
Finally, there is a plenty of tests to perform to understand how behave the cluster layer as well as the cluster resource.
Well, there is still a way to go in order to complete all my work on Linux. To be continued ….
By David Barbarin