Linux で読み取りスケールの SQL Server 可用性グループを構成する

適用対象: SQL Server - Linux

この記事では、クラスター マネージャー "なし" の Linux 上の SQL Server Always On 可用性グループを作成する方法を説明します。 このアーキテクチャは、読み取りスケールのみを提供します。 高可用性は提供 "されません"。

AG には 2 種類のアーキテクチャがあります。 高可用性 向けのアーキテクチャでは、クラスター マネージャーを使用して、向上した事業継続性が提供されます。 高可用性アーキテクチャを作成するには、「Linux で高可用性を実現するために SQL Server の Always On 可用性グループを構成する」をご覧ください。

CLUSTER_TYPE = NONE による可用性グループには、さまざまなオペレーティング システム プラットフォームでホストされているレプリカを含めることができます。 高可用性はサポートできません。

前提条件

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

• 可用性レプリカをホストするすべてのサーバーが通信できるように環境を設定します。
• SQL Server をインストールします。

Linux でクラスターで管理するには、クラスター リソースとして追加する前に可用性グループを作成する必要があります。 このドキュメントでは、可用性グループを作成する例を示します。

1. 各ホストのコンピューター名を更新します。

   各 SQL Server インスタンス名には次の条件があります。

   • 15 文字以下。
   • ネットワーク内で一意。

   コンピューター名を設定するには、/etc/hostname を編集します。 次のスクリプトを使うと、vi で /etc/hostname を編集できます。

   sudo vi /etc/hostname
   

2. hosts ファイルを構成する。

   Note

   ホスト名が IP アドレスで DNS サーバーに登録されている場合、次の手順を実行する必要はありません。 可用性グループの一部として構成されているすべてのノードが、相互通信できることを確認します。 (そのホスト名を ping した場合、その対応する IP アドレスが返される必要があります)。また、/etc/hosts ファイルに、localhost IP アドレス 127.0.0.1 をノードのホスト名とマップするレコードが含まれないことを確認します。

   すべてのサーバー上の hosts ファイルには、可用性グループに参加するすべてのサーバーの IP アドレスと名前が含まれています。

   次のコマンドは、現在のサーバーの IP アドレスを返します。

   sudo ip addr show
   

   /etc/hosts を更新します。 次のスクリプトを使うと、vi で /etc/hosts を編集できます。

   sudo vi /etc/hosts
   

   次の例は、node1 の /etc/hosts を示しています。node1、node2、node3 に対して追加があります。 このサンプルでは、node1 がプライマリ レプリカをホストするサーバーを指し、node2 と node3 がセカンダリ レプリカをホストするサーバーを指します。

   127.0.0.1    localhost localhost4 localhost4.localdomain4
   ::1          localhost localhost6 localhost6.localdomain6
   10.128.18.12 node1
   10.128.16.77 node2
   10.128.15.33 node3
   

SQL Server をインストールする

SQL Server をインストールします。 次のリンクは、SQL Server のさまざまなディストリビューションでのインストール手順です。

• クイック スタート:Red Hat に SQL Server をインストールし、データベースを作成する
• クイック スタート:SUSE Linux Enterprise Server で SQL Server をインストールし、データベースを作成する
• クイック スタート:Ubuntu に SQL Server をインストールし、データベースを作成する

Always On 可用性グループを有効にする

SQL Server インスタンスをホストする各ノードで Always On 可用性グループを有効にしてから、mssql-server を再起動します。 次のスクリプトを実行します。

sudo /opt/mssql/bin/mssql-conf set hadr.hadrenabled 1
sudo systemctl restart mssql-server

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

オプションで拡張イベント (XE) を有効にすると、可用性グループをトラブルシューティングするとき根本原因の診断に役立ちます。 SQL Server の各インスタンスで次のコマンドを実行します。

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

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

証明書を作成する

