SQL Server 2017 : Failover Cluster Instance2017/10/15 |
SQL Server Failover Cluster Instance を構成します。
当例では、以下のような環境を前提に進めます。
ISCSIストレージ + CLVM (Clustered Logical Volume Manager) + GFS2ファイルシステム 上に SQL Server Failover Cluster Instance を構築します。 +--------------------+ | [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] | 全ノードで SQL Server を停止し、自動起動をオフにします。また SQL Server for HA パッケージを追加しておきます。 |
[root@node01 ~]# systemctl stop mssql-server [root@node01 ~]# systemctl disable mssql-server [root@node01 ~]# yum -y install mssql-server-ha |
[3] | 共有ストレージをクラスターリソースに追加し、SQL Server のデータ領域を共有ストレージ上にコピーします。 |
[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] | 一旦 SQL Server を起動し、Pacemaker 用の SQL Server アカウントを追加しておきます。共有ストレージがマウントされているノード上で実施します。 |
[root@node01 ~]# systemctl start mssql-server [root@node01 ~]# sqlcmd -S localhost -U SA Password: 1> use master; 2> go # [hacluster] ユーザー作成 1> create login hacluster with PASSWORD= N'password'; 2> go # [sysadmin] ロール付与 1> alter server role [sysadmin] add member hacluster; 2> go 1> exit[root@node01 ~]# systemctl stop mssql-server |
[5] | 追加した Pacemaker 用の SQL Server アカウントの情報を登録します。全ノードで実施します。 |
[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] | SQL Server と仮想 IP をクラスターリソースに追加します。いずれか一方のノードで実施します。 |
[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] | 以上で設定完了です。アクティブノードを手動で停止させ、正常にフェイルオーバーするか確認しておきます。 |
[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) # node01 停止 [root@node01 ~]# pcs cluster stop node01.srv.world
# 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 |
|