PacemakerでSQL Server on Linux 2017を管理してみる。。。

# 前文
歳のせいか、業界の動向に疎くなっているようで。。。いつの間にか、SQL Server on Linux 2017でした（涙）
 - https://www.microsoft.com/ja-jp/sql-server/sql-server-2017-linux
正式にMSのPacemaker用のRA(fci)も用意されているようです。
 - https://github.com/Microsoft/mssql-server-ha

今のバージョンがどの程度、安定性があるのか不明ですが、LinuxのDatabaseの選択子の一つになっていくかも知れません。

# 環境
 - RHEL7.4の単ノード
 - Pacemakerは2.0-rc3
 - SQL Server 2017

```
[root@test~]# rpm -qa | grep sql                                                                                                                                                                           
msodbcsql17-17.1.0.1-1.x86_64                                                                                                                                                                                    
mssql-server-ha-14.0.3025.34-3.x86_64                                                                                                                                                                            
mssql-tools-17.1.0.1-1.x86_64                                                                                                                                                                                    
mssql-server-14.0.3025.34-3.x86_64      
```

# 設定
「SQL Server on Linux 2017」のインストールや初期設定については、関連リンク先に詳しく書かれているので割愛します。
systemd管理と、fci管理の２つのパターンで、STONITH無で設定しています。単ノードで管理するRAは１つのみにしています。
実際には、クラスタ構成するのであれば、アクセス用のVIP(IPaddr2)や、DB領域用の共有（もしくは、DRBDなどの同期可能なディスク）が必要ですが、省いています。

# 動作確認
## systemd管理
 - Pacemakerを起動してcrmファイルを流し込みます。

```
property no-quorum-policy="ignore" \
        stonith-enabled="false" \
        startup-fencing="false" \

rsc_defaults resource-stickiness="INFINITY" \
        migration-threshold="1"
#
primitive prmMssqlServer systemd:mssql-server \
        op start interval="0s" timeout="60s" on-fail="restart" \
        op monitor interval="10s" timeout="60s" on-fail="restart" \
        op stop interval="0s" timeout="60s" on-fail="block"
```

 - crm_monの様子

```
[root@rh74-test ~]# crm_mon -1 -Af                                                                                                                                                                               
Stack: corosync
Current DC: rh74-test (version 2.0.0-2c83c6872a) - partition WITHOUT quorum
Last updated: Tue May 15 15:26:25 2018
Last change: Tue May 15 15:26:19 2018 by root via cibadmin on rh74-test

1 node configured
1 resource configured

Online: [ rh74-test ]

Active resources:

 prmMssqlServer (systemd:mssql-server): Started rh74-test

Node Attributes:
* Node rh74-test:

Migration Summary:
* Node rh74-test:
```
 - psとstatusで確認してみます

