Always On 可用性グループの読み取りスケールを構成する

[アーティクル]
04/26/2023

適用対象:SQL Server

Windows で読み取りスケールワークロードの SQL Server Always On 可用性グループを構成できます。可用性グループには、2 種類のアーキテクチャがあります。

高可用性のアーキテクチャでは、クラスターマネージャーを利用し、ビジネス継続性を改善します。また、読み取り可能なセカンダリレプリカを含めることもできます。この高可用性アーキテクチャを作成するには、「可用性グループの作成と構成 (SQL Server)」を参照してください。
アーキテクチャでは、読み取りスケールワークロードのみをサポートします。

この記事では、読み取りスケールワークロードの場合で、クラスターマネージャーがない可用性グループを作成する方法について説明します。このアーキテクチャは、読み取りスケールのみを提供します。高可用性は提供されません。

注意

CLUSTER_TYPE = NONE による可用性グループには、さまざまなオペレーティングシステムプラットフォームでホストされているレプリカを含めることができます。高可用性はサポートできません。 Linux オペレーティングシステムについては、Linux で読み取りスケールの SQL Server 可用性グループを構成する方法に関するページを参照してください。

前提条件

可用性グループを作成する前に、以下のことを行う必要があります。

可用性レプリカをホストするすべてのサーバーが通信できるように環境を設定します。
SQL Server をインストールします。詳細については、「SQL Server をインストールする」を参照してください。

Always On 可用性グループを有効にして mssql-server を再起動する

注意

次のコマンドでは、PowerShell ギャラリーで公開されている sqlserver モジュールのコマンドレットを利用しています。 Install-Module コマンドを使用して、このモジュールをインストールすることができます。

SQL Server インスタンスをホストする各レプリカで Always On 可用性グループを有効にします。次に、SQL Server サービスを再起動します。次のコマンドを実行し、SQL Server サービスを有効にして再起動します。

Enable-SqlAlwaysOn -ServerInstance <server\instance> -Force

AlwaysOn_health イベントセッションを有効にする

可用性グループのトラブルシューティング時に根本原因を診断できるように、オプションで Always On 可用性グループの拡張イベント (XEvents) セッションを有効にすることができます。この操作を行うには、SQL Server の各インスタンスで次のコマンドを実行します。

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

この XEvents セッションの詳細については、「Always On 可用性グループの拡張イベント」を参照してください。

データベースミラーリングエンドポイントの認証

同期が正常に機能するように、読み取りスケール可用性グループに関連するレプリカでは、エンドポイントを介して認証する必要があります。このような認証に使用できる 2 つの主なシナリオを、次のセクションで説明します。

サービスアカウント

すべてのセカンダリレプリカが同じドメインに参加している Active Directory 環境では、SQL Server はサービスアカウントを利用して認証することができます。 SQL Server インスタンスでそれぞれ明示的に、サービスアカウントのログインを作成する必要があります。

CREATE LOGIN [<domain>\service account] FROM WINDOWS;

セカンダリレプリカが Active Directory ドメインに参加していない可能性がある環境では、SQL 認証を利用する必要があります。次の Transact-SQL スクリプトでは、dbm_login という名前のログインと dbm_user という名前のユーザーを作成します。強力なパスワードでスクリプトを更新します。データベースミラーリングエンドポイントのユーザーを作成するには、すべての SQL Server インスタンスで次のコマンドを実行します。

CREATE LOGIN dbm_login WITH PASSWORD = '**<1Sample_Strong_Password!@#>**';
CREATE USER dbm_user FOR LOGIN dbm_login;

証明書の認証

SQL 認証での認証が必要なセカンダリレプリカを利用する場合は、証明書を使用してミラーリングエンドポイント間の認証を行います。

次の Transact-SQL スクリプトでは、マスターキーと証明書を作成します。その後、証明書をバックアップし、秘密キーでファイルをセキュリティ保護します。強力なパスワードでスクリプトを更新してください。プライマリ SQL Server インスタンスで次のスクリプトを実行し、証明書を作成します。

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '**<Master_Key_Password>**';
CREATE CERTIFICATE dbm_certificate WITH SUBJECT = 'dbm';
BACKUP CERTIFICATE dbm_certificate
   TO FILE = 'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\dbm_certificate.cer'
   WITH PRIVATE KEY (
       FILE = 'c:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\dbm_certificate.pvk',
       ENCRYPTION BY PASSWORD = '**<Private_Key_Password>**'
   );

