SQL Server 2017 : Failover Cluster Instance2017/10/15

Configure SQL Server Failover Cluster Instance.

This example is based on the environment below.
Configure SQL Server Failover Cluster Instance on ISCSI + CLVM (Clustered Logical Volume Manager) + GFS2 FileSystem.

                        +--------------------+
                        | [node03.srv.world] |
                        |    ISCSI Target    |
                        +---------+----------+
                         10.0.0.53|
                                  |
+----------------------+          |          +----------------------+
| [ node01.srv.world ] |10.0.0.51 | 10.0.0.52| [ node02.srv.world ] |
|         CLVM         +----------+----------+          CLVM        |
|      SQL Server      |          |          |       SQL Server     |
+----------------------+          |          +----------------------+
                             VIP:10.0.0.50

[1]	Configure Cluster, refer to here. Finish to configure to [7] section. It's unnecessary to proceed to [8] or later on the link.)。
[2]	Stop SQL Server on All Nodes and install SQL Server for HA package.

[root@node01 ~]#

systemctl stop mssql-server

[root@node01 ~]#

systemctl disable mssql-server

[root@node01 ~]#

yum -y install mssql-server-ha

[3]	Add storage to Cluster resource and copy SQL Server's Data on it.

[root@node01 ~]#

cp -pR /var/opt/mssql/data /var/opt/mssql/data.bk

[root@node01 ~]#

pcs resource create fs_gfs2 Filesystem \
device="/dev/vg_cluster/lv_cluster" directory="/var/opt/mssql/data" fstype="gfs2" \
options="noatime,nodiratime" op monitor interval=10s on-fail=fence clone interleave=true

[root@node01 ~]#

pcs resource show

 Clone Set: dlm-clone [dlm]
     Started: [ node01.srv.world node02.srv.world ]
 Clone Set: clvmd-clone [clvmd]
     Started: [ node01.srv.world node02.srv.world ]
 Clone Set: fs_gfs2-clone [fs_gfs2]
     Started: [ node01.srv.world node02.srv.world ]

[root@node01 ~]#

pcs constraint order start clvmd-clone then fs_gfs2-clone

Adding clvmd-clone fs_gfs2-clone (kind: Mandatory) (Options: first-action=start then-action=start)
[root@node01 ~]#

pcs constraint colocation add fs_gfs2-clone with clvmd-clone

[root@node01 ~]#

df -hT /var/opt/mssql/data

Filesystem                        Type  Size  Used Avail Use% Mounted on
/dev/mapper/vg_cluster-lv_cluster gfs2   30G  431M   30G   2% /var/opt/mssql/data

[root@node01 ~]#

