DBAtlas · データベース運用アトラス

SQL Server Always On 可用性グループの構築

症状

目標: Always On 可用性グループ (AG) で、SQL Server の自動フェイルオーバーと読み取り可能セカンダリレプリカを構築する。

1. Windows Server フェールオーバー クラスター (WSFC) を構築する

Always On は基盤となる WSFC に依存する。すべてのノードにフェールオーバー クラスタリング機能をインストールし、ネットワークと監視 (witness) を構成する。PowerShell でクラスタを作成する。

SQL Server

-- PowerShell (the Failover-Clustering feature must be installed on every node):
New-Cluster -Name sqlclu -Node node1,node2 -StaticAddress 10.0.0.50
-- Witness:
Set-ClusterQuorum -NodeAndFileShareMajority \\fs01\quorum

2. 各インスタンスで Always On を有効にする

SQL Server 構成マネージャーまたは PowerShell で Always On 可用性グループ機能を有効にする。その後 SQL Server サービスを再起動する。これによりクラスタのメンバーシップがインスタンスに結び付けられる。

SQL Server

-- PowerShell:
Enable-SqlAlwaysOn -ServerInstance node1\SQL01 -Force
Enable-SqlAlwaysOn -ServerInstance node2\SQL01 -Force

3. データベースをバックアップし、セカンダリで WITH NORECOVERY でリストアする

AG に追加するデータベースは FULL 復旧モデルである必要がある。プライマリでフルバックアップとログバックアップを取り、セカンダリで WITH NORECOVERY でリストアする (redo を受け取り続けられるように)。これは手動シーディングであり、代替は自動シーディングである。

SQL Server

-- On the primary:
BACKUP DATABASE app TO DISK='\\fs01\bak\app.bak';
BACKUP LOG app TO DISK='\\fs01\bak\app.trn';
-- On the secondary:
RESTORE DATABASE app FROM DISK='\\fs01\bak\app.bak' WITH NORECOVERY;
RESTORE LOG app FROM DISK='\\fs01\bak\app.trn' WITH NORECOVERY;

4. エンドポイントを作成し、可用性グループを定義する

ノード間の redo トラフィック用に、ポート 5022 で HADR エンドポイントを作成する。同期モード (SYNCHRONOUS_COMMIT) はデータ損失ゼロの自動フェイルオーバーを提供し、非同期 (ASYNCHRONOUS_COMMIT) は遅延があるが遠隔 DR 向けに高性能である。

SQL Server

CREATE ENDPOINT hadr_endpoint STATE=STARTED
  AS TCP (LISTENER_PORT=5022)
  FOR DATA_MIRRORING (ROLE=ALL, ENCRYPTION=REQUIRED ALGORITHM AES);

CREATE AVAILABILITY GROUP appag
  FOR DATABASE app
  REPLICA ON
    'NODE1\SQL01' WITH (ENDPOINT_URL='TCP://node1:5022', AVAILABILITY_MODE=SYNCHRONOUS_COMMIT, FAILOVER_MODE=AUTOMATIC, SECONDARY_ROLE(ALLOW_CONNECTIONS=ALL)),
    'NODE2\SQL01' WITH (ENDPOINT_URL='TCP://node2:5022', AVAILABILITY_MODE=SYNCHRONOUS_COMMIT, FAILOVER_MODE=AUTOMATIC, SECONDARY_ROLE(ALLOW_CONNECTIONS=ALL));

5. セカンダリを AG に参加させ、データベースを参加させる

セカンダリインスタンスで AG に JOIN し、リストアしたデータベースを AG に追加する。この手順は手動シーディングを使った場合に必要で、自動シーディングではプライマリ側で SET(SEEDING_MODE) により処理される。

SQL Server

-- On the secondary:
ALTER AVAILABILITY GROUP appag JOIN;
ALTER DATABASE app SET HADR AVAILABILITY GROUP = appag;

6. 可用性グループリスナーを作成する

リスナーはアプリケーションが接続する仮想名と IP である。フェイルオーバー後、接続は自動的に新プライマリにリダイレクトされる。アプリケーションの接続文字列でこの名前と MultiSubnetFailover=True を使う。

SQL Server

ALTER AVAILABILITY GROUP appag
  ADD LISTENER 'appag-listener' (WITH IP ((N'10.0.0.60', N'255.255.255.0')), PORT=1433);

7. フェイルオーバーをテストし、同期状態を検証する

同期が 'HEALTHY' になったら、計画的フェイルオーバーでロール変更をテストする。自動フェイルオーバーには、同期モードかつ FAILOVER_MODE=AUTOMATIC である必要がある。sys.dm_hadr_availability_replica_states で状態を監視する。

SQL Server

SELECT ag.name, ars.role_desc, ars.synchronization_health_desc
FROM sys.dm_hadr_availability_replica_states ars
JOIN sys.availability_groups ag ON ag.group_id=ars.group_id;
-- Planned failover (on the node that will become the new primary):
ALTER AVAILABILITY GROUP appag FAILOVER;
DBAtlas で開く →