1

I have installed mssql-server and mssql-server-ha on Ubuntu Server 16.04 LTS. I'm using drbd on two nodes, with pacemaker and corosync attempting to control the automatic failover between the two nodes. crm status shows 2 errors:

Failed Actions:
* res_mssql_monitor_5000 on hostname2 'invalid parameter' (2): call=57, status=complete, exitreason='2017/11/09 12:33:01 Expected local server name to be res_mssql but it was hostname1',
last-rc-change='Thu Nov  9 12:33:01 2017', queued=0ms, exec=5241ms
* res_mssql_start_0 on hostname2 'unknown error' (1): call=6086, status=complete, exitreason='SQL Server crashed during startup.',
last-rc-change='Thu Nov  9 12:32:39 2017', queued=0ms, exec=24329ms

(actual host names replaced with "hostname1 and hostname2")

TL;DR If someone has successfully configured a two-node pacemaker/corosync/drbd SQL Server 2017 on Linux setup with a floating IP, I'm interested to find out what I'm doing wrong. If you require additional config or log files please let me know.


I don't know where it's finding the actual hostname1 vs rs_mssql as the expected hostname. The above error is on hostname2, so I think it might have been when I copied a config file over from hostname1 to hostname2 during initial setup.

My crm configuration:

(Note: I haven't attacked the IPaddr2 problem yet; I have ens160 and ens192 for my regular IP addresses, and I want to later configure an IP alias as ip_mssql for the public IP access to the SQL server)

node 1: hostname1 \
   attributes
node 2: hostname2 \
   attributes
primitive ip_mssql IPaddr2 \
   params ip=(virt IP addr) iflabel=ip_mssql \ #I think iflabel is wrong
   op monitor interval=5s nic=ip_mssql \
   meta target-role=Stopped
primitive res_drbd_mssql ocf:linbit:drbd \
   params drbd_resource=mssql \
   op start interval=0 timeout=240s \
   op stop interval=0 timeout=120s
primitive res_fs_mssqlData Filesystem \
   params device="/dev/drbd0" directory="/var/opt/mssql/data" fstype=xfs \
   op start interval=0 timeout=60s \
   op stop interval=0 timeout=120s
primitive res_fs_mssqlLog Filesystem \
   params device="/dev/drbd1" directory="/var/opt/mssql/log" fstype=xfs \
   op start interval=0 timeout=60s \
   op stop interval=0 timeout=120s
primitive res_fs_mssqlTempDB Filesystem \
   params device="/dev/drbd2" directory="/var/opt/mssql/tempDB" fstype=xfs \
   op start interval=0 timeout=60s \
   op stop interval=0 timeout=120s
primitive res_mssql ocf:mssql:fci \
   op monitor interval=5s timeout=30s \
   op start interval=0 timeout=60s \
   op stop interval=0 timeout=60s
group mssqlserver res_fs_mssqlData res_fs_mssqlLog res_fs_mssqlTempDB ip_mssql
ms ms_drbd_mssql res_drbd_mssql \
   meta notify=true master-max=1 master-node-max=1 clone-max=2 clone-node-max=1
colocation col_mssql_drbd inf: mssqlserver ms_drbd_mssql:Master
order ord_mssql inf: ms_drbd_mssql:promote mssqlserver:start
property cib-bootstrap-options: \
   have-watchdog=false \
   dc-version=1.1.14-70404b0 \
   cluster-infrastructure=corosync \
   cluster-name=mssqlserver \
   stonith-enabled=false \
   start-failure-is-fatal=false \
   last-lrm-refresh=1510177588 \
   startup-fencing=true \
   enable-startup-probes=true \
   symmetric-cluster=true \
   stop-orphan-actions=true \
   stonith-action=reboot \
   remove-after-stop=false \
   stop-all-resources=false \
   stop-orphan-resources=true \
   no-quorum-policy=ignore \
   is-managed-default=true

I can manually start mssql-server just fine:

sudo systemctl start mssql-server
sudo systemctl status mssql-server

mssql-server.service - Microsoft SQL Server Database Engine
  Loaded: loaded (/lib/systemd/system/mssql-server.service; enabled; vendor preset: enabled)
Active: active (running) since Thu 2017-11-09 12:49:21 CST; 1s ago
   Docs: https://docs.microsoft.com/en-us/sql/linux
Main PID: 3368 (sqlservr)
   Tasks: 62
Memory: 171.0M
   CPU: 1.770s
CGroup: /system.slice/mssql-server.service
       3368 /opt/mssql/bin/sqlservr
       3371 /opt/mssql/bin/sqlservr

Nov 09 12:49:21 hostname2 systemd[1]: Started Microsoft SQL Server Database Engine.

These are the only actual errors I found in /var/opt/mssql/log/errorlog:

2017-11-09 12:49:28.17 spid4s      Service Master Key could not be decrypted using one of its encryptions. See sys.key_encryptions for details.
2017-11-09 12:49:28.17 spid4s      An error occurred during Service Master Key initialization. SQLErrorCode=33095, State=8, LastOsError=0.
2017-11-09 12:49:31.14 spid22s     The Service Broker endpoint is in disabled or stopped state.
2017-11-09 12:49:31.14 spid22s     The Database Mirroring endpoint is in disabled or stopped state.
2017-11-09 12:49:31.17 spid22s     Service Broker manager has started.
2017-11-09 12:49:31.37 spid4s      Recovery is complete. This is an informational message only. No user action is required.

Manual drbd failover works by umount /dev/drbd0 /dev/drbd1 /dev/drbd2 and drbdadm secondary mssql, then reversing that process on the new primary (drbdadm primary mssql and mount...).

My /etc/drbd.d/mssql.res conf (/etc/drbd.d/global_common.conf is unaltered from repositories):

resource mssql {
   handlers {
            split-brain "/usr/lib/drbd/notify-split-brain.sh root";
   }
   net {
            after-sb-0pri discard-least-changes;
            after-sb-1pri discard-secondary;
            after-sb-2pri disconnect;
   }
   volume 0 {
        device minor 0;
        disk /dev/VG-SqlData/LV-SqlData;
            meta-disk internal;
   }
   volume 1 {
       device minor 1;
       disk /dev/VG-SqlLogs/LV-SqlLogs;
            meta-disk internal;
   }
   volume 2 {
       device minor 2;
       disk /dev/VG-TempDB/LV-TempDB;
            meta-disk internal;
   }
   syncer {
            rate 35M;
            verify-alg md5;
   }
   on hostname1 {
            address <ip addr1>:7788;
   }
   on hostname2 {
            address <ip addr2>:7788;
   }
}
  • How are you starting MSSQL manually? – Matt Kereczman Nov 14 '17 at 17:28
  • I've updated above to include sudo systemctl start mssql-server – Ben M Nov 15 '17 at 14:51
1

Try using systemd to start the service instead: crm configure edit res_mssql

Edit the config so it reads like this:

primitive res_mssql systemd:mssql-server \
   op monitor interval=30s timeout=30s \
   op start interval=0 timeout=60s \
   op stop interval=0 timeout=60s

That should accomplish the same thing. However, I would imagine the resource-agent could take a few additional parameters, which might be all that's needed to get it working the way you had tried.

I would suggest checking the RA info to see if you can figure out what parameters you're missing: crm ra info ocf:mssql:fci

  • Oh, I also adjusted the monitor interval because 5s seems too tight – Matt Kereczman Nov 15 '17 at 15:56
  • Thanks for the crm ra information. At the end of the file it states: ` Operations' defaults (advisory minimum): start timeout=1000 stop timeout=20 monitor. timeout=30 interval=10` So I changed those. I dove deeper into /var/log/syslog | grep sql and found: Nov 15 14:21:40 hostname2 sqlservr[25694]: The SQL Server End-User License Agreement (EULA) must be accepted before SQL Server can start. ... Main process exited, code=exited, status=1/FAILURE So I think I have found my problem. Will post back here if not. Thanks again! – Ben M Nov 15 '17 at 21:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.