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] | |
[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 ~]#
[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 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 ~]#
[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
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) |
Sponsored Link |
|