SQL Server の Read Scale Availability Group (読み取りスケール Availability Group) の手順を調べたので、その時のメモです。

環境

OS: Windows Server 2019 Standard Evaluation
DB: Microsoft SQL Server 2019 Developer Edition

Availability Group について

あるサーバーが故障した時に、別のサーバーがその役割を引き継いでシステムを継続可能にする性能を “可用性” と呼びます。

一般的にデータベースでは、Primary と Secondary サーバーを用意しておいて、普段は Primary データベースを使うが、Primary が何らかの障害で止まった時は Secondary データベースにフェールオーバーしてシステムを継続させる、というように構成して可用性を実現しています。

SQL Server では、高可用性を実現するための機能を Always On Availability Group と呼びます (日本語では “Always On 可用性グループ”)。

Always On Availability Group は SQL Server 2014 で追加された機能で、通常時は Primary データベースでデータ更新を行い、その変更は Secondary データベースにレプリケーションされます。そして Primary が故障した時は、自動的に Secondary が新しい Primary に昇格し、以降は Secondary が新しい Primary としてデータ更新も受け付けるようになります。

サーバー故障時のフェールオーバーには Windows Server Failover Cluster を使います。SQL Server の設定に加え、Windows Server クラスターの設定も必要になるため、考慮すべきことが多く煩雑でした。

(ちなみに、SQL Server for Linux では Pacemaker という高可用性を実現するコンポーネントを使ってフェールオーバーを行います。サーバーのフェールオーバーは SQL Server が行うのではなく OS に任せる、という点では Windows と同様です)

Always On Availability Group では、Secondary となるサーバーを Read Replica としても使うことができます。レプリケーションも Read Replica を実現する機能として以前からありましたが、Availability Group と異なり、レプリケーションでは Secondary が Primary に昇格できませんでした。

(レプリケーションについてはこのブログでも書いたので、SQL Server レプリケーションを参照してください)

ただ、Read 処理を分散し、障害時にのみ Read Replica が Primary に昇格する、ということだけを実現するために、Windows Server クラスターを構築しなければいけないというのは手間でした。

そのため SQL Server 2017 では Windows Server Failover Cluster を利用せず、Read 処理の分散のみを目的とした機能が “Read Scale Availability Group” として追加されました。

Read Scale Availability Group では、MySQL など他のデータベースのレプリケーションと同様、Primary データベースでの変更が Secondary データベースへレプリケーションされ、Secondary データベースでは読み取り処理のみ実行できます。そして Primary が停止した場合は Secondary を新しい Primary として昇格することができます。

Read Scale Availability Group は高可用性を実現するための機能ではないため、Primary 停止時にフェールオーバーは自動では行われず、手動で行う必要がある点に注意してください。フェールオーバーを自動的に行い、ダウンタイムを最小限に抑える必要がある場合は Always On Availability Group を使う必要があると思います。

この記事では Read Scale Availability Group について説明します。

用語

Availability Group で一般的に使われる用語を簡単に説明します。

用語	説明
Availability Group (可用性グループ)	フェールオーバーが行われるデータベースのグループを表します。
Availability Database (可用性データベース)	可用性グループに含まれる、フェールーバーが行われるデータベースを表します。 1つの可用性グループには、1つの Primary データベースと、1～8 個の Secondary データベースが含まれます。
Availability Replica (可用性レプリカ)	Availability Database をホストするサーバーを表します。 Primary Replica は Primary データベースをホストするサーバーを表します。 Secondary Replica は Secondary データベースをホストするサーバーを表します。

Read Scale Availability Group 構成手順

Read Scale Availability Group を構成する手順を説明します。SQL Server Management Studio を使って UI 上で設定することもできますが、今回は全てコマンドを使って構成する方法を紹介します。

各サーバーの情報は以下の通りです。

Primary サーバー:
- コンピューター名: MSSQL1
- IP アドレス: 172.16.11.14
Secondary サーバー:
- コンピューター名: MSSQL2
- IP アドレス: 172.16.11.15

1). 事前準備

1-1). テスト用データベースの作成

Primary サーバーに今回レプリケーションするデータベースを作成します。今回は testdb という名前にします。

データベースは “完全復旧モデル” として設定する必要がある点に注意してください。

Primary サーバー

/* データベースを作成 */
CREATE DATABASE testdb COLLATE Japanese_CI_AS;