Linux 上の SQL Server サービスは、ミラーリングのエンドポイント間の通信を認証するのに証明書を使用します。

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

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<Master_Key_Password>';
CREATE CERTIFICATE dbm_certificate WITH SUBJECT = 'dbm';
BACKUP CERTIFICATE dbm_certificate
    TO FILE = '/var/opt/mssql/data/dbm_certificate.cer'
    WITH PRIVATE KEY (
        FILE = '/var/opt/mssql/data/dbm_certificate.pvk',
        ENCRYPTION BY PASSWORD = '<Private_Key_Password>'
    );

この時点で、プライマリ SQL Server レプリカの /var/opt/mssql/data/dbm_certificate.cer には証明書が、var/opt/mssql/data/dbm_certificate.pvk には秘密キーが作成されています。 これら 2 つのファイルを、可用性レプリカをホストするすべてのサーバー上の同じ場所にコピーします。 mssql ユーザーを使うか、またはこれらのファイルへのアクセス許可を mssql ユーザーに付与します。

たとえば、ソース サーバーでは、次のコマンドでファイルがターゲット コンピューターにコピーされます。 <node2> の値を、レプリカをホストする SQL Server インスタンスの名前に置き換えます。

cd /var/opt/mssql/data
scp dbm_certificate.* root@<node2>:/var/opt/mssql/data/

各ターゲット サーバーで証明書にアクセスするアクセス許可を mssql ユーザーに付与します。

cd /var/opt/mssql/data
chown mssql:mssql dbm_certificate.*

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

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

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<Master_Key_Password>';
CREATE CERTIFICATE dbm_certificate
    FROM FILE = '/var/opt/mssql/data/dbm_certificate.cer'
    WITH PRIVATE KEY (
        FILE = '/var/opt/mssql/data/dbm_certificate.pvk',
        DECRYPTION BY PASSWORD = '<Private_Key_Password>'
    );

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

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

次の Transact-SQL スクリプトでは、可用性グループに対して Hadr_endpoint という名前のリスニング エンドポイントを作成します。 エンドポイントが起動され、作成した証明書に接続許可が付与されます。 スクリプトを実行する前に、< ... > の間の値を置き換えます。 必要に応じて、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;

Note

構成専用のレプリカのホストに 1 つのノードで SQL Server Express エディションを使用する場合、ROLE に有効な値は WITNESS のみです。 SQL Server Express エディションで次のスクリプトを実行します。

CREATE ENDPOINT [Hadr_endpoint]
    AS TCP (LISTENER_PORT = 5022)
    FOR DATABASE_MIRRORING (
        ROLE = WITNESS,
        AUTHENTICATION = CERTIFICATE dbm_certificate,
        ENCRYPTION = REQUIRED ALGORITHM AES
    );

ALTER ENDPOINT [Hadr_endpoint] STATE = STARTED;

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

重要

SQL Server 2017 (14.x) では、データベース ミラーリング エンドポイントでサポートされる唯一の認証方法は CERTIFICATE です。 WINDOWS オプションは利用できません。

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

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

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

次の Transact-SQL スクリプトによって ag1 という名前の AG が作成されます。 このスクリプトでは、SEEDING_MODE = AUTOMATIC で AG レプリカが構成されます。 この設定によって、SQL Server は AG にセカンダリ サーバーが追加されるたびに、そのセカンダリ サーバーでデータベースを自動作成します。 ご利用の環境に合わせて次のスクリプトを変更してください。 <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 インスタンスを AG に参加させる

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

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

ALTER AVAILABILITY GROUP [ag1] GRANT CREATE ANY DATABASE;

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

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

CREATE DATABASE [db1];
GO

ALTER DATABASE [db1]
SET RECOVERY FULL;
GO

BACKUP DATABASE [db1]
TO DISK = N'/var/opt/mssql/data/db1.bak';

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

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

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

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

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;
GO

この AG は高可用性構成ではありません。 高可用性が必要な場合は、「Linux で高可用性を実現するために SQL Server の Always On 可用性グループを構成する」の手順に従ってください。 具体的には、CLUSTER_TYPE=WSFC (Windows) または CLUSTER_TYPE=EXTERNAL (Linux) で AG を作成します。 その後、Windows Server フェールオーバー クラスタリング (Windows) または Pacemaker (Linux) を使って、クラスター マネージャーと統合できます。

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