```
[root@rh74-test ~]# ps -ef | grep mssql                                                                                                                                                                          
mssql     5853     1  0 15:26 ?        00:00:01 /opt/mssql/bin/sqlservr                                                                                                                                          
mssql     5855  5853  9 15:26 ?        00:00:10 /opt/mssql/bin/sqlservr                                                                                                                                          
root      6083 25955  0 15:28 pts/0    00:00:00 grep --color=auto mssql                                                                                                                                          
[root@rh74-test ~]# systemctl status mssql-server.service 
● mssql-server.service - Cluster Controlled mssql-server
   Loaded: loaded (/usr/lib/systemd/system/mssql-server.service; enabled; vendor preset: disabled)
  Drop-In: /run/systemd/system/mssql-server.service.d
           └─50-pacemaker.conf
   Active: active (running) since 火 2018-05-15 15:26:19 JST; 2min 9s ago
     Docs: https://docs.microsoft.com/en-us/sql/linux
 Main PID: 5853 (sqlservr)
   Memory: 693.7M
   CGroup: /system.slice/mssql-server.service
           ├─5853 /opt/mssql/bin/sqlservr
           └─5855 /opt/mssql/bin/sqlservr

 5月 15 15:26:28 rh74-test sqlservr[5853]: 2018-05-15 15:26:28.41 Server      2018-05-15 15:26:28.42 Server      Server is listening on [ ::1 <ipv6> 1434].
 5月 15 15:26:28 rh74-test sqlservr[5853]: 2018-05-15 15:26:28.42 Server      2018-05-15 15:26:28.42 Server      Server is listening on [ 127.0.0.1 <ipv4> 1434].
 5月 15 15:26:28 rh74-test sqlservr[5853]: 2018-05-15 15:26:28.42 Server      2018-05-15 15:26:28.42 Server      Dedicated admin connection support was established for listening locally on port 1434.
 5月 15 15:26:28 rh74-test sqlservr[5853]: 2018-05-15 15:26:28.44 spid19s     SQL Server 2018-05-15 15:26:28.44 spid19s     SQL Server is now ready for client connections. This is an informat...n is required.
 5月 15 15:26:28 rh74-test sqlservr[5853]: 2018-05-15 15:26:28.98 spid9s      2018-05-15 15:26:28.98 spid9s      Starting up database 'tempdb'.
 5月 15 15:26:29 rh74-test sqlservr[5853]: 2018-05-15 15:26:29.26 spid9s      tempdb 2018-05-15 15:26:29.26 spid9s      The tempdb database has 1 data file(s).
 5月 15 15:26:29 rh74-test sqlservr[5853]: 2018-05-15 15:26:29.27 spid24s     Service Broker 2018-05-15 15:26:29.27 spid24s     The Service Broker endpoint is in disabled or stopped state.
 5月 15 15:26:29 rh74-test sqlservr[5853]: 2018-05-15 15:26:29.27 spid24s     Database Mirroring 2018-05-15 15:26:29.27 spid24s     The Database Mirroring endpoint is in disabled or stopped state.
 5月 15 15:26:29 rh74-test sqlservr[5853]: 2018-05-15 15:26:29.29 spid24s     Service Broker 2018-05-15 15:26:29.29 spid24s     Service Broker manager has started.
 5月 15 15:26:29 rh74-test sqlservr[5853]: 2018-05-15 15:26:29.45 spid5s      2018-05-15 15:26:29.45 spid5s      Recovery is complete. This is an informational message only. No user action is required.
Hint: Some lines were ellipsized, use -l to show in full.
```
 - mssqlプロセスに疑似故障を起こしてみます。故障検知し単ノードの為、停止します。