/* 完全復旧モデルとして設定 */
ALTER DATABASE [testdb] SET RECOVERY FULL;
GO

USE testdb;
GO

/* レプリケーションするテーブルを作成 */
CREATE TABLE table1 (id INT IDENTITY PRIMARY KEY, name NVARCHAR(255));
GO

BEGIN TRAN
INSERT INTO table1 (name) VALUES (N'name 1-1'), (N'name 1-2');
COMMIT

CREATE TABLE table2 (id INT IDENTITY PRIMARY KEY, name NVARCHAR(255));
GO

BEGIN TRAN
INSERT INTO table2 (name) VALUES (N'name 2-1'), (N'name 2-2');
COMMIT

1-2). Always On Availability Group を有効化

Primary と Secondary 両方で、Always On Availability Group を有効化しておく必要があります。

今回は Powershell の SqlServer モジュールを使って設定しています。

Primary サーバー

> powershell

# SqlServer モジュールをインストール
PS> Install-Module -Name SqlServer

# Always On Availability Group を有効化
PS> Enable-SqlAlwaysOn -Path SQLSERVER:\SQL\MSSQL1\DEFAULT -Force

Secondary サーバー

> powershell

# SqlServer モジュールをインストール
PS> Install-Module -Name SqlServer

# Always On Availability Group を有効化
PS> Enable-SqlAlwaysOn -Path SQLSERVER:\SQL\MSSQL2\DEFAULT -Force

1-3). エラー発生時の調査を楽にするため、xEvent を有効化

これも Primary と Secondary 両方で有効化しておきます。

Primary サーバー

ALTER EVENT SESSION  AlwaysOn_health ON SERVER WITH (STARTUP_STATE=ON);
GO

Secondary サーバー

ALTER EVENT SESSION  AlwaysOn_health ON SERVER WITH (STARTUP_STATE=ON);
GO

1-4). エンドポイントの設定

Primary と Secondary は “ミラーリングエンドポイント” というエンドポイントを使ってやり取りするので、このエンドポイントを作成します。

エンドポイントへの認証の方法は、SQL Server 認証と AD 認証の 2つから選択できます。今回は SQL Server 認証を使います。SQL Server 認証では、認証に証明書を使うため、まず認証用の証明書を作成してからエンドポイントを作成します。

今回は rsag_login という SQL Server LOGIN を使い、データベースへの認証には rsag_user という USER を使うことにします。証明書や秘密鍵は SQL Server のデータディレクトリに保存しています。

Primary サーバー

USE master;
GO

/* SQL Server LOGIN とユーザーを作成 */
CREATE LOGIN rsag_login WITH PASSWORD = 'password1!';
CREATE USER rsag_user FOR LOGIN rsag_login ;

/* 証明書と秘密鍵を作成 */
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password1!';
CREATE CERTIFICATE rsag_certificate WITH SUBJECT = 'rsag';
BACKUP CERTIFICATE rsag_certificate
  TO FILE = 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\rsag_certificate.cer'
  WITH PRIVATE KEY (
    FILE = 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\rsag_certificate.pvk',
    ENCRYPTION BY PASSWORD = 'password1!'
  );

上記の SQL を実行すると、Primary サーバーの C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA フォルダに rsag_certificate.cer と rsag_certificate.pvk という2つのファイルが作成されます。

これらのファイルを Secondary サーバーの同じフォルダにコピーします。証明書と秘密鍵を Secondary サーバーにコピーしたら、コピーした証明書などを元に Secondary の証明書を設定します。

Secondary サーバー

USE master;
GO

/* Primary と同じ ID、パスワードで SQL Server LOGIN とユーザーを作成 */
CREATE LOGIN rsag_login WITH PASSWORD = 'password1!';  
CREATE USER rsag_user FOR LOGIN rsag_login ;

/* Primary で作成した証明書と秘密鍵を元に、Secondary の証明書を設定 */
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password1!';
CREATE CERTIFICATE rsag_certificate
  AUTHORIZATION rsag_user 
  FROM FILE = 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\rsag_certificate.cer'
  WITH PRIVATE KEY (
    FILE = 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\rsag_certificate.pvk',
    DECRYPTION BY PASSWORD = 'password1!'
  );

続いて Primary と Secondary それぞれでエンドポイントを作成します。

今回はポート 5022 を使ってエンドポイントを作成しています。

Primary サーバー