cp -pR /var/opt/mssql/data.bk/* /var/opt/mssql/data/

[root@node01 ~]#

ll /var/opt/mssql/data

total 53504
-rw-r-----. 1 mssql mssql  4194304 Oct 16 15:44 master.mdf
-rw-r-----. 1 mssql mssql  2097152 Oct 16 15:44 mastlog.ldf
-rw-r-----. 1 mssql mssql  8388608 Oct 16 15:44 modellog.ldf
-rw-r-----. 1 mssql mssql  8388608 Oct 16 15:44 model.mdf
-rw-r-----. 1 mssql mssql 13959168 Oct 16 15:44 msdbdata.mdf
-rw-r-----. 1 mssql mssql   786432 Oct 16 15:44 msdblog.ldf
-rw-r-----. 1 mssql mssql  8388608 Oct 16 15:44 tempdb.mdf
-rw-r-----. 1 mssql mssql  8388608 Oct 16 15:44 templog.ldf

[root@node01 ~]#

rm -rf /var/opt/mssql/data.bk

[4]	Start SQL Server and add an account for Pacemaker. Do it on a Node which mounts clustered storage.

[root@node01 ~]#

systemctl start mssql-server

[root@node01 ~]#

sqlcmd -S localhost -U SA

Password:


1> use master;
2> go

# add [hacluster] user
1> create login hacluster with PASSWORD= N'password';
2> go

# add [sysadmin] role
1> alter server role [sysadmin] add member hacluster;
2> go
1> exit

[root@node01 ~]#

systemctl stop mssql-server

[5]	Add information of the account above for Pacemaker like follows on All Nodes.

[root@node01 ~]#

echo 'hacluster' > /var/opt/mssql/secrets/passwd

[root@node01 ~]#

echo 'password' >> /var/opt/mssql/secrets/passwd

[root@node01 ~]#

chown root. /var/opt/mssql/secrets/passwd

[root@node01 ~]#

chmod 600 /var/opt/mssql/secrets/passwd

[6]	Add SQL Server and Virtual IP address to Cluster Resource. Do it on a Node.

[root@node01 ~]#

pcs resource create MSSQL_HA ocf:mssql:fci op start interval=60s --group SQL_Cluster

[root@node01 ~]#

pcs resource create VIP ocf:heartbeat:IPaddr2 ip=10.0.0.50 cidr_netmask=24 op monitor interval=30s --group SQL_Cluster

[root@node01 ~]#

pcs resource show

 Clone Set: dlm-clone [dlm]
     Started: [ node01.srv.world node02.srv.world ]
 Clone Set: clvmd-clone [clvmd]
     Started: [ node01.srv.world node02.srv.world ]
 Clone Set: fs_gfs2-clone [fs_gfs2]
     Started: [ node01.srv.world node02.srv.world ]
 Resource Group: SQL_Cluster
     MSSQL_HA   (ocf::mssql:fci):       Started node01.srv.world
     VIP        (ocf::heartbeat:IPaddr2):       Started node01.srv.world

[root@node01 ~]#

pcs constraint order start fs_gfs2-clone then SQL_Cluster

Adding fs_gfs2-clone SQL_Cluster (kind: Mandatory) (Options: first-action=start then-action=start)
[root@node01 ~]#

pcs constraint colocation add SQL_Cluster with fs_gfs2-clone

[root@node01 ~]#

pcs constraint show

Location Constraints:
Ordering Constraints:
  start dlm-clone then start clvmd-clone (kind:Mandatory)
  start clvmd-clone then start fs_gfs2-clone (kind:Mandatory)
  start fs_gfs2-clone then start SQL_Cluster (kind:Mandatory)
Colocation Constraints:
  clvmd-clone with dlm-clone (score:INFINITY)
  fs_gfs2-clone with clvmd-clone (score:INFINITY)
  SQL_Cluster with fs_gfs2-clone (score:INFINITY)
Ticket Constraints:

[7]	It's OK all. Stop active node manualy and verify failover normally.

[root@node01 ~]#

pcs resource show

 Clone Set: dlm-clone [dlm]
     Started: [ node01.srv.world node02.srv.world ]
 Clone Set: clvmd-clone [clvmd]
     Started: [ node01.srv.world node02.srv.world ]
 Clone Set: fs_gfs2-clone [fs_gfs2]
     Started: [ node01.srv.world node02.srv.world ]
 Resource Group: SQL_Cluster
     MSSQL_HA   (ocf::mssql:fci):       Started node01.srv.world
     VIP        (ocf::heartbeat:IPaddr2):       Started node01.srv.world

[root@node01 ~]#

sqlcmd -S 10.0.0.50 -U SA -Q 'select @@version'

Password:

-----------------------------------------------------------------------
Microsoft SQL Server 2017 (RTM) - 14.0.1000.169 (X64)
        Aug 22 2017 17:04:49
        Copyright (C) 2017 Microsoft Corporation
        Developer Edition (64-bit) on Linux (CentOS Linux 7 (Core))

(1 rows affected)

# stop node01

[root@node01 ~]#

pcs cluster stop node01.srv.world

# on node02

[root@node02 ~]#

pcs resource show

 Clone Set: dlm-clone [dlm]
     Started: [ node02.srv.world ]
     Stopped: [ node01.srv.world ]
 Clone Set: clvmd-clone [clvmd]
     Started: [ node02.srv.world ]
     Stopped: [ node01.srv.world ]
 Clone Set: fs_gfs2-clone [fs_gfs2]
     Started: [ node02.srv.world ]
     Stopped: [ node01.srv.world ]
 Resource Group: SQL_Cluster
     MSSQL_HA   (ocf::mssql:fci):       Started node02.srv.world
     VIP        (ocf::heartbeat:IPaddr2):       Started node02.srv.world

[root@node02 ~]#

sqlcmd -S 10.0.0.50 -U SA -Q 'select @@version'

Password:

-----------------------------------------------------------------------
Microsoft SQL Server 2017 (RTM) - 14.0.1000.169 (X64)
        Aug 22 2017 17:04:49
        Copyright (C) 2017 Microsoft Corporation
        Developer Edition (64-bit) on Linux (CentOS Linux 7 (Core))

(1 rows affected)

Matched Content