```
[root@rh74-test opt]# ps -ef |grep mssql                                                                                                                                                                         
mssql     5853     1  0 15:26 ?        00:00:01 /opt/mssql/bin/sqlservr                                                                                                                                          
mssql     5855  5853  6 15:26 ?        00:00:12 /opt/mssql/bin/sqlservr                                                                                                                                          
root      6106 26259  0 15:29 pts/1    00:00:00 grep --color=auto mssql                                                                                                                                          
[root@rh74-test opt]# kill -9 5853
[root@rh74-test opt]# ps -ef |grep mssql
root      6109 26259  0 15:29 pts/1    00:00:00 grep --color=auto mssql   

[root@rh74-test ~]# crm_mon -1 -Af                                                                                                                                                                               
Stack: corosync
Current DC: rh74-test (version 2.0.0-2c83c6872a) - partition WITHOUT quorum
Last updated: Tue May 15 15:30:03 2018
Last change: Tue May 15 15:26:19 2018 by root via cibadmin on rh74-test

1 node configured
1 resource configured

Online: [ rh74-test ]

No active resources


Node Attributes:
* Node rh74-test:

Migration Summary:
* Node rh74-test:
   prmMssqlServer: migration-threshold=1 fail-count=1 last-failure='Tue May 15 15:29:52 2018'

Failed Actions:
* prmMssqlServer_monitor_10000 on rh74-test 'not running' (7): call=7, status=complete, exitreason='',
    last-rc-change='Tue May 15 15:29:52 2018', queued=0ms, exec=0ms

[root@rh74-test ~]# systemctl status mssql-server.service 
● mssql-server.service - Microsoft SQL Server Database Engine
   Loaded: loaded (/usr/lib/systemd/system/mssql-server.service; enabled; vendor preset: disabled)
   Active: failed (Result: signal) since 火 2018-05-15 15:29:44 JST; 1min 1s ago
     Docs: https://docs.microsoft.com/en-us/sql/linux
 Main PID: 5853 (code=killed, signal=KILL)

 5月 15 15:26:28 rh74-test sqlservr[5853]: 2018-05-15 15:26:28.44 spid19s     SQL Server 2018-05-15 15:26:28.44 spid19s     SQL Server is now ready for client connections. This is an informat...n is required.
 5月 15 15:26:28 rh74-test sqlservr[5853]: 2018-05-15 15:26:28.98 spid9s      2018-05-15 15:26:28.98 spid9s      Starting up database 'tempdb'.
 5月 15 15:26:29 rh74-test sqlservr[5853]: 2018-05-15 15:26:29.26 spid9s      tempdb 2018-05-15 15:26:29.26 spid9s      The tempdb database has 1 data file(s).
 5月 15 15:26:29 rh74-test sqlservr[5853]: 2018-05-15 15:26:29.27 spid24s     Service Broker 2018-05-15 15:26:29.27 spid24s     The Service Broker endpoint is in disabled or stopped state.
 5月 15 15:26:29 rh74-test sqlservr[5853]: 2018-05-15 15:26:29.27 spid24s     Database Mirroring 2018-05-15 15:26:29.27 spid24s     The Database Mirroring endpoint is in disabled or stopped state.
 5月 15 15:26:29 rh74-test sqlservr[5853]: 2018-05-15 15:26:29.29 spid24s     Service Broker 2018-05-15 15:26:29.29 spid24s     Service Broker manager has started.
 5月 15 15:26:29 rh74-test sqlservr[5853]: 2018-05-15 15:26:29.45 spid5s      2018-05-15 15:26:29.45 spid5s      Recovery is complete. This is an informational message only. No user action is required.
 5月 15 15:29:44 rh74-test systemd[1]: mssql-server.service: main process exited, code=killed, status=9/KILL
 5月 15 15:29:44 rh74-test systemd[1]: Unit mssql-server.service entered failed state.
 5月 15 15:29:44 rh74-test systemd[1]: mssql-server.service failed.
Hint: Some lines were ellipsized, use -l to show in full.

```

## fci管理
 - Pacemakerを起動してcrmファイルを流し込みます。

```
property no-quorum-policy="ignore" \
        stonith-enabled="false" \
        startup-fencing="false" \

rsc_defaults resource-stickiness="INFINITY" \
        migration-threshold="1"
#
primitive rh74-01 ocf:mssql:fci \
        op start interval="0s" timeout="60s" on-fail="restart" \
        op monitor interval="10s" timeout="60s" on-fail="restart" \
        op stop interval="0s" timeout="60s" on-fail="block"
```

 - crm_monの様子

```
[root@rh74-test ~]# crm_mon -1 -Af
Stack: corosync
Current DC: rh74-test (version 2.0.0-2c83c6872a) - partition WITHOUT quorum
Last updated: Tue May 15 15:39:50 2018
Last change: Tue May 15 15:39:31 2018 by root via cibadmin on rh74-test

1 node configured
1 resource configured

Online: [ rh74-test ]

Active resources:

 rh74-01        (ocf::mssql:fci):       Started rh74-test

Node Attributes:
* Node rh74-test:

Migration Summary:
* Node rh74-test:
```

 - psとstatusで確認してみます。systemd管理ではないので、systemdのstatusでは確認されません。