CREATE ENDPOINT [rsag_endpoint]
  AS TCP (LISTENER_PORT = 5022)
  FOR DATA_MIRRORING (
	  ROLE = ALL,
	  AUTHENTICATION = CERTIFICATE rsag_certificate,
    ENCRYPTION = REQUIRED ALGORITHM AES
  );

ALTER ENDPOINT [rsag_endpoint] STATE = STARTED;
GRANT CONNECT ON ENDPOINT::[rsag_endpoint] TO [rsag_login];

Secondary サーバー

CREATE ENDPOINT [rsag_endpoint]
  AS TCP (LISTENER_PORT = 5022)
  FOR DATA_MIRRORING (
    ROLE = ALL,
    AUTHENTICATION = CERTIFICATE rsag_certificate, 
    ENCRYPTION = REQUIRED ALGORITHM AES
	);

ALTER ENDPOINT [rsag_endpoint] STATE = STARTED;
GRANT CONNECT ON ENDPOINT::[rsag_endpoint] TO [rsag_login];

1-5). Primary と Secondary 間のアクセス設定

Primary と Secondary 間で、コンピューター名を使用して相互にアクセスできるよう設定します。

コンピューター名の名前解決は hosts で行うことにします。また Firewall では SQL Server のポート (1433) の他に、上記で作成したミラーリングエンドポイントのポート (5022) も許可します。

SQL Server はデフォルトで TCP 接続が無効なので、ここで有効にしておきます。

Primary サーバー

# hosts に Secondary の IP アドレスを登録
> notepad C:\Windows\System32\drivers\etc\hosts
---
172.16.11.15 MSSQL2
---

# Firewall でポート 1433 および 5022 を許可
> netsh advfirewall firewall add rule name="allow-sql-server" dir=in action=allow enable=yes ^
  profile=any protocol=tcp localport=1433 remoteip=172.16.11.15 remoteport=any 
> netsh advfirewall firewall add rule name="allow-sql-server-mirror-endpoint" dir=in action=allow enable=yes ^
  profile=any protocol=tcp localport=5022 remoteip=172.16.11.15 remoteport=any 

# SQL Server で TCP を許可
> sqlps
PS> $wmi = New-Object 'Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer' localhost
PS> $tcp = $wmi.ServerInstances['MSSQLSERVER'].ServerProtocols['Tcp']
PS> $tcp.IsEnabled = $true
PS> $tcp.Alter()
PS> Restart-Service -Name MSSQLSERVER -Force
PS> exit

Secondary サーバー

# hosts に Primary の IP アドレスを登録
> notepad C:\Windows\System32\drivers\etc\hosts
---
172.16.11.14 MSSQL1
---

# Firewall でポート 1433 および 5022 を許可
> netsh advfirewall firewall add rule name="allow-sql-server" dir=in action=allow enable=yes ^
  profile=any protocol=tcp localport=1433 remoteip=172.16.11.14 remoteport=any 
> netsh advfirewall firewall add rule name="allow-sql-server-mirror-endpoint" dir=in action=allow enable=yes ^
  profile=any protocol=tcp localport=5022 remoteip=172.16.11.14 remoteport=any 

# SQL Server で TCP を許可
> sqlps
PS> $wmi = New-Object 'Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer' localhost
PS> $tcp = $wmi.ServerInstances['MSSQLSERVER'].ServerProtocols['Tcp']
PS> $tcp.IsEnabled = $true
PS> $tcp.Alter()
PS> Restart-Service -Name MSSQLSERVER -Force
PS> exit

2). Availability Group を作成

それでは Availability Group の構成を行っていきます。まず Primary サーバーで Availability Group を作成します。

今回は rsag1 という名前の Availability Group を作成しています。

Primary サーバー

# Availability Group を作成
CREATE AVAILABILITY GROUP [rsag1] /* Availability Group 名 */
  WITH (CLUSTER_TYPE = NONE)
  FOR REPLICA ON
    N'MSSQL1' WITH (  /* Primary のコンピューター名 */
      ENDPOINT_URL = N'tcp://MSSQL1:5022', /* Primary のエンドポイント */
		  AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
		  FAILOVER_MODE = MANUAL,
		  SEEDING_MODE = AUTOMATIC,
      SECONDARY_ROLE (ALLOW_CONNECTIONS = ALL)
		),
    N'MSSQL2' WITH (  /* Secondary のコンピューター名 */
		  ENDPOINT_URL = N'tcp://MSSQL2:5022', /* Secondary のエンドポイント */
		  AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
		  FAILOVER_MODE = MANUAL,
		  SEEDING_MODE = AUTOMATIC,
		  SECONDARY_ROLE (ALLOW_CONNECTIONS = ALL)
		);