この時点で、プライマリ SQL Server レプリカの c:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\dbm_certificate.cer には証明書が、c:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\dbm_certificate.pvk には秘密キーが作成されています。これら 2 つのファイルを、可用性レプリカをホストするすべてのサーバー上の同じ場所にコピーします。

各セカンダリレプリカで、SQL Server インスタンスのサービスアカウントに証明書へのアクセス権限があることを確認します。

セカンダリサーバーで証明書を作成する

次の Transact-SQL スクリプトでは、プライマリ SQL Server レプリカで作成したバックアップからマスターキーと証明書を作成します。コマンドではユーザーが証明書にアクセスすることも承認します。強力なパスワードでスクリプトを更新してください。暗号化解除パスワードは、前の手順で .pvk ファイルの作成に使ったものと同じパスワードです。証明書を作成するには、すべてのセカンダリサーバーで次のスクリプトを実行します。

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '**<Master_Key_Password>**';
CREATE CERTIFICATE dbm_certificate
    AUTHORIZATION dbm_user
    FROM FILE = 'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\dbm_certificate.cer'
    WITH PRIVATE KEY (
        FILE = 'c:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\dbm_certificate.pvk',
        DECRYPTION BY PASSWORD = '**<Private_Key_Password>**'
    );

すべてのレプリカにデータベースミラーリングエンドポイントを作成する

データベースミラーリングエンドポイントでは、伝送制御プロトコル (TCP) を使用して、データベースミラーリングセッションに参加するサーバーインスタンス間、または可用性レプリカをホストするサーバーインスタンス間でメッセージを送受信します。データベースミラーリングエンドポイントでは、一意の TCP ポート番号でリッスンします。

次の Transact-SQL スクリプトでは、可用性グループに対して Hadr_endpoint という名前のリスニングエンドポイントを作成します。これで、エンドポイントが開始され、前の手順で作成した SQL ログインまたはサービスアカウントへの接続権限が与えられます。スクリプトを実行する前に、**< ... >** の間の値を置き換えます。必要に応じて、IP アドレス LISTENER_IP = (0.0.0.0) を含めることができます。リスナー IP アドレスは、IPv4 アドレスである必要があります。 0.0.0.0 を使用することもできます。

すべての SQL Server インスタンスで、ご利用の環境に合わせて次の Transact-SQL スクリプトを更新します。

CREATE ENDPOINT [Hadr_endpoint]
    AS TCP (LISTENER_PORT = **<5022>**)
    FOR DATABASE_MIRRORING (
        ROLE = ALL,
        AUTHENTICATION = CERTIFICATE dbm_certificate,
        ENCRYPTION = REQUIRED ALGORITHM AES
    );
ALTER ENDPOINT [Hadr_endpoint] STATE = STARTED;
GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO [<service account or user>];

ファイアウォールの TCP ポートをリスナーポート用に開く必要があります。

詳細については、「データベースミラーリングエンドポイント (SQL Server)」を参照してください。

可用性グループを作成する

可用性グループを作成します。 CLUSTER_TYPE = NONE を設定します。さらに、FAILOVER_MODE = NONE で各レプリカを設定します。分析やレポートのワークロードを実行するクライアントアプリケーションは、セカンダリデータベースに直接接続できます。また、読み取り専用ルーティングリストも作成できます。プライマリレプリカへの接続によって、ルーティングリストに基づき、ラウンドロビン方式で各セカンダリレプリカに読み取り接続要求を転送します。

次の Transact-SQL スクリプトでは、ag1 という名前の可用性グループを作成します。このスクリプトでは、SEEDING_MODE = AUTOMATIC で可用性グループのレプリカが構成されます。この設定によって、SQL Server で可用性グループにセカンダリサーバーが追加されるたびに、そのセカンダリサーバーでデータベースが自動作成されます。