```
[root@rh74-test ~]# ps -ef |grep mssql                                                                                                                                                                           
mssql     9475     1  3 15:44 ?        00:00:00 /opt/mssql/bin/sqlservr                                                                                                                                          
mssql     9486  9475 73 15:44 ?        00:00:09 /opt/mssql/bin/sqlservr                                                                                                                                          
root      9730  9353  0 15:44 ?        00:00:00 /bin/bash /usr/lib/ocf/resource.d/mssql/fci monitor                                                                                                              
root      9757  9730  0 15:44 ?        00:00:00 /bin/bash /usr/lib/ocf/resource.d/mssql/fci monitor                                                                                                              
root      9758  9757  4 15:44 ?        00:00:00 /usr/lib/ocf/lib/mssql/fci-helper --port 1433 --credentials-file /var/opt/mssql/secrets/passwd --application-name monitor-rh74-01 --connection-timeout 20 --health-threshold 3 --action monitor --virtual-server-name rh74-01
root      9759  9757  0 15:44 ?        00:00:00 /bin/bash /usr/lib/ocf/resource.d/mssql/fci monitor                                                                                                              
root      9785 25955  0 15:44 pts/0    00:00:00 grep --color=auto mssql      


[root@rh74-test ~]# systemctl status mssql-server.service 
● mssql-server.service - Microsoft SQL Server Database Engine
   Loaded: loaded (/usr/lib/systemd/system/mssql-server.service; enabled; vendor preset: disabled)
   Active: inactive (dead) since 火 2018-05-15 15:43:10 JST; 1min 20s ago
     Docs: https://docs.microsoft.com/en-us/sql/linux
  Process: 9110 ExecStart=/opt/mssql/bin/sqlservr (code=exited, status=0/SUCCESS)
 Main PID: 9110 (code=exited, status=0/SUCCESS)

 5月 15 15:43:00 rh74-test sqlservr[9110]: 2018-05-15 15:43:00.21 spid24s     Service Broker 2018-05-15 15:43:00.21 spid24s     The Service Broker endpoint is in disabled or stopped state.
 5月 15 15:43:00 rh74-test sqlservr[9110]: 2018-05-15 15:43:00.21 spid24s     Database Mirroring 2018-05-15 15:43:00.22 spid24s     The Database Mirroring endpoint is in disabled or stopped state.
 5月 15 15:43:00 rh74-test sqlservr[9110]: 2018-05-15 15:43:00.23 spid24s     Service Broker 2018-05-15 15:43:00.23 spid24s     Service Broker manager has started.
 5月 15 15:43:00 rh74-test sqlservr[9110]: 2018-05-15 15:43:00.28 spid6s      2018-05-15 15:43:00.28 spid6s      Recovery is complete. This is an informational message only. No user action is required.
 5月 15 15:43:00 rh74-test systemd[1]: Stopping Microsoft SQL Server Database Engine...
 5月 15 15:43:00 rh74-test sqlservr[9110]: 2018-05-15 15:43:00.47 spid6s      Always On: SQL Server is shutting down 2018-05-15 15:43:00.48 spid6s      Always On: The availability replica man...n is required.
 5月 15 15:43:00 rh74-test sqlservr[9110]: 2018-05-15 15:43:00.48 spid6s      2018-05-15 15:43:00.48 spid6s      SQL Server is terminating in response to a 'stop' request from Service Control...n is required.
 5月 15 15:43:00 rh74-test sqlservr[9110]: 2018-05-15 15:43:00.59 spid24s     Service Broker 2018-05-15 15:43:00.59 spid24s     Service Broker manager has shut down.
 5月 15 15:43:10 rh74-test sqlservr[9110]: 2018-05-15 15:43:10.30 spid6s      2018-05-15 15:43:10.30 spid6s      SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an infor...n is required.
 5月 15 15:43:10 rh74-test systemd[1]: Stopped Microsoft SQL Server Database Engine.
Hint: Some lines were ellipsized, use -l to show in full.
               
```

 - mssqlプロセスに疑似故障を起こしてみます。故障検知し単ノードの為、停止します。

