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

[アーティクル]
06/28/2024

適用対象: SQL Server - Linux

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

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

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

前提条件

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

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

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

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

各 SQL Server インスタンス名には次の条件があります。
- 15 文字以下。
- ネットワーク内で一意。
コンピューター名を設定するには、/etc/hostname を編集します。次のスクリプトを使うと、vi で /etc/hostname を編集できます。
```
sudo vi /etc/hostname
```
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 のさまざまなディストリビューションでのインストール手順です。

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 インスタンスに直接接続し、データベースにクエリを実行できます。リスナーを要求する読み取り専用ルーティングも利用できます。

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

各可用性グループにはプライマリレプリカが 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 に追加できるようになりました。

次の方法で共有

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

前提条件

SQL Server をインストールする

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

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

証明書を作成する

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

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

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

セカンダリ SQL Server インスタンスを AG に参加させる

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

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

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

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

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

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

フィードバック

その他のリソース

次の方法で共有

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

前提条件

SQL Server をインストールする

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

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

証明書を作成する

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

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

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

セカンダリ SQL Server インスタンスを AG に参加させる

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

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

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

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

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

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

関連するコンテンツ

フィードバック

その他のリソース

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

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

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

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

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

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