ご利用の環境に合わせて次のスクリプトを変更してください。 <node1> 値と <node2> 値を、レプリカをホストする SQL Server インスタンスの名前に置き換えます。 <5022> 値を、エンドポイントに設定したポートに置き換えます。プライマリ SQL Server レプリカで次の Transact-SQL スクリプトを実行します。

CREATE AVAILABILITY GROUP [ag1]
    WITH (CLUSTER_TYPE = NONE)
    FOR REPLICA ON
        N'<node1>' WITH (
            ENDPOINT_URL = N'tcp://<node1>:<5022>',
		    AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
		    FAILOVER_MODE = MANUAL,
		    SEEDING_MODE = AUTOMATIC,
                    SECONDARY_ROLE (ALLOW_CONNECTIONS = ALL)
		    ),
        N'<node2>' WITH (
		    ENDPOINT_URL = N'tcp://<node2>:<5022>',
		    AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
		    FAILOVER_MODE = MANUAL,
		    SEEDING_MODE = AUTOMATIC,
		    SECONDARY_ROLE (ALLOW_CONNECTIONS = ALL)
		    );

ALTER AVAILABILITY GROUP [ag1] GRANT CREATE ANY DATABASE;

セカンダリ SQL Server インスタンスを可用性グループに参加させる

次の Transact-SQL スクリプトにより、ag1 という名前の可用性グループにサーバーを参加させます。ご利用の環境に合わせてスクリプトを変更してください。可用性グループに参加させるには、各セカンダリ SQL Server レプリカで次の Transact-SQL スクリプトを実行します。

ALTER AVAILABILITY GROUP [ag1] JOIN WITH (CLUSTER_TYPE = NONE);

ALTER AVAILABILITY GROUP [ag1] GRANT CREATE ANY DATABASE;

可用性グループにデータベースを追加する

可用性グループに追加するデータベースが、完全復旧モデルであり、有効なログバックアップがあることを確認します。データベースがテストデータベースまたは新しく作成されたデータベースの場合は、データベースバックアップを実行します。 db1 という名前のデータベースを作成してバックアップするには、プライマリ SQL Server インスタンスで、次の Transact-SQL スクリプトを実行します。

CREATE DATABASE [db1];
ALTER DATABASE [db1] SET RECOVERY FULL;
BACKUP DATABASE [db1]
   TO DISK = N'c:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Backup\db1.bak';

db1 という名前のデータベースを ag1 という名前の可用性グループに追加するには、プライマリ SQL Server レプリカで、次の Transact-SQL スクリプトを実行します。

ALTER AVAILABILITY GROUP [ag1] ADD DATABASE [db1];

セカンダリサーバーにデータベースが作成されたことを確認する

db1 データベースが作成されて同期されているかどうかを確認するには、各セカンダリ SQL Server レプリカで次のクエリを実行します。

SELECT * FROM sys.databases WHERE name = 'db1';
GO
SELECT DB_NAME(database_id) AS 'database', synchronization_state_desc FROM sys.dm_hadr_database_replica_states;

この可用性グループは高可用性構成ではありません。高可用性が必要な場合、Linux で SQL Server の Always On 可用性グループを構成する方法に関するページか、「可用性グループの作成と構成 (SQL Server)」の手順に従ってください。

読み取り専用セカンダリレプリカに接続する

読み取り専用セカンダリレプリカには、次の 2 つの方法で接続できます。

アプリケーションは、セカンダリレプリカをホストする SQL Server インスタンスに直接接続し、データベースにクエリを実行できます。詳細については、「アクティブなセカンダリ: 読み取り可能なセカンダリレプリカ (Always On 可用性グループ)」を参照してください。
アプリケーションでは、リスナーを要求する読み取り専用ルーティングも利用できます。クラスターマネージャーを使用せずに読み取りスケールのシナリオをデプロイする場合でも、SQL Server がリッスンするものと同じ現在のプライマリレプリカとポートの IP アドレスを指すリスナーを作成することができます。フェールオーバー後に新しいプライマリ IP アドレスを指すようにリスナーを再作成する必要があります。詳細については、「リスナーを使用した読み取り専用セカンダリレプリカ (読み取り専用ルーティング) への接続」を参照してください。

読み取りスケール可用性グループのプライマリレプリカをフェールオーバーする