```
[root@rh74-test ~]# ps -ef |grep mssql
mssql     9475     1  1 15:44 ?        00:00:00 /opt/mssql/bin/sqlservr                                                                                                                                          
mssql     9486  9475 25 15:44 ?        00:00:10 /opt/mssql/bin/sqlservr                                                                                                                                          
root      9876 25955  0 15:44 pts/0    00:00:00 grep --color=auto mssql                                                                                                                                          
[root@rh74-test ~]# kill -9 9475
[root@rh74-test ~]# ps -ef |grep mssql                                                                                                                                                                           
root      9995 25955  0 15:44 pts/0    00:00:00 grep --color=auto mssql          

[root@rh74-test ~]# crm_mon -1 -Af
Stack: corosync
Current DC: rh74-test (version 2.0.0-2c83c6872a) - partition WITHOUT quorum
Last updated: Tue May 15 15:45:05 2018
Last change: Tue May 15 15:44:01 2018 by root via cibadmin on rh74-test

1 node configured
1 resource configured

Online: [ rh74-test ]

No active resources


Node Attributes:
* Node rh74-test:

Migration Summary:
* Node rh74-test:
   rh74-01: migration-threshold=1 fail-count=1 last-failure='Tue May 15 15:44:48 2018'

Failed Actions:
* rh74-01_monitor_10000 on rh74-test 'unknown error' (1): call=7, status=complete, exitreason='2018/05/15 15:44:48 Unexpected error: Unable to open tcp connection with host 'localhost:1433': dial tcp 127.0.0.1:1433: getsock',
    last-rc-change='Tue May 15 15:44:48 2018', queued=0ms, exec=0ms

```