読み取り専用セカンダリ レプリカには 2 つの方法で接続できます。 アプリケーションは、セカンダリ レプリカをホストする SQL Server インスタンスに直接接続し、データベースにクエリを実行できます。 リスナーを要求する読み取り専用ルーティングも利用できます。

• Always On 可用性グループのセカンダリ レプリカに読み取り専用の負荷を移す
• 読み取り専用ルーティング

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

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

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

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

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

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

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

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

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

2. アクティブなトランザクションが、プライマリ レプリカと少なくとも 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 の場合、セカンダリ レプリカは同期されています。

3. 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 つの同期セカンダリ レプリカにコミットされます。

   注意

   この設定は、フェールオーバーに固有のものではなく、環境の要件に基づいて設定する必要があります。

4. ロールの変更に備えて、フェールオーバーに参加していないプライマリ レプリカとセカンダリ レプリカをオフラインに設定します。

   ALTER AVAILABILITY GROUP [AGRScale] OFFLINE
   

5. ターゲット セカンダリ レプリカをプライマリに昇格させます。

   ALTER AVAILABILITY GROUP AGRScale FORCE_FAILOVER_ALLOW_DATA_LOSS; 
   

6. 以前のプライマリとその他のセカンダリのロールを SECONDARY に更新し、以前のプライマリ レプリカをホストする SQL Server インスタンスで次のコマンドを実行します。

   ALTER AVAILABILITY GROUP [AGRScale] 
        SET (ROLE = SECONDARY); 
   

   注意

   可用性グループを削除するには、DROP AVAILABILITY GROUP を使います。 種類が NONE または EXTERNAL のクラスターを使って作成された可用性グループでは、可用性グループに含まれるすべてのレプリカでコマンドを実行する必要があります。

7. データ移動を再開し、プライマリ レプリカがホストされている SQL Server インスタンス上の可用性グループ内のすべてのデータベースに対して、次のコマンドを実行します。

   ALTER DATABASE [db1]
        SET HADR RESUME
   

8. 読み取りスケールの目的で作成した、クラスター マネージャーでは管理されないリスナーをすべて再作成します。 元のリスナーが以前のプライマリを指している場合、それを削除して、新しいプライマリを指すように再作成します。

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

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

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

1. セカンダリ レプリカ (N2) で、強制フェールオーバーを開始します。

   ALTER AVAILABILITY GROUP [AGRScale] FORCE_FAILOVER_ALLOW_DATA_LOSS;
   

2. 新しいプライマリ レプリカ (N2) 上で、元のプライマリ (N1) を削除します。

   ALTER AVAILABILITY GROUP [AGRScale]
   REMOVE REPLICA ON N'N1';
   

3. すべてのアプリケーション トラフィックがリスナーまたは新しいプライマリ レプリカに向けられていることを確認します。

4. 元のプライマリ (N1) がオンラインになった場合は、直ちに、元のプライマリ (N1) 上で可用性グループ AGRScale をオフラインにします。

   ALTER AVAILABILITY GROUP [AGRScale] OFFLINE
   

5. データまたは同期されていない変更がある場合は、ビジネス ニーズに合わせてバックアップまたはその他のデータ レプリケート オプションを使用して、そのデータを保存します。

6. 次に、元のプライマリ (N1) から可用性グループを削除します。

   DROP AVAILABILITY GROUP [AGRScale];
   

7. 元のプライマリ レプリカ (N1) 上の可用性グループ データベースを削除します。

   USE [master]
   GO
   DROP DATABASE [AGDBRScale]
   GO
   

8. (省略可能) 必要に応じて、N1 を新しいセカンダリ レプリカとして可用性グループ AGRScale に追加できるようになりました。

関連するコンテンツ

• 分散型可用性グループ
• Always On 可用性グループとは
• Always On 可用性グループの強制手動フェールオーバーの実行 (SQL Server)