各可用性グループにはプライマリレプリカが 1 つだけあります。プライマリレプリカは読み書きができます。プライマリになっているレプリカの変更は、フェールオーバーで行うことができます。一般的な可用性グループでは、クラスターマネージャーによってフェールオーバープロセスが自動化されます。クラスターの種類が NONE の可用性グループでは、フェールオーバープロセスは手動です。

クラスターの種類が NONE の可用性グループでプライマリレプリカをフェールオーバーするには、2 つの方法があります。

データ損失のない手動フェールオーバー
データ損失のある強制的な手動フェールオーバー

データ損失のない手動フェールオーバー

プライマリレプリカを使用できても、プライマリレプリカをホストするインスタンスを一時的または永続的に変更する必要がある場合は、この方法を使用します。データ損失の可能性を排除するため、手動フェールオーバーを実行する前にターゲットセカンダリレプリカが最新の状態であることを確認します。

データ損失のない手動フェールオーバーを行うには:

現在のプライマリおよびターゲットセカンダリレプリカを SYNCHRONOUS_COMMIT とします。

ALTER AVAILABILITY GROUP [AGRScale] 
     MODIFY REPLICA ON N'<node2>' 
     WITH (AVAILABILITY_MODE = SYNCHRONOUS_COMMIT);

アクティブなトランザクションが、プライマリレプリカと少なくとも 1 つの同期セカンダリレプリカにコミットされていることを確認するために、次のクエリを実行します。
```
SELECT ag.name, 
   drs.database_id, 
   drs.group_id, 
   drs.replica_id, 
   drs.synchronization_state_desc, 
   ag.sequence_number
FROM sys.dm_hadr_database_replica_states drs, sys.availability_groups ag
WHERE drs.group_id = ag.group_id; 
```
synchronization_state_desc が SYNCHRONIZED の場合、セカンダリレプリカは同期されています。
REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT を 1 に更新します。

次の例のスクリプトは、ag1 という名前の可用性グループで REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT を 1 に設定します。次のスクリプトを実行する前に、ag1 を実際の可用性グループの名前に置き換えます。
```
ALTER AVAILABILITY GROUP [AGRScale] 
     SET (REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT = 1);
```
この設定により、すべてのアクティブなトランザクションが、プライマリレプリカと少なくとも 1 つの同期セカンダリレプリカにコミットされます。

注意

この設定は、フェールオーバーに固有のものではなく、環境の要件に基づいて設定する必要があります。
ロールの変更に備えて、フェールオーバーに参加していないプライマリレプリカとセカンダリレプリカをオフラインに設定します。
```
ALTER AVAILABILITY GROUP [AGRScale] OFFLINE
```
ターゲットセカンダリレプリカをプライマリに昇格させます。
```
ALTER AVAILABILITY GROUP AGRScale FORCE_FAILOVER_ALLOW_DATA_LOSS; 
```
以前のプライマリとその他のセカンダリのロールを SECONDARY に更新し、以前のプライマリレプリカをホストする SQL Server インスタンスで次のコマンドを実行します。
```
ALTER AVAILABILITY GROUP [AGRScale] 
     SET (ROLE = SECONDARY); 
```
注意

可用性グループを削除するには、DROP AVAILABILITY GROUP を使います。種類が NONE または EXTERNAL のクラスターを使って作成された可用性グループでは、可用性グループに含まれるすべてのレプリカでコマンドを実行する必要があります。
データ移動を再開し、プライマリレプリカがホストされている SQL Server インスタンス上の可用性グループ内のすべてのデータベースに対して、次のコマンドを実行します。
```
ALTER DATABASE [db1]
     SET HADR RESUME
```
読み取りスケールの目的で作成した、クラスターマネージャーでは管理されないリスナーをすべて再作成します。元のリスナーが以前のプライマリを指している場合、それを削除して、新しいプライマリを指すように再作成します。

データ損失のある強制的な手動フェールオーバー

プライマリレプリカが利用できず、復旧をすぐに行えない場合は、データ損失を伴うセカンダリレプリカへのフェールオーバーを強制的に実行する必要があります。ただし、フェールオーバー後に元のプライマリレプリカが回復した場合は、それによってプライマリの役割が引き継がれます。各レプリカが異なる状態になるのを回避するには、データ損失を伴う強制フェールオーバー後に、可用性グループから元のプライマリを削除します。元のプライマリがオンラインに戻ったら、その中の可用性グループ全体を削除します。

プライマリレプリカ N1 からセカンダリレプリカ N2 へのデータ損失を伴う手動フェールオーバーを強制的に実行するには、次の手順を行います。

セカンダリレプリカ (N2) で、強制フェールオーバーを開始します。
```
ALTER AVAILABILITY GROUP [AGRScale] FORCE_FAILOVER_ALLOW_DATA_LOSS;
```
新しいプライマリレプリカ (N2) 上で、元のプライマリ (N1) を削除します。
```
ALTER AVAILABILITY GROUP [AGRScale]
REMOVE REPLICA ON N'N1';
```
すべてのアプリケーショントラフィックがリスナーまたは新しいプライマリレプリカに向けられていることを確認します。
元のプライマリ (N1) がオンラインになった場合は、直ちに、元のプライマリ (N1) 上で可用性グループ AGRScale をオフラインにします。
```
ALTER AVAILABILITY GROUP [AGRScale] OFFLINE
```
データまたは同期されていない変更がある場合は、ビジネスニーズに合わせてバックアップまたはその他のデータレプリケートオプションを使用して、そのデータを保存します。
次に、元のプライマリ (N1) から可用性グループを削除します。
```
DROP AVAILABILITY GROUP [AGRScale];
```
元のプライマリレプリカ (N1) 上の可用性グループデータベースを削除します。
```
USE [master]
GO
DROP DATABASE [AGDBRScale]
GO
```
(省略可能) 必要に応じて、N1 を新しいセカンダリレプリカとして可用性グループ AGRScale に追加できるようになりました。

リスナーを使用して接続する場合は、フェールオーバーの実行後にリスナーを再作成する必要があることに注意してください。

Always On 可用性グループの読み取りスケールを構成する

前提条件

Always On 可用性グループを有効にして mssql-server を再起動する

AlwaysOn_health イベントセッションを有効にする

データベースミラーリングエンドポイントの認証

サービスアカウント

証明書の認証

セカンダリサーバーで証明書を作成する

すべてのレプリカにデータベースミラーリングエンドポイントを作成する

可用性グループを作成する

セカンダリ SQL Server インスタンスを可用性グループに参加させる

可用性グループにデータベースを追加する

セカンダリサーバーにデータベースが作成されたことを確認する

読み取り専用セカンダリレプリカに接続する

読み取りスケール可用性グループのプライマリレプリカをフェールオーバーする

データ損失のない手動フェールオーバー

データ損失のある強制的な手動フェールオーバー

次のステップ

フィードバック

フィードバック

その他のリソース

Always On 可用性グループの読み取りスケールを構成する

前提条件

Always On 可用性グループを有効にして mssql-server を再起動する

AlwaysOn_health イベント セッションを有効にする

データベース ミラーリング エンドポイントの認証

サービス アカウント

SQL ログイン認証

証明書の認証

セカンダリ サーバーで証明書を作成する

すべてのレプリカにデータベース ミラーリング エンドポイントを作成する

可用性グループを作成する

セカンダリ SQL Server インスタンスを可用性グループに参加させる

可用性グループにデータベースを追加する

セカンダリ サーバーにデータベースが作成されたことを確認する

読み取り専用セカンダリ レプリカに接続する

読み取りスケール可用性グループのプライマリ レプリカをフェールオーバーする

データ損失のない手動フェールオーバー

データ損失のある強制的な手動フェールオーバー

次のステップ

フィードバック

フィードバック

その他のリソース

AlwaysOn_health イベントセッションを有効にする

データベースミラーリングエンドポイントの認証

サービスアカウント

セカンダリサーバーで証明書を作成する

すべてのレプリカにデータベースミラーリングエンドポイントを作成する

セカンダリサーバーにデータベースが作成されたことを確認する

読み取り専用セカンダリレプリカに接続する

読み取りスケール可用性グループのプライマリレプリカをフェールオーバーする