ALTER AVAILABILITY GROUP [rsag1] GRANT CREATE ANY DATABASE;

3). Secondary を Availability Group に参加させる

Primary サーバーで Availability Group を作成したら、Secondary サーバーを参加させます。

Secondary サーバー

ALTER AVAILABILITY GROUP [rsag1] JOIN WITH (CLUSTER_TYPE = NONE);
ALTER AVAILABILITY GROUP [rsag1] GRANT CREATE ANY DATABASE;

4). Availability Group にデータベースを追加

Primary サーバーでレプリケーションするデータベースを Availability Group に追加します。

なお、データベースを Availability Group に追加する時、そのデータベースのトランザクションログバックアップが必要となります。

一度もバックアップを取得したことのないデータベースでは、まだログバックアップが存在しないため、ここで一度バックアップを取得しておきます。

Primary サーバー

/* バックアップを取得 */
BACKUP DATABASE [testdb]
  TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\testdb.bak';

/* Availability Group にデータベースを追加 */
ALTER AVAILABILITY GROUP [rsag1] ADD DATABASE [testdb];

Primary サーバーで Availability Group にデータベースを追加すると、Secondary サーバーにもデータベースが作成されるので、確認してみます。

Secondary サーバー

/* データベースが作成されているのを確認 */
SELECT * FROM sys.databases WHERE name = 'testdb';
GO

/* Availability Group の同期状態を確認 */
SELECT DB_NAME(database_id) AS 'database', synchronization_state_desc 
FROM sys.dm_hadr_database_replica_states;

5). 各データベースの利用

Primary および Secondary にある各データベースは、通常の SQL Server データベースと同じように接続して使用できます。

Secondary では Write 処理 (データの追加、更新、削除等) ができない点を除き、特に特別なことはありません。

6). Secondary へのフェールオーバー

Primary が障害などで利用できなくなった場合、手動で Secondary へフェールオーバーする必要があります。Primary がメンテナンス等で一時的に利用できなくなる場合と、障害で停止しその後ずっと利用できなくなる場合とで手順が異なります。

今回は障害で停止したと想定し、強制的にフェールオーバーする手順を紹介します。

Secondary へのフェールオーバーは、まず Secondary サーバーで Availability Group を強制的にフェールオーバーさせ、その後 Primary サーバーを Availability Group から削除するという手順で行います。

Secondary サーバー

/* Secondary へ強制的にフェールオーバー */
ALTER AVAILABILITY GROUP [rsag1] FORCE_FAILOVER_ALLOW_DATA_LOSS;

/* Availability Group から Primary を削除 */
ALTER AVAILABILITY GROUP [rsag1] REMOVE REPLICA ON N'MSSQL1';

これで、Secondary が新しい Primary となり、Write 処理も受け付けられるようになります。

障害で停止した Primary について

一度フェールオーバーし、Availability Group から削除されたデータベースは、そのままでは再度 Availability Group に参加することはできません。

もし障害があったサーバーを再度 Availability Group に参加させたい場合は、一度インスタンスに設定されている Availability Group や Availability Database などを削除した上で、新しい Secondary として改めて Availability Group に参加し直す必要があります。

ただ通常は障害のあったサーバーインスタンスは破棄し、新しいサーバーに SQL Server をインストールして使用すると思いますので、この記事では扱いません。

まとめ

Read Scale Availability Group の設定手順は以上です。

MySQL や PostgreSQL など他のデータベースのレプリケーションと同じように運用できるので、SQL Server で Read Replica を使用する場合はレプリケーションではなく Read Scale Availability Group を利用するのが良いと思います。

如是我聞

a blog about life and tech

SQL Server Read Scale Availability Group

環境

Availability Group について

用語

Read Scale Availability Group 構成手順

1). 事前準備

1-1). テスト用データベースの作成

1-2). Always On Availability Group を有効化

1-3). エラー発生時の調査を楽にするため、xEvent を有効化

1-4). エンドポイントの設定

1-5). Primary と Secondary 間のアクセス設定

2). Availability Group を作成

3). Secondary を Availability Group に参加させる

4). Availability Group にデータベースを追加

5). 各データベースの利用

6). Secondary へのフェールオーバー

障害で停止した Primary について

まとめ