# 気になる点
 - systemd管理時に疑似故障時にcrm_monのexitreasonが何も入っていないので、何かエラー文字列を埋めた方が良いと思われます。(もしかしたら、systemdリソースの場合は、exitreasonは設定出来ないのかも??)
 - fci管理の場合、fciリソースの名称を変更する必要があるようです。(詳細をみていませんが、設定などで回避する方法があるのかも知れません。。。）

```
[root@rh74-test ~]# crm_mon -1 -Af
Stack: corosync
Current DC: rh74-test (version 2.0.0-2c83c6872a) - partition WITHOUT quorum
Last updated: Tue May 15 15:35:54 2018
Last change: Tue May 15 15:35:39 2018 by root via cibadmin on rh74-test

1 node configured
1 resource configured

Online: [ rh74-test ]

Active resources:

 prmMssqlServer (ocf::mssql:fci):       FAILED rh74-test

Node Attributes:
* Node rh74-test:

Migration Summary:
* Node rh74-test:
   prmMssqlServer: migration-threshold=1 fail-count=1000000 last-failure='Tue May 15 15:35:53 2018'

Failed Actions:
* prmMssqlServer_start_0 on rh74-test 'invalid parameter' (2): call=6, status=complete, exitreason='2018/05/15 15:35:53 Expected local server name to be prmMssqlServer but it was rh74-test',
    last-rc-change='Tue May 15 15:35:40 2018', queued=0ms, exec=12836ms

(fci-helperの該当エラー箇所)
(snip)
// Function: monitor
//
// Description:
//    Implements the OCF "monitor" action
//
func monitor(db *sql.DB, virtualServerName string, stdout *log.Logger) (mssqlcommon.OcfExitCode, error) {
        stdout.Println("Querying local server name...")
        currentServerName, err := mssqlcommon.GetLocalServerName(db)
        if err != nil {
                return mssqlcommon.OCF_ERR_GENERIC, fmt.Errorf("Could not query local server name: %s", err)
        }
        stdout.Printf("Local server name is %s\n", currentServerName)
        if !strings.EqualFold(currentServerName, virtualServerName) {
                return mssqlcommon.OCF_ERR_ARGS, fmt.Errorf("Expected local server name to be %s but it was %s", virtualServerName, currentServerName)
        }

        return mssqlcommon.OCF_SUCCESS, nil
}
(snip)
fciのRA内の$OCF_RESOURCE_INSTANCEを渡している模様。。。

```

 - fci管理で起動するとmonitorの都度、ログが大量に出て、かなり鬱陶しい感じです。実際にPacemakerでクラスタする場合には、改善(ログをdebugなどに落とす)が必要でしょう。

```
(snip)
May 15 15:06:44 rh74-test fci(rh74-test)[4149]: INFO: mssql_validate
May 15 15:06:44 rh74-test fci(rh74-test)[4149]: INFO: Resource agent invoked with: monitor
May 15 15:06:44 rh74-test fci(rh74-test)[4149]: INFO: mssql_monitor
May 15 15:06:44 rh74-test fci(rh74-test)[4149]: INFO: monitor: 2018/05/15 15:06:44 fci-helper invoked with hostname [localhost]; port [1433]; credentials-file [/var/opt/mssql/secrets/passwd]; application-name [monitor-rh74-test]; connection-timeout [20]; health-threshold [3]; action [monitor]
May 15 15:06:44 rh74-test fci(rh74-test)[4149]: INFO: monitor: 2018/05/15 15:06:44 fci-helper invoked with virtual-server-name [rh74-test]
May 15 15:06:44 rh74-test fci(rh74-test)[4149]: INFO: monitor: 2018/05/15 15:06:44 Attempt 1 to connect to the instance at localhost:1433 and run sp_server_diagnostics
May 15 15:06:45 rh74-test fci(rh74-test)[4149]: INFO: monitor: 2018/05/15 15:06:45 Connected to the instance at localhost:1433
May 15 15:06:50 rh74-test fci(rh74-test)[4149]: INFO: monitor: 2018/05/15 15:06:50 Querying local server name...
May 15 15:06:50 rh74-test fci(rh74-test)[4149]: INFO: monitor: 2018/05/15 15:06:50 Local server name is rh74-test
May 15 15:06:50 rh74-test fci(rh74-test)[4149]: INFO: rh74-test monitor : 0
May 15 15:07:00 rh74-test fci(rh74-test)[4250]: INFO: mssql_validate
May 15 15:07:00 rh74-test fci(rh74-test)[4250]: INFO: Resource agent invoked with: monitor
May 15 15:07:00 rh74-test fci(rh74-test)[4250]: INFO: mssql_monitor
May 15 15:07:00 rh74-test fci(rh74-test)[4250]: INFO: monitor: 2018/05/15 15:07:00 fci-helper invoked with hostname [localhost]; port [1433]; credentials-file [/var/opt/mssql/secrets/passwd]; application-name [monitor-rh74-test]; connection-timeout [20]; health-threshold [3]; action [monitor]
May 15 15:07:00 rh74-test fci(rh74-test)[4250]: INFO: monitor: 2018/05/15 15:07:00 fci-helper invoked with virtual-server-name [rh74-test]
May 15 15:07:00 rh74-test fci(rh74-test)[4250]: INFO: monitor: 2018/05/15 15:07:00 Attempt 1 to connect to the instance at localhost:1433 and run sp_server_diagnostics
May 15 15:07:00 rh74-test fci(rh74-test)[4250]: INFO: monitor: 2018/05/15 15:07:00 Connected to the instance at localhost:1433
May 15 15:07:05 rh74-test fci(rh74-test)[4250]: INFO: monitor: 2018/05/15 15:07:05 Querying local server name...
May 15 15:07:05 rh74-test fci(rh74-test)[4250]: INFO: monitor: 2018/05/15 15:07:05 Local server name is rh74-test
May 15 15:07:05 rh74-test fci(rh74-test)[4250]: INFO: rh74-test monitor : 0
(snip)
```

# 関連リンク
以下に既にPacemakerで設定されている記載がありました。参考にさせて頂きました。
 - https://qiita.com/harukano/items/6488c15e0845402fafc3
 - https://www.server-world.info/query?os=CentOS_7&p=mssql2